Search code examples
sql-serverms-accesslinked-tables

Access Linked Tables can't see the new SQL Server tables

HISTORY

In the 1990's, my predecessor built an Access database for my company (four of them actually). In the aughts, the next employee built a SQL Server 2008 Express on Windows Server 2003 and moved the data there. Now it's the 20's and I'm trying to upgrade to Windows Server 2019 and SQL Server 2019 Express. The database has already been upgraded to Access 365.

Here's what I've done so far:

CURRENT SITUATION

I backed up the databases from SQL Server 2008 and restored them to SQL Server 2019. After some troubleshooting and reconfiguring I was able to get my SQL Server Management Studio to connect to the 2019 databases and it can see all the tables. Access can connect to the 2019 databases as well.

THE PROBLEM

When I go to re-link the tables, it connects successfully, but says it can't find the tables in the database. SQL Server Management Studio can see the tables just fine. The tables are definitely there and the names are exactly correct. I can't see any reason for this error.

Any suggestions on where I should look next would be appreciated.

Solution

  • Access requires the database to be specified in the connection string or DSN, else it will look for tables in master, and there shouldn't be any.

    How to do that can vary slightly between driver versions, for example with ODBC Driver 17, you can use the Change default database to option:

    enter image description here

    Alternately, you can alter the user default database, but since this is per-user, I recommend against that.

    Or, go DSNless. I strongly recommend DSNless connections since they don't rely on the DSN on users machines being configured right, thus allow for the database to be used on multiple machines without any configuration on the individual machines.