Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

How to execute a 'view' in ADODB/Jet/VB6?

eatc7402eatc7402 Posts: 15Member
Maybe I am all wet?... I THOUGHT (maybe mistakenly) that you could
execute a prewritten 'query' defined in an Access (Jet .mdb) database
by writing the 'proper' adodb code in VB6. But all I seem to come up
with is executing the 'SQL equivilent' of the predefined query in VB.

My problem seems to be how in the world would I know what the SQL should
be for a prewritten query? Is there a way to 'open' the predefined view
in adodb and determine it's underlying SQL code is... and the subsequently run that SQL, which would seem to be a 'way' to execute a predefined view.

My purpose is to execute a series of predefing queries already defined
in an .mdb database, and return recordsets for each query run.
I perhaps am suffering from the 'forest for the trees' syndrom,
but I don't seem to be comming across the DOCUMENTATION to deal with
situation.

Anyone have a 'steer' for me?

eatc7402
DaveF.

Comments

  • BarkeeperBarkeeper Posts: 335Member
    AFAIK it's the same like opening a table.

    in DAO: Set rs=db.openrecordset("PreDefinedQuery")
    in RDO: Set rs=db.OpenResultset("PreDefinedQuery")

    i don't know the ADO-Syntax since i'm not using ADO, but you should get the Idea.

    Zvoni

    : Maybe I am all wet?... I THOUGHT (maybe mistakenly) that you could
    : execute a prewritten 'query' defined in an Access (Jet .mdb) database
    : by writing the 'proper' adodb code in VB6. But all I seem to come up
    : with is executing the 'SQL equivilent' of the predefined query in VB.
    :
    : My problem seems to be how in the world would I know what the SQL should
    : be for a prewritten query? Is there a way to 'open' the predefined view
    : in adodb and determine it's underlying SQL code is... and the subsequently run that SQL, which would seem to be a 'way' to execute a predefined view.
    :
    : My purpose is to execute a series of predefing queries already defined
    : in an .mdb database, and return recordsets for each query run.
    : I perhaps am suffering from the 'forest for the trees' syndrom,
    : but I don't seem to be comming across the DOCUMENTATION to deal with
    : situation.
    :
    : Anyone have a 'steer' for me?
    :
    : eatc7402
    : DaveF.
    :
    :

    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

  • lionblionb Posts: 1,688Member ✭✭
    : Maybe I am all wet?... I THOUGHT (maybe mistakenly) that you could
    : execute a prewritten 'query' defined in an Access (Jet .mdb) database
    : by writing the 'proper' adodb code in VB6. But all I seem to come up
    : with is executing the 'SQL equivilent' of the predefined query in VB.
    :
    : My problem seems to be how in the world would I know what the SQL should
    : be for a prewritten query? Is there a way to 'open' the predefined view
    : in adodb and determine it's underlying SQL code is... and the subsequently run that SQL, which would seem to be a 'way' to execute a predefined view.
    :
    : My purpose is to execute a series of predefing queries already defined
    : in an .mdb database, and return recordsets for each query run.
    : I perhaps am suffering from the 'forest for the trees' syndrom,
    : but I don't seem to be comming across the DOCUMENTATION to deal with
    : situation.
    :
    : Anyone have a 'steer' for me?
    :
    : eatc7402
    : DaveF.
    :
    :
    You have to refer to Microsoft ActiveX DataObject 2.5(or older)Library. To do that go to Project-References and select it from dropdown list.
    [code]
    Dim cnConnection As ADODB.Connection
    Dim rsRecord As New ADODB.Recordset
    Dim sConnectString As String
    Dim strSQL As String

    sConnection = " " 'use here connection string for Access DB [b]not SPACE[/b]
    Something like that
    [blue]Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:My Documentsdb1.mdb;Persist Security Info=False [/blue]
    cnConnection.Open sConnectString

    strSQL = "SELECT StudentFirstName From Students"
    "WHERE StudentLName = '" & txtStudentLName.Text & "'"

    rsRecord.Open strSQL, cnSQLconnection, adOpenDynamic
    If Not rsRecord.EOF then
    Do Until rsRecord.EOF
    strLName = rsRecord.Fields("StudentLName").Value
    rsRecord.MoveNext
    Loop
    Else
    Msgbox "Record not found"
    End if
    [/code]
  • eatc7402eatc7402 Posts: 15Member
    : : Maybe I am all wet?... I THOUGHT (maybe mistakenly) that you could
    : : execute a prewritten 'query' defined in an Access (Jet .mdb) database
    : : by writing the 'proper' adodb code in VB6. But all I seem to come up
    : : with is executing the 'SQL equivilent' of the predefined query in VB.
    : :
    : : My problem seems to be how in the world would I know what the SQL should
    : : be for a prewritten query? Is there a way to 'open' the predefined view
    : : in adodb and determine it's underlying SQL code is... and the subsequently run that SQL, which would seem to be a 'way' to execute a predefined view.
    : :
    : : My purpose is to execute a series of predefing queries already defined
    : : in an .mdb database, and return recordsets for each query run.
    : : I perhaps am suffering from the 'forest for the trees' syndrom,
    : : but I don't seem to be comming across the DOCUMENTATION to deal with
    : : situation.
    : :
    : : Anyone have a 'steer' for me?
    : :
    : : eatc7402
    : : DaveF.
    : :
    : :
    : You have to refer to Microsoft ActiveX DataObject 2.5(or older)Library. To do that go to Project-References and select it from dropdown list.
    : [code]
    : Dim cnConnection As ADODB.Connection
    : Dim rsRecord As New ADODB.Recordset
    : Dim sConnectString As String
    : Dim strSQL As String
    :
    : sConnection = " " 'use here connection string for Access DB [b]not SPACE[/b]
    : Something like that
    : [blue]Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:My Documentsdb1.mdb;Persist Security Info=False [/blue]
    : cnConnection.Open sConnectString
    :
    : strSQL = "SELECT StudentFirstName From Students"
    : "WHERE StudentLName = '" & txtStudentLName.Text & "'"
    :
    : rsRecord.Open strSQL, cnSQLconnection, adOpenDynamic
    : If Not rsRecord.EOF then
    : Do Until rsRecord.EOF
    : strLName = rsRecord.Fields("StudentLName").Value
    : rsRecord.MoveNext
    : Loop
    : Else
    : Msgbox "Record not found"
    : End if
    : [/code]
    :

    ===================================
    Let me restate my problem. It is NOT (repeat NOT) a problem getting
    an SQL command to do its job. That works fine.

    My problem is I want to execute an ALREADY WRITTEN QUERY (written in
    the .mdb Access db), from VB6 using adodb.

    After setting up the connection (DbaseConn)

    This works...
    Set Rs = New ADODB.Recordset
    Rs.Open "select * from Fires_MAIN ", DbaseConn, 3, 1
    '--------------------------

    This DOES NOT WORK....
    Set Rs = DbaseConn.OpenRecordset("Fires_all_Query", dbOpenForwardOnly)

    Neither does this work....
    Set Rs = DbaseConn.OpenRecordset("Fires_all_Query")

    the results of this are a run time error of 3001...
    Arguments are in conflict, or out of range.

    I don't understand what might be wrong. The query name is correct for
    what exists in the db. My feeling is that 'something' special needs to
    be done when trying to open a predefined qwuery, but I can't seem to
    figure what that is.


    =======================
    A related question assumming I can get the query to run is...

    Is there a way to get the properties of the predefined query object
    and determine what the SQL for the query consists of, so it may displayed, or modified?

    eatc7402
    DaveF



  • lionblionb Posts: 1,688Member ✭✭
    ===================================
    : Let me restate my problem. It is NOT (repeat NOT) a problem getting
    : an SQL command to do its job. That works fine.
    :
    : My problem is I want to execute an ALREADY WRITTEN QUERY (written in
    : the .mdb Access db), from VB6 using adodb.
    :
    : After setting up the connection (DbaseConn)
    :
    : This works...
    : Set Rs = New ADODB.Recordset
    : Rs.Open "select * from Fires_MAIN ", DbaseConn, 3, 1
    : '--------------------------
    :
    : This DOES NOT WORK....
    : Set Rs = DbaseConn.OpenRecordset("Fires_all_Query", dbOpenForwardOnly)
    :
    : Neither does this work....
    : Set Rs = DbaseConn.OpenRecordset("Fires_all_Query")
    :
    : the results of this are a run time error of 3001...
    : Arguments are in conflict, or out of range.
    :
    : I don't understand what might be wrong. The query name is correct for
    : what exists in the db. My feeling is that 'something' special needs to
    : be done when trying to open a predefined qwuery, but I can't seem to
    : figure what that is.
    :
    :
    : =======================
    : A related question assumming I can get the query to run is...
    :
    : Is there a way to get the properties of the predefined query object
    : and determine what the SQL for the query consists of, so it may displayed, or modified?
    :
    : eatc7402
    : DaveF
    :
    :
    :
    Sorry, I missuderstood your question. Honestly speaking, a worked with Access DB ages ago. I think you may try Command object to run Query. Try this
    [code]
    Dim cnConnection As ADODB.Connection
    Dim rsRecord As New ADODB.Recordset
    Dim cmdSQL As New ADODB.Command
    Dim sConnectString As String

    cnConnection.Open sConnectString
    With cmdSQL
    .ActiveConnection = cnConnection
    .CommandType = adCmdStoredProc
    .CommandText = "Fires_all_Query" 'Query name
    Set rsRecord = .Execute
    End With

    If Not rsRecord.EOF then
    Do Until rsRecord.EOF
    strLName = rsRecord.Fields("StudentLName").Value
    rsRecord.MoveNext
    Loop
    Else
    Msgbox "Record not found"
    End if
    [/code]

  • eatc7402eatc7402 Posts: 15Member
    Yes! That works, and I get back the data I expect!

    I now see thst with PREDEFINED QUERIES you do an
    COMMAND EXECUTE,

    not an OpenRecordset to get back your data. Also It points my
    confusion over a STORED PROCEDURE (read that as a 'query')
    and a VIEW. They appear to act the same way (for those that
    return data). Maybe I missed it somewhere, but not everyone
    knows what a Srored Procedure is (or what they do),
    and may not equate it to a 'query' or 'view'.

    Anyway, many thanks. I may at least be able to the EDGE of the forest.

    Any thoughts about my second question about the underlaying SQL of
    a predefined query, and the question of it's 'visibility'.

    eatc7402
    DaveF.

  • lionblionb Posts: 1,688Member ✭✭
    : Yes! That works, and I get back the data I expect!
    :
    : I now see thst with PREDEFINED QUERIES you do an
    : COMMAND EXECUTE,
    :
    : not an OpenRecordset to get back your data. Also It points my
    : confusion over a STORED PROCEDURE (read that as a 'query')
    : and a VIEW. They appear to act the same way (for those that
    : return data). Maybe I missed it somewhere, but not everyone
    : knows what a Srored Procedure is (or what they do),
    : and may not equate it to a 'query' or 'view'.
    :
    : Anyway, many thanks. I may at least be able to the EDGE of the forest.
    :
    : Any thoughts about my second question about the underlaying SQL of
    : a predefined query, and the question of it's 'visibility'.
    :
    : eatc7402
    : DaveF.
    :
    :
    About Stored Procedure - from MS SQL Server Help:
    'Stored procedures are a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit.
    Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values. '

    I am not sure about VIEW in Access. In DB2, Oracle, MS SQL Server and other databases view is a SQL statement(s) that can be called by other views, stored procedures or SQL statemets. Example
    [code]
    [blue]vwTest[/blue]
    SELECT UserFirstName,UserLastName,Phone,Address FROM tblUsers WHER UserID IN(1,100)

    [blue]Stored Procedure[/blue]

    CREATE PROCEDURE proc_USER_Name
    @LastName varchar (50)
    AS
    BEGIN
    SELECT * FROM vwTest WHERE UserLAstName = @LastName
    GO

    [blue]another view[/blue]
    SELECT * FROM vwTest WHERE UserLAstName LIKE 'Smit%'
    [/code]




  • eatc7402eatc7402 Posts: 15Member
    : : Any thoughts about my second question about the underlaying SQL of
    : : a predefined query, and the question of it's 'visibility'.
    : :
    : : eatc7402
    : : DaveF.
    : :
    : :
    : About Stored Procedure - from MS SQL Server Help:
    : 'Stored procedures are a precompiled collection of SQL statements and optional control-of-flow statements stored under a name and processed as a unit.
    : Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values. '
    :
    : I am not sure about VIEW in Access. In DB2, Oracle, MS SQL Server and other databases view is a SQL statement(s) that can be called by other views, stored procedures or SQL statemets. Example
    : [code]
    : [blue]vwTest[/blue]
    : SELECT UserFirstName,UserLastName,Phone,Address FROM tblUsers WHER UserID IN(1,100)
    :
    : [blue]Stored Procedure[/blue]
    :
    : CREATE PROCEDURE proc_USER_Name
    : @LastName varchar (50)
    : AS
    : BEGIN
    : SELECT * FROM vwTest WHERE UserLAstName = @LastName
    : GO
    :
    : [blue]another view[/blue]
    : SELECT * FROM vwTest WHERE UserLAstName LIKE 'Smit%'
    : [/code]
    :

    Your reply is BACKWARDS from I am looking for. I do NOT
    want SEND a queries SQL to the database. I am asking if the
    SQL 'already' written into a predefined query can RETREIVED
    somehow from the query definition, using VB code somehow.
    Again, I don't want to send SQL codes, I want to GET them,
    if they are available.

    eatc7402
    DaveF.


  • lionblionb Posts: 1,688Member ✭✭
    : Your reply is BACKWARDS from I am looking for. I do NOT
    : want SEND a queries SQL to the database. I am asking if the
    : SQL 'already' written into a predefined query can RETREIVED
    : somehow from the query definition, using VB code somehow.
    : Again, I don't want to send SQL codes, I want to GET them,
    : if they are available.
    :
    : eatc7402
    : DaveF.
    :
    There is only one way to do that. Somebody has to write query for you :-D. There are no predefined queries because all query are different. Working with Access you can build query using Wizard but that way is limited. Working with MS SQL Server you can do that using Query Analyzer but it's still limited. You will be able to build only very simple query. Nothing sophisticated could be built using those ways. At the same time, in real world, in most cases you need quite sopfisticated SQL code.
  • BarkeeperBarkeeper Posts: 335Member
    I think i remember a property of the QueryDef-Object in DAO called "SQL".... now what could that be????

    Zvoni

    : : Your reply is BACKWARDS from I am looking for. I do NOT
    : : want SEND a queries SQL to the database. I am asking if the
    : : SQL 'already' written into a predefined query can RETREIVED
    : : somehow from the query definition, using VB code somehow.
    : : Again, I don't want to send SQL codes, I want to GET them,
    : : if they are available.
    : :
    : : eatc7402
    : : DaveF.
    : :
    : There is only one way to do that. Somebody has to write query for you :-D. There are no predefined queries because all query are different. Working with Access you can build query using Wizard but that way is limited. Working with MS SQL Server you can do that using Query Analyzer but it's still limited. You will be able to build only very simple query. Nothing sophisticated could be built using those ways. At the same time, in real world, in most cases you need quite sopfisticated SQL code.
    :

    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

  • HackmanCHackmanC Posts: 441Member
    [b][red]This message was edited by HackmanC at 2006-6-15 13:11:31[/red][/b][hr]
    ANYWAY ...
    The correct way to write MSAccess parametriziced ? querys.

    ---- IN MSACCESS :: NAME Query1 -----
    PARAMETERS PARAM1 Short [red]Short is the type name[/red]
    SELECT * FROM Table01 WHERE IdTab = [PARAM1]
    ----

    USE THE QUERY EDITOR AND OPEN THE
    PARAMETERS WINDOW TO CREATE PARAMETERS
    IF NOT, YOU HAVE TO KNOW THE TYPE NAME OF MSACCESS.

    [code]
    Private Sub Form_Load()
    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim p As ADODB.Parameter
    Dim r As ADODB.Recordset

    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Documents and SettingsAdministratorMy documents estdata.mdb;Persist Security Info=False"
    conn.Open
    conn.CursorLocation = adUseClient

    Set cmd = New ADODB.Command
    cmd.ActiveConnection = conn
    cmd.CommandType = adCmdStoredProc

    Set p = New ADODB.Parameter
    p.Name = "PARAM1"
    p.Type = adSmallInt
    p.Direction = adParamInput
    p.Value = 10
    cmd.Parameters.Append p

    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "Query1"
    Set r = cmd.Execute

    Debug.Print r.Supports(adBookmark)
    Set Me.Adodc1.Recordset = r
    End Sub
    [/code]

    WHY USE THIS APPROACH ?
    * BECAUSE A STORED PROCEDURE IS PREPARED, IF YOU CALL THE SAME STORED PROCEDURE MORE THAN ONE TIME SHOULD BE FASTER.
    * BECAUSE IS SECURE, EVEN WHEN A HACKER TRY TO SNEAK INTO YOUR QUERY STRING (IN WEB, OF COURSE), SHOUL BE MORE DIFICULT TO INTRODUCE A MODIFIED QUERY.
    * ITS TYPE SAFE, BECAUSE THE PARAMETER HAS A TYPE DEFINITION.
    * BECAUSE IS EASY TO MAINTAIN.
    * IT ENFORCES BUSINESS RULES.

    ----
    : Maybe I am all wet?... I THOUGHT (maybe mistakenly) that you could
    : execute a prewritten 'query' defined in an Access (Jet .mdb) database
    : by writing the 'proper' adodb code in VB6. But all I seem to come up
    : with is executing the 'SQL equivilent' of the predefined query in VB.
    :
    : My problem seems to be how in the world would I know what the SQL should
    : be for a prewritten query? Is there a way to 'open' the predefined view
    : in adodb and determine it's underlying SQL code is... and the subsequently run that SQL, which would seem to be a 'way' to execute a predefined view.
    :
    : My purpose is to execute a series of predefing queries already defined
    : in an .mdb database, and return recordsets for each query run.
    : I perhaps am suffering from the 'forest for the trees' syndrom,
    : but I don't seem to be comming across the DOCUMENTATION to deal with
    : situation.
    :
    : Anyone have a 'steer' for me?
    :
    : eatc7402
    : DaveF.
    :
    :

    [red]Good luck![/red]
    [blue]Hackman[/blue]





Sign In or Register to comment.