CODEWITHSUNDEEP
excelvbaadodb
WGroleau
WGroleau

Reputation: 455

ADODB.Connection undefined

Reference 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 into the same module
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.

?!?

Upvotes: 0

Views: 7051

Answers (4)

Deep
Deep

Reputation: 11

I observed the same error message and in my case nothing had changed. I wondered if my odbc driver needed to be reinstalled (based on what i read online). In any case, restarting excel did the trick. Sometimes the solution is much simpler. :-)

Upvotes: 1

Christian
Christian

Reputation: 14067

I realize that this question is really old. But for the record I want to document my solutions for the error here: It was a data related error in a spreadsheet! A column was formatted as date and contained a value 3000000. Changing the Format to numbers solved the Error 80004005.

Upvotes: 0

Stevoni
Stevoni

Reputation: 351

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.

Upvotes: 0

SnakeWasTheNameTheyGaveMe
SnakeWasTheNameTheyGaveMe

Reputation: 2562

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

Upvotes: 0

Related Questions