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/overview/overview.asp
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 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.


  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.


Steps 1 through 5 in the following code were copied from "Creating Parameter Queries with DAO" in the Microsoft Excel 7.0 Online Help. To read the help topic, in Microsoft Excel for Windows 95, version 7.0, click Help and select Answer Wizard. In the Search edit box, type Creating Parameter Queries with DAO and click Search. Scroll down to "Programming and Language Reference" and double-click "Creating Parameter Queries with DAO,", or select this topic, and click Display. This displays the code used to create this article with amplification.

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 
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.

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:

Q176476 OFF: Office Assistant Not Answering Visual Basic Questions
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)

Additional query words: 8.00 97

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


Last Reviewed: November 9, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.