Microsoft KB Archive/151511

Knowledge Base

Modifying Access Databases from Excel Using DAO QueryDefs

Article ID: 151511

Article Last Modified on 10/11/2006

---

APPLIES TO

• Microsoft Excel 97 Standard Edition
• Microsoft Excel 95 Standard Edition

---

This article was previously published under Q151511

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers 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 requirements. 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.

1. 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.
2. Create a named QueryDef object with your SQL statement. This QueryDef is stored in your database.
3. 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.
4. Execute the QueryDef. Because this query returns records, you need to create a RecordSet object to capture the result set.
5. Issue a MoveLast followed by a MoveFirst. This ensures that you have collected all the records that meet the criteria.
6. 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.

1. Set up a loop that collects the field names and places them in the first row starting at "A1", and then make them bold.
2. Issue a MoveFirst to move to the beginning of the recordset.
3. Use "CopyFromRecordset" to move the data onto the worksheet.
4. Select the sheet that data was written to and autofit the column widths.
5. 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:

176476 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 XL

Keywords: kbdtacode kbhowto kbprogramming kbualink97 KB151511

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.