ADODB.Connection undefined

Question

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.

?!?

By another spreadsheet I'm assuming in the same document, correct? — Stevoni
– Stevoni, Commented Nov 19, 2009 at 20:30
@Stevoni: No, it was copying a module from one spreadsheet to another. Both spreadsheets had to connect to the same SQL Server instance, so the only thing to change would be the authentication for the particular DB. I am now retired,and can't say how I resolved it, but I do know it eventually worked. — WGroleau
– WGroleau, Commented Mar 21, 2016 at 16:26

Deep · Accepted Answer · 2016-03-21 15:56:59Z

1

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

answered Mar 21, 2016 at 15:56

Deep

111 bronze badge

Sign up to request clarification or add additional context in comments.

1 Comment

WGroleau Over a year ago

Restarting did not fix it for me, as the question text shows.

SnakeWasTheNameTheyGaveMe · Accepted Answer · 2009-11-19 20:03:24Z

0

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

answered Nov 19, 2009 at 20:03

SnakeWasTheNameTheyGaveMe

2,5822 gold badges28 silver badges73 bronze badges

Comments

Stevoni · Accepted Answer · 2009-11-19 20:29:47Z

0

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.

answered Nov 19, 2009 at 20:29

Stevoni

3517 silver badges18 bronze badges

1 Comment

WGroleau Over a year ago

No, they were in the same module. Sorry that wasn't clear before.

Christian · Accepted Answer · 2012-11-27 13:05:19Z

0

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.

answered Nov 27, 2012 at 13:05

Christian

14.3k2 gold badges38 silver badges51 bronze badges

1 Comment

WGroleau Over a year ago

Not the same thing. Debugger showed that the error occurred trying to execute the open connection method.

Collectives™ on Stack Overflow

ADODB.Connection undefined

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related