Failed VBA SQL Query in Outlook 365?-CodePudding

I have some VBA code in Outlook that runs a SQL query and populates a userform I designed. This code was previously running fine on one PC, then I was issued a new laptop and I copied the outlook VBA module directly over.

When I try to run, I just get an error "Compile Error: Can't find project or library" and the text "adOpenStatic" in the RS.open statement is highlighted. Previously, I had seen the same error if my SQL query was bad or the driver was missing, but I double checked both. I copied the EXACT code into an excel VBA module and it ran as expected.

The only other difference I can see is that I went from Outlook 2019 to Office 365 for Enterprise.

Dim server, database, login_user, password, port As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

server = "dummy"
database = "dummy"
login_user = "dummy"
password = "dummy"
port = "dummy"

Set cn = New ADODB.Connection
cn.ConnectionString = "DRIVER={MySQL ODBC 8.0 ANSI Driver};Provider=MSDASQL;" & "SERVER=" & server & ";" & " DATABASE=" & database & ";" & "UID=" & login_user & ";PWD=" & password & "; OPTION=3; PORT=" & port & ";Connect Timeout=20;"


'open the connection
cn.Open

Set rs = New ADODB.Recordset

strSQL = "SELECT * FROM quote WHERE id = 1505"
rs.Open strSQL, cn, adOpenStatic

With rs
    Do Until .EOF
        'parse out relevant project information
        rs.MoveNext
    Loop
End With

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

I can run the exact query in MySQL workbench, and it works as expected. The fact that the identical code can run in Excel without issue tells me there is perhaps a syntax difference in Outlook that I am unaware of? Anyone else have ideas?

NOTE, if it wasn't already obvious, I am omitting my database details with "Dummy", but the real code has the correct strings.

CodePudding user response：

Go to the VBA Editor, and choose Tools-References; and tick the box next to 'Microsoft ActiveX Data Objects' (the latest version). It should compile ok now.

CodePudding user response：

This code was previously running fine on one PC, then I was issued a new laptop and I copied the outlook VBA module directly over.

You need to re-add all COM references you had on the old machine.

From the Tools menu, choose References to display the References dialog box.

The References dialog box shows all object libraries registered with the operating system. Scroll through the list for the application whose object library you want to reference. If the application isn't listed, you can use the Browse button to search for object libraries (*.olb and .tlb) or executable files (.exe and *.dll on Windows). References whose check boxes are selected are used by your project; those that aren't selected are not used, but can be added.