XL: Modifying Access Databases from Excel Using DAO QueryDefs

ID: Q151511

The information in this article applies to:

• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel 97 for Windows

SUMMARY

You can use the versions of Microsoft Excel listed at the beginning of this article to modify data in a Microsoft Access database. Although you can modify Microsoft Access databases in several different ways, this article focuses on using QueryDefs to accomplish the task.

To modify data using a QueryDef, first create a QueryDef, and then a Recordset based on search criteria. You can then modify the data in the Recordset, and by using the UPDATE command, can modify the data in the database.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/

 1. Create a PARAMETERS clause string that includes a ParameterName and
    Data Type for each parameter. Don't use the field name alone as the
    ParameterName, because duplicating it may cause problems. You can,
    however, include the field name within the ParameterName.

    If you are working with a database accessed by Microsoft Access,
    the ParameterName is used as a prompt string. Keep this in mind if
    you want Microsoft Access users to use this query.

 2. Create a SELECT statement that retrieves the needed fields and
    incorporates the named parameters into the WHERE clause. In the
    example below, the parameters filter the query to return only
    records for a specified country. "Germany" has been hard-coded
    into the code, but you can set parameters in many different ways.
    Note that the Microsoft Jet database engine substitutes the parameter
    [CountryWanted] during execution of the query at run time.

 3. Create a named QueryDef object with your SQL statement. This
    QueryDef is stored in your database.

 4. Set the QueryDef object parameters. First, you need to gain access
    to the QueryDef object. In this case, all the customers in
    "GERMANY" are found.

 5. Execute the QueryDef. Because this query returns records, you need
    to create a RecordSet object to capture the result set.

 6. Issue a MoveLast followed by a MoveFirst. This ensures that you
    have collected all the records that meet the criteria.

 7. Set up a loop that will modify each record in the recordset. In
    this case, the "REGION" is set to "EUROPE" for each record. This
    recordset is comprised of customers in "GERMANY." Note that you
    need to issue an "Rs.Update" before moving to the next record, or
    the database will not be updated.

    At this point, the database has been modified. The rest of this
    code displays the data on a worksheet. This is not necessary to
    complete the operation.

 8. Set up a loop that collects the field names and places them in
    the first row starting at "A1", and then make them bold.

 9. Issue a MoveFirst to move to the beginning of the recordset.

11. Select the sheet that data was written to and autofit the column

    widths.

    removes it from the database. Then, close the objects.

The search results also display a number of help topics relating to working with databases.

This code has been modified from the Help file to work with the Northwind database. With Microsoft Office 7.0, the default location for the Northwind database is:

   C:\MSOffice\Access\Samples\Northwind.mdb

  C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb

Additionally, code has been added to demonstrate looping through a RecordSet and writing a RecordSet to a worksheet.

NOTE: Before running this macro, you must create a reference to the correct DAO Object Library for the version of Microsoft Excel you are using. Microsoft Excel 95 uses the Microsoft DAO 3.0 Object Library and Microsoft Excel 97 uses the Microsoft DAO 3.5 Object Library. To create a reference, follow these steps:

   1. On a Module sheet, click References on the Tools menu.

   2. Select Microsoft the correct DAO Object Library and click OK.

Sub UpdateRecordsQuery()
    Dim Db As database
    Dim Qd As QueryDef
    Dim Rs As recordset
    Dim qdParmQD As QueryDef
    Dim SQL As String
    Dim i as integer

' Set your database object. You may need to change the path to match
' where Microsoft Office is installed.
   Set Db = _
   workspaces(0).OpenDatabase("c:\msoffice\access\samples\northwind.mdb")

    ' 1. Create a PARAMETERS clause string.
    SQL = "PARAMETERS [CountryWanted] TEXT; "

    ' 2. Create a SELECT statement.
    SQL = SQL & "SELECT DISTINCTROW * " & _
        " FROM Customers" & _
        " WHERE (Customers.Country =[CountryWanted]) "

    ' 3. Create a named QueryDef object with your SQL statement.
    Set Qd = Db.CreateQueryDef("Find Customers", SQL)

    ' 4. Set the QueryDef object parameters.
    Set qdParmQD = Db.querydefs("Find Customers")
    qdParmQD("CountryWanted") = "Germany"

    ' 5. Execute the QueryDef.
    Set Rs = qdParmQD.OpenRecordset()

    ' 6. Issue a MoveLast followed by a MoveFirst
    Rs.MoveLast
    Rs.MoveFirst

   ' 7. Set up a loop that will modify each record in the recordset.
    For i = 1 To Rs.RecordCount
        Rs.Edit
        Rs("Region") = "Europe"
        Rs.Update
        Rs.MoveNext
    Next i

    ' At this point, the database has been modified. The rest of this
    ' code displays the data on a worksheet. This is not necessary to
    ' complete the operation.

    ' 8. Collect field names.
    For i = 0 To Rs.Fields.Count - 1
        Sheets("Sheet1").Cells(1, i + 1).Value = Rs.Fields(i).Name
    Next i
    Sheets("Sheet1").Range(Sheets("Sheet1").Cells(1, 1), _
        Sheets("Sheet1").Cells(1, Rs.Fields.Count)).Font.Bold = True

    ' 9. Issue a MoveFirst to move to the beginning of the recordset.
    Rs.MoveFirst

    ' 10. Use CopyFromRecordset to move the data onto the worksheet
    Sheets("Sheet1").Range("A2").CopyFromRecordset Rs

    ' 11. Select the sheet that data was written to and autofit the
    '     column widths.
    Sheets("Sheet1").Select
    Selection.CurrentRegion.Select
    Selection.Columns.AutoFit

    ' Select cell A1.
    Range("A1").Select

    ' 12. Clean up and delete the QueryDef that was just created.  This
    '     removes it from the database.  Then close the objects.
    Db.querydefs.Delete "Find Customers"
    Qd.Close
    Rs.Close
    Db.Close

End Sub

REFERENCES

For more information about Data Access Objects in Microsoft Excel 97, from the Visual Basic Editor, click the Office Assistant, type "DAO," click Search, and then click to view "Data Access Objects Overview."

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If the Assistant is not able to answer your query, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q176476
   TITLE     : OFF: Office Assistant Not Answering Visual Basic Questions

   DAO

   Creating Parameter Queries with DAO

   Parameter Object

   PARAMETERS Declaration (SQL)

   QueryDef Object

   SELECT Statement (SQL)

   WHERE Clause (SQL)

Keywords          : kbprg kbualink97 kbdta kbdtacode KbVBA kbhowto 
Version           : WINDOWS:7.0,7.0a,97
Platform          : WINDOWS

Last Reviewed: May 18, 1999