MS Access Crash on DAO connection

Question

Can someone take a look at this code and provide a hint as to what it is I am doing wrong. This code taken almost literally from a reference book, as an example on how to use VBA ADO object. Here is the code:
Sub TestMyADODB() Dim mySQL As String Dim myConnection As DAO.Connection Dim myRecordSet As DAO.Recordset Set myConnection = CurrentProject.Connection ' Crash here mySQL = mySQL & "UPDATE Amplifier " mySQL = mySQL & "SET Amplifier.lngCategory = 405, Amplifier.lngSubType = 3 " mySQL = mySQL & "WHERE (((Amplifier.lngPartIndex)=9))" myRecordSet.OpenRecordset mySQL Debug.Print "DONE" myRecordSet.Close Set myRecordSet = Nothing Set myConnection = Nothing

Furthermore, I tried using the ADODB object, result, the compiler will not permit the code. I found I needed to add a reference to the MS dll, which I did, still the same result. Then I started getting an error from VBA stating that I cannot use DAO which is now " Outdated " and that I must use ADO. The rest is 10 hours of lost precious time trying to demystify the ADO DAO issue from multiple articles on tne web. Bottom line, my Access 2007 will literally crash if I use DAO and the compiler will refuse to use ADO. I am loosing my hairs over this issue, and I need help! Appreciate
PS: The mySQL string has been tested in an Access query and functional.

Answer 1

You say the code is an example of how to use ADO, but it references DAO. DAO and ADO are two completely different data access libraries from Microsoft. ADO is the newer one. Please don't mix them up.

Try the following:

In your Access Visual Basic IDE, click on Tools | References. Make sure that Microsoft ActiveX Data Objects 2.8 is selected. Unselect any other references mentioning DAO, Data Access Objects, ADO or ActiveX Data Objects. If you don't have version 2.8 installed, try the latest version you've got.

Now, modify your sub to look like this:

Sub TestMyADODB()
    Dim myConnection As ADODB.Connection
    Set myConnection = CurrentProject.Connection

    Dim mySQL As String
    mySQL = mySQL & "UPDATE Amplifier "
    mySQL = mySQL & "SET Amplifier.lngCategory = 405, Amplifier.lngSubType = 3 "
    mySQL = mySQL & "WHERE Amplifier.lngPartIndex=9"

    myConnection.Execute mySQL

    Debug.Print "DONE"
End Sub

I changed the connection type to ADODB.Connection to match the selected library. I also removed the recordset object since that's only used for SELECT queries. Queries that don't return rows can be executed directly on the connection.

This works on my machine at least.