I have an Excel spreadsheet that successfully uses data from an Access database over connections defined over the interactive Data / Connections definitions, and also using VBA code and ADODB connections used to access directly various tables using straightforward SQL statements.
Because all the tables in that Access DB should be migrated to SQL Server, I started to duplicate and modify the code I had for connecting to Access in order to connect to SQL Server tables. And that doesn't work.
Strange enough, The code to just establish the connection works. It looks like that:
' Instantiate the ADO-objects.
Set connSQLS = New ADODB.Connection
Set rsSQLS = New ADODB.Recordset
Debug.Print "Access connection var and recordset var defined"
' Path to the database.
sDBServer = "MyServer"
sDBName = "MyDBTest"
' Create the connection string
sConnectionStringSQLS = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=" & sDBServer & ";" & _
"InitialCatalog=" & sDBName & ";"
'Open the connection
This succeeds. I have an ON Error GoTo above it, and that is not triggered. The connection string is built from defining a connection over the Menus Data / Connections and copy/pasting the useful bits.
However, when I want to access one table to check if everything works, the following code fails:
' Check connection
rsSQLS.Open "SELECT Max(ID) FROM MyTable", connSQLS
The error is: Invalid object name 'MyTable'
For the "MyTable" string, I've tried various syntaxes:
- <table name> appearing after "dbo." when looking at the DB tables in SQL Server Manager (which is what I use for Access tables when using the same code, just with the connection string for the Access DB)
- dbo_<table name>
- dbo.<table name>
None of it works.
What am I doing wrong ?
Thanks for help.