Trouble connecting to SQL from Access

Brad White 41 Reputation points
2021-12-09T06:47:50.987+00:00

I have an existing MS-Access front end, that connects to MS-SQL on the back end.
I have the SQL database restored and everything is working fine.
I've made some changes to both the app and the database.
Now I want to check the behavior against the original database format.
So I restore another copy of the SQL database.
Add user.

Next I want to programatically relink the passthru tables with the new database.

CODE
In Access, First we make sure we can connect to the DB:

strConnect = "ODBC;DRIVER=" & strDriver & ";SERVER=" & strServer & ";DATABASE=" & strDatabase & ";Network=DBMSSOCN;UID=" & strUsername & ";PWD=" & strPassword & ";Trusted_Connection=No"


Set dbCurrent = DBEngine(0)(0)
Set qdf = dbCurrent.CreateQueryDef("")

With qdf
    .Connect = strConnect
    .sql = "SELECT CURRENT_USER"
    Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)
End With

This appears to work successfully for both DBs. Although, now I'm thinking this doesn't prove as much as I thought it did.

Then, I am refreshing the connection for the linked table, by deleting and recreating, like so

strConnect = "ODBC;DRIVER=" & strDriver & ";SERVER=" & strServer & ";DATABASE=" & strDatabase & ";UID=" & strUsername & ";PWD=" & strPassword & ";Trusted_Connection=No"
 CurrentDb.TableDefs.Delete "destTable"       

'using dbAttachSavePWD saves the user name/password with the attached table, using 0 does NOT
'Set td = CurrentDb.CreateTableDef("destTable", dbAttachSavePWD, "dbo.srcTable", strConnect)
Set td = CurrentDb.CreateTableDef("destTable", 0, "dbo.srcTable", strConnect)

I've tried it with and without the SavePWD.

I can connect to the first DB but not the second.

Obvious culprit is user or permissions. All looks ok.
So I created another user and added it to both databases.
I can connect to the first instance but not the second.
This same database was working when they backed it up, and worked when I restored it the first time.

I checked the SQL activity log and I can see both calls, and both look successful.
That makes it look like not a user or permissions issue.
Again, with the first DB, this succeeds for all tables.
For the second DB, I get:

Code: 3011

Msg: The Microsoft Access database engine could not find the object 'dbo.srcTable'. Make sure the object exists and that you spell its name and the path name correctly. If 'dbo.srcTable' is not a local object, check your network connection or contact the server administrator.

I've restarted the SQL service and the machine. Dropped all connections. Taken both DBs offline and back on. Tried to force TCP instead of named pipes. Tried turning off the firewall and anti-virus. I've copy/pasted the name of the table from the working DB to the other to ensure there were no typos or funny chars.

If I run this code to link the tables to the first DB, go to the Linked Table Manager, those tables show as linked. Change the connection string to use the new DB, Refresh and Relink give the same error:

The Microsoft Access database engine could not find the object 'dbo.srcTable'. Make sure the object exists and that you spell its name and the path name correctly. If 'dbo.srcTable' is not a local object, check your network connection or contact the server administrator.

with no error code number.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,772 questions
Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
823 questions
0 comments
Accepted answer
  1. Dave Patrick 426.1K Reputation points MVP
    2021-12-09T16:43:12.963+00:00

    I'd still try to manually relink them. Then check the hidden system table MSysObjects to see that the connection string for the problem linked table looks correct. Also try linking a new MDB for testing.

    --please don't forget to upvote and Accept as answer if the reply is helpful--

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Dave Patrick 426.1K Reputation points MVP
    2021-12-09T16:24:09.977+00:00
  2. Dave Patrick 426.1K Reputation points MVP
    2021-12-09T21:32:35.07+00:00

    I run this code to link the tables to the first DB

    I'd skip that and try to manually create new linked tables.

    --please don't forget to upvote and Accept as answer if the reply is helpful--