Hpw to access stored quires in access 2007 - using vba excel

Posted on 2011-05-07
Last Modified: 2012-05-11

I can see some stored quires in my access database.
Was just wondering how owuld you access one using vba excel?
Also , is this like a stored procedure? - i guess yes - please confirm

Question by:SameerMirza
    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    It's been a while since I have used stored queries in Access but I would imaging they are very similar to using SQL stored procedures when accessed using ADODB. Here is some information on using ADODB from Excel.

    The following text and sample code illustrates how to:

       -> open a database connection
       -> use a stored procedure to perform a query
       -> execute any SQL command against a database
       -> open a recordset using a custom query
       -> open a recordset using a table name
       -> check for an empty recordset
       -> read all records in a recordset
       -> add a record to a recordset
       -> delete a record from a recordset
       -> copy a recordset with headers to a worksheet
       -> close a recordset and database

    This sample code, except for Open database method, can be used with any database such as Access, SQL Server, or Oracle. When using a database, most interaction happens via a recordset. Data is manipulated almost entirely using Recordset objects. Any number of Recordset objects can be created and used at the same time - each representing a different query or the same query. Different Recordset objects can access the same tables, queries, and fields without conflicting.

    After opening a Recordset the Recordset can contain zero or more records. One record in the Recordset is always the current record except when the Recordset BOF or EOF property is true in which case no record is the current record. The current record is the record that is affected by any record-specific methods. To move amongst the records in a Recordset use the MoveNext, MovePrevious, MoveLast, and MoveFirst Recordset methods. A specific record can be made the current record by setting the AbsolutePosition property to the index number of the desired record. Fields in the current record are access as illustrated below.

       Value = MyRecordset!Field1
       MyRecordset!Field2 = Value + 1

    When the current record is changed use the Update method to apply the changes to the database. Use the Add method to add a new record and the Delete method to delete the current record. The Add method can be used even if the query returns an empty recordset.

    Caution about using the RecordCount method: The RecordCount for a serverside recordset may return a -1. This occurs with ActiveX Data Objects (ADO) version 2.0 or later when the CursorType is adOpenForwardonly or adOpenDynamic and when with ADO 1.5 only when the cursortype is adOpenForwardonly. To get around this problem use either adOpenKeyset or adOpenStatic as the CursorType for server side cursors or use a client side cursor. Client side cursors use only adOpenStatic for CursorTypes regardless of which CursorType is selected.

    Before writing any ADODB code the data objects library "Microsoft ActiveX Data Objects x.x Library" must be referenced in the VBA project (Tools->References).

    For additional information on the ADODB interface see the MSDN pages at:

    Sample code:

       Dim MyDatabase As ADODB.Connection
       Dim MyCommand As ADODB.Command
       Dim MyRecordset As ADODB.RecordSet
       Dim Column As Long

       ' Open database connection
       Set MyDatabase = New ADODB.Connection
       MyDatabase.CursorLocation = adUseClient
       MyDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='C:\full\path\to\database.mdb'; User Id=admin; Password=;"
       ' For more information about Open syntax:
       ' Additional help constructing connection strings can be found at

       ' Query database using stored procedure (requires command object)
       Set MyCommand = New ADODB.Command
       Set MyCommand.ActiveConnection = MyDatabase
       MyCommand.CommandText = "qrySomeQuery" ' <- name of procedure
       MyCommand.CommandType = adCmdStoredProc
       With MyCommand
          .Parameters.Append .CreateParameter("QueryTextParam", adVarChar, adParamInput, 10, StringValue)
          .Parameters.Append .CreateParameter("QueryTextParam", adDouble, adParamInput, , DoubleValue)
          .Parameters.Append .CreateParameter("QueryLongParam", adBigInt, adParamInput, , LongValue)
          .Parameters.Append .CreateParameter("QueryDateParam", adDate, adParamInput, , DateValue)
          .Parameters.Append .CreateParameter("QueryDateTimeStampParam", adDBTimeStamp, adParamInput, , DateTimeValue)
          .Parameters.Append .CreateParameter("BooleanParam", adBoolean, adParamInput, , BooleanValue)
          ' For more information about CreateParameter syntax:
       End With
       ' Open recordset using command object
       Set MyRecordset = New ADODB.Recordset
       MyRecordset.Open MyCommand, , adOpenDynamic, adLockPessimistic
       ' For more information about Open method syntax:
       ' Build a custom query using command object
       Set MyCommand = New ADODB.Command
       With MyCommand
          Set .ActiveConnection = MyDatabase
          .CommandType = adCmdText
          .CommandText = "SELECT * From tblMyTable WHERE (tblMyTable.MyID = 1)"
       End With
       MyRecordSet.Open MyCommand, , adOpenDynamic, adLockReadOnly

       ' Execute any SQL statement
       MyDatabase.Execute "INSERT INTO TableName (Field1, Field2) VALUES ('" & Range("A1").Value & "','" & Range("A2").Value & "')"

       ' Open a recordset by specifying specific table (no query)
       MyRecordset.Open "TableName", MyDatabase, adOpenDynamic, adLockPessimistic
       ' Open a recordset by specifying query without using command object
       MyRecordset.Open "SELECT * FROM MyTable", MyDatabase, adOpenDynamic, adLockPessimistic

       ' Test for no records
       If MyRecordset.BOF And MyRecordset.EOF Then
          MsgBox "No records in table"
       End If

       ' Determine total records (see notes above about inconsistencies with the RecordCount method)
       MsgBox "Total records: " & MyRecordset.RecordCount
       ' Look at all records in record set
       While Not MyRecordset.EOF
          MsgBox "Record number: " & MyRecordset.AbsolutePosition

       ' Find a specific record given a field value
       MyRecordset.Find "ID='ABC123'"
       If Not MyRecordset.BOF And Not MyRecordset.EOF Then
          MyRecordset!Field1 = "Match Found"
       End If

       ' Copy the entire recordset to a worksheet (this technique does not copy field names)
       Sheets("Sheet1").[A2].CopyFromRecordset MyRecordset
       ' Create headers and copy data
       With Sheets("Sheet1")
          For Column = 0 To MyRecordset.Fields.Count - 1
             .Cells(1, Column + 1).Value = MyRecordset.Fields(Column).Name
          .Range(.Cells(1, 1), .Cells(1, MyRecordset.Fields.Count)).Font.Bold = True
         .Cells(2, 1).CopyFromRecordset MyRecordset
       End With

       ' Update current record
       MyRecordset!Field1 = "Some data"
       MyRecordset!Field2 = "Some more data"

       ' Move specific fields from current record to worksheet
       With Sheets("Sheet1")
          Cells(Row, "A") = MyRecordset!Field1
          Cells(Row, "B") = MyRecordset!Field2
       End With
       ' Add new record and set field values
       MyRecordset!Field1 = "Some data"

       ' Update an existing record or add it if it does not exist
       MyRecordset.Find "Field1='"& SourceSheet.Cells(Row, "A") & "'"
       If MyRecordset.BOF Or MyRecordset.EOF Then
       End If
       MyRecordset!Field1 = SourceSheet.Cells(Row, "A")
       MyRecordset!Field2 = SourceSheet.Cells(Row, "B")
       ' Delete current record

       ' Close recordset

       ' Close database

    LVL 92

    Accepted Solution


    What is it that you are ultimately trying to do?

    In any event, I prefer using DAO when working with Access, as DAO is tied more tightly to the Access object model.

    The following code will enumerate the stored queries in an Access database, and assume you are running the VBA code from Excel.

    Sub ListAccessQueries()
        Dim acc As Object
        Dim db As Object
        Dim qd As Object
        Dim r As Long
        Dim arr() As Variant
        Const db_path As String = "C:\folder\subfolder\database.mdb"
        Set acc = CreateObject("Access.Application")
        acc.OpenCurrentDatabase db_path
        Set db = acc.CurrentDb
        ReDim arr(1 To db.QueryDefs.Count, 1 To 2) As Variant
        For r = 1 To UBound(arr, 1)
            ' QueryDefs is a zero-based collection
            Set qd = db.QueryDefs(r - 1)
            arr(r, 1) = qd.Name
            arr(r, 2) = qd.Sql
        Set qd = Nothing
        Set db = Nothing
        Set acc = Nothing
        [a1] = "Query Name"
        [b1] = "SQL Statement"
        Range(Cells(2, 1), Cells(1 + r, 2)).Value = arr
    End Sub

    Open in new window

    Access stored queries are somewhat akin to sprocs, but nowhere near as flexible.  An Access SELECT or UNION query is probably more like a SQL Server view.


    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now