troubleshooting Question

Unable to connect to SQL 2000 from VBA Excel

Avatar of Mark Bakelaar
Mark BakelaarFlag for Norway asked on
Windows 10VBASQL
6 Comments1 Solution66 ViewsLast Modified:
Hi Experts,

I have an ODBC connection setup in Windows 10 to a SQL 2000 DB. Using this connection I can connect from Excel and retrieve data from any table in the DB. This is also working fine.

I want to do the same thing from VBA Excel. Below the code (names of DB and Initial Catalog are changed). I can connect to the DB, but I get an error on the SQL query. The message is "Invalid object name 'SALESTABLE'". I get the same message if I change to another table.

What can be the issue?


Sub ConnectSqlServer()

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim strConnString As String
    ' Create the connection string
    strConnString = "Provider=SQLOLEDB.1;Data source=MyDatasource;" & _
                    "Initial Catalog=MyCatalog;" & _
                    "INTEGRATED SECURITY=SSPI;"
    ' Create the Connection and Recordset objects
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    ' Open the connection and execute
    conn.Open strConnString
    Set rs = conn.Execute("SELECT * FROM SALESTABLE")
    ' Check if there is data
    If Not rs.EOF Then
        Sheets(3).Range("A1").CopyFromRecordset rs
        MsgBox "Error: No records returned.", vbCritical
    End If

    ' Clean up
    If CBool(conn.State And adStateOpen) Then conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Sub
Senior Developer

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros