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:
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))"
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.