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
On part of the problem is that you're mixing the ways you open an recordset and the other is propably an object name error. Test how the table is named:
Open in new window