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?

Regards,
Mark

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
        rs.Close
    Else
        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
ASKER CERTIFIED SOLUTION
ste5an
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