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