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

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.

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

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

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

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 :