Tables not showing up when linking SQL Server to Access using ODBC connection

Question

I am learning to link the back end of a database in SQL Server express 2016 to the front end in Access 2016. The tables were originally in Access and then I exported them to SQL Server. Now I am attempting to link them to the front end in Access but the problem is that they are not showing up in the possible tables that I can link. The answers I found when searching this up was that I simply did not have permission, but I am on my laptop and there are no other users and I am using Windows Authentication. I am doing this using the Northwind sample database. The following shows that I only see the system tables as options when trying to link not the desired tables.

This shows the settings of my data source.

Answer 1

I just had your problem. Here's how I fixed it.

I was testing a new SQL database, a copy of an existing one that worked fine. My problem was the same as yours: I had a view that wasn't showing when I tried to connect a link to it from Access.

It turned out that the current production database, when I looked in ODBC, had a value in the Default Database, even though that option wasn't checked. Let's say that value was DBABC123. I could check the box, and see other options in the drop-down, like "master", but "DBABC123" was the one it was using. Views for that ODBC source properly gave me the view I wanted to link to.

The new database, set up in ODBC the same way (just with a different server and different database username/password) would connect, but showed the same screen you showed above. When I checked the ODBC connection, the value in that Default Database field was "master". It was unchecked.

What pointed out the issue is that I could connect to the new database through Excel just fine, but you see a list of all the "databases" associated with the ODBC source, and we'd just expand the "DBABC123" one to find the view.

So, I went into ODBC and changed the Default Database field so it was checked. When the drop-down values displayed, "DBABC123" was in the list, so I picked that, and carried on with finishing the connection edit, and tested the connection (it worked, but it worked before, too).

This time, though, when I tried to link to the table from access, my view showed up. It was now looking at the proper database.

Good luck!

Answer 2

I know this was 8 months ago. Your problem may be that you did not select a default database. When linking, select the options button and select the database that is not appearing. I hope this helps.