0

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.System Tables

This shows the settings of my data source.my data source.

2 Answers 2

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!

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.