ADODB.Connection undefined

Question

Reference http://stackoverflow.com/questions/1690622/excel-vba-to-sql-server-without-ssis

After I got the above working, I copied all the global variables/constants from the routine, which included

    Const CS As String = "Driver={SQL Server};" _
                       & "Server=**;" _
                       & "Database=**;" _
                       & "UID=**;" _
                       & "PWD=**"
    Dim DB_Conn As ADODB.Connection
    Dim Command As ADODB.Command
    Dim DB_Status As String

into a similar module in another spreadsheet. I also copied

Sub Connect_To_Lockbox()
    If DB_Status <> "Open" Then
        Set DB_Conn = New Connection
        DB_Conn.ConnectionString = CS
        DB_Conn.Open  ' problem!
        DB_Status = "Open"
    End If
End Sub

I added the same reference (ADO 2.8)

The first spreadsheet still works; the seccond at DB_Conn.Open pops up "Run-time error '-214767259 (80004005)': [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" Removing the references on both, saving files, re-opening, re-adding the references doesn't help. The one still works and the other gets the error.

?!?

Answer 1

When the error pops up, check your "locals" windows to see what the CS holds. View > Locals Window

Answer 2

Problem: Your constant isn't found by the compiler.

Solution: With the constant being located in a separate module, you'll need to set it as Public for the other code to see it.

Proof:
In order to prove this theory you can do the following: Open a new Excel spreadsheet Go to the VBA designer and add a new module In this module put:

Const TestString As String = "Test String"

Then add the following code to ThisWorkbook:

Public Sub TestString()
   MsgBox (TestString)
End Sub

After adding this return to the workbook and add a button, selecting "TestString" as the macro to run when clicked.

Click the button and a blank message box will appear. Go back to the VBA designer and change the const in Module1 to Public Click the button on the spreadsheet and you should now see "Test String" in the message box.