0

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.

?!?

Community
  • 1
  • 1
WGroleau
  • 448
  • 1
  • 9
  • 26
  • By another spreadsheet I'm assuming in the same document, correct? – Stevoni Nov 19 '09 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 Mar 21 '16 at 16:26

4 Answers4

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

Deep
  • 11
  • 1
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.

Christian
  • 13,285
  • 2
  • 32
  • 49
  • Not the same thing. Debugger showed that the error occurred trying to execute the open connection method. – WGroleau Mar 21 '16 at 16:34
0

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

SnakeWasTheNameTheyGaveMe
  • 2,522
  • 2
  • 28
  • 70
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.

Stevoni
  • 341
  • 7
  • 18