0

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.

0

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.

7
  • Thank you reaanb. At least now it doesn't crash Access. But it doesn't work. First I referenced to MS Activex DO 6.1 since it's the most recent I have available. Then I god an error like "data source object already initialized". Then I switched to MSADO2,8 as you suggested. Same error on line 2 of your code. Set myConnection = CurrentProject.Connection – Fred Cailloux Jan 29 '17 at 17:16
  • Here is my list of References: VBA: C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL <br/> Access: C:\Program Files (x86)\Microsoft Office\Office12\MSACC.OLB <br/> stdole: C:\Windows\SysWOW64\stdole2.tlb <br/> ADODB: C:\Program Files (x86)\Common Files\System\ado\msado28.tlb – Fred Cailloux Jan 29 '17 at 17:25
  • It works for me using version 6.1 as well. Try creating a blank new database, copy only the Amplifier table across and the test sub. – reaanb Jan 29 '17 at 17:27
  • Created a brand new Database and copied the Amplifier table into. Closed everything. Opened Database, Reference to ActiveX Data Object 6.1 _ then ran your code. Bang,,, Access crashed, like in terminate with as in Application Crash and closed. This is mind boggling – Fred Cailloux Jan 29 '17 at 17:37
  • 1
    I ran Office 2007 setup and chose Repair. Rebooted the computer and ran your little code with my new short Database. Everything worked fine. Hence, you were right on, my system was corrupted somehow. Don't know what the problem was but it is no more. Much appreciation – Fred Cailloux Jan 30 '17 at 2:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.