XL: Modifying Access Databases from Excel Using DAO QueryDefs

• 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. The data in the Recordset can then be modified, and by using the UPDATE command, the data in the database can be modified.

MORE INFORMATION

The macro described in this article modifies data using a QueryDef, following these steps:

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 are used to filter the query to return only records for a specified country. "Germany" has been hard-coded into the code, but parameters can be set in many different ways. Note that the parameter [CountryWanted] is substituted by the Microsoft Jet database engine 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" will be 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 will collect the field names and place them in the first row starting at "A1". Then bold them.

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

10. Use "CopyFromRecordset" to move the data onto the worksheet.

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

12. Clean up and delete the QueryDef that was just created. This removes it from the database. Then, close the objects.

The search results will 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. With Microsoft Office 97, the default location for the Northwind database is C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb. You may need to modify the OpenDatabase statement in this macro to specify the correct path to the Northwind database for your installation of Microsoft Office.

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

Microsoft provides examples of Visual Basic for Applications procedures 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. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

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, do the following:

   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

For more information about data access objects in Microsoft Excel version 7.0, click Answer Wizard on the Help menu and type the following topics:

   DAO

   Creating Parameter Queries with DAO

   Parameter Object

   PARAMETERS Declaration (SQL)

   QueryDef Object

   SELECT Statement (SQL)

   WHERE Clause (SQL)