We help IT Professionals succeed at work.
Get Started

Unable to connect to SQL 2000 from VBA Excel

60 Views
Last Modified: 2020-03-05
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
Comment
Watch Question
Senior Developer
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant

An Experts Exchange subscription includes unlimited access to online courses.

Get Started
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE