ODBC Connection DSN and Linked Table Manager Giving Different Results: The link table manager in access database only gives the master tables

EFS 0

I am setting up a DSN and a Linked Table Manager link all on the same Access database on the same computer with the same database Login and associated User (and of course the same database). The DSN gives me the tables I need, but the Linked Table Manager does not, it only gives me the Master tables, what am I doing wrong?

Oury Ba-MSFT 16,391 Reputation points Microsoft Employee

2024-04-23T21:04:59.22+00:00

@EFSAdmin Thank you for reaching out.

Have you tried the below steps.

Did you tried refreshing the data source and linked Tables.?

Are tables showing status as "failed"? If yes, please try to relink them.

Regards,

Oury
EFS 0 Reputation points

2024-04-23T21:16:25.9433333+00:00

Yes I have tried Refreshing the link in Linked Table Manager, only the Master tables appear. The tables work fine and appear like they should when I use DSN, they do not appear in the table list when using Linked Table Manager so I can't test them but when I have elected one of the sys tables they work great, but I am trying to get something other than the Master DB.

1 answer

Tom van Stiphout 1,621 Reputation points MVP

2024-04-23T23:57:01.24+00:00

In the LTM I check the SQL data source, click Relink, and the Edit Connection String dialog appears.
It should have an explicit "DATABASE=" section, as shown here.
If that is missing, the default database will be used, which could be the "master" database.
Please sign in to rate this answer.
EFS 0 Reputation points

2024-04-24T14:28:27.8+00:00

Correct, and it's not missing, I'm copying the link in the Azure Portal as well which has everything in it (at least I think). This is my string in LTM; I have changed the server name and the credentials are out for privacy.

Driver={ODBC Driver 18 for SQL Server};Server=tcp:test.database.windows.net,1433;Database=AdventureWorksLT;Uid=;Pwd=;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

I also have a custom DB I made on the server which is having the same problems, it does not have a schema like AdventureWorksLT does, and I cannot see any tables in it either, so i know it has nothing to do with the schema. I have tried using different SQL drivers, even just "SQL Server". I have given permissions to the AdventureWorksLT schema, but nothing seems to be working.

Oury Ba-MSFT 16,391 Reputation points Microsoft Employee

2024-04-24T23:38:22.52+00:00

EFSAdmin Sorry to hear that the previous answers were not helpful.

Could you please check the link below to see if that could help in anyway.

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

Regards,

Oury

EFS 0 Reputation points

2024-04-25T16:05:55.11+00:00

Thank you for this links, but it has not offered anything new.

This DSN connection string DOES work in LTM (Linked Table Manager) - I can see the AdventureWorksLT tables

DSN=Azure;Description=AzureDB;UID=DBUser;PWD=;Trusted_Connection=No;APP=Microsoft Office;DATABASE=AdventureWorksLT;

This does NOT work in LTM, but I need it to:

Driver={ODBC Driver 18 for SQL Server};Server=tcp:{server_name},1433;Database=AdventureWorksLT;Uid=DBUser;Pwd=;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;

Note: We CANNOT/WILL NOT use DSN nor a DSNLess VBA connection. We only use LTM here.

DBUser has been added to db_datareader and db_datawriter and the we have granted select/insert/update/delete to the schema for DBUser.

1433 is open and working fine (obviously since DSN works using it).

Albert Kallal 4,651 Reputation points

2024-04-26T19:10:59.9333333+00:00

Well, a few things, when you use the Access LTM, then if you choose a FILE dsn, then the result is a DSN less connection! In fact, you can even delete the FILE dsn after having linked the tables, and the connection will still work.

And you can move the application to another computer, and it will continue to work! So you ARE using a DSN-less connection when you use the LTM and choose a FILE dsn. Once you linked, then the actual DSN is NOT used anymore, nor required!

The above only applies to a FILE DSN (which is the default). This is a preferred default, since as noted, it means each workstation does not require any configuration for the application to work. Hence, keep in mind that further changes to the DSN are ignored, not used, nor seen anymore by Access.

I should also point out it is rather easy when creating the DSN from Access that you skipped, or missed the page that lets you set/select the database, else it will default to master, and you not see your database, but ONLY see "master" as you well noted.

That option is this one:

So, it can be rather ease to miss the above step. And then toss in the fact that once the tables are linked (using a FILE dsn), then from that point on, you are running a DSN less setup. If you want to change the above database, you need to re-create the FILE dsn, and re-link using that new DSN. And again, keep in mind that once you linked, the FILE dsn is no longer used, nor even required to exist.

EFS 0 Reputation points

2024-04-29T20:13:18.7266667+00:00

Thanks for the input. Just to be clear, I did state the database in the User DSN that I created as well as the LTM connection. You can see that in my connection strings above. That's why I am able to see the tables through that connection. I am also stating the DB in the LTM connection string - but I can only see the Master tables. This should not be the case since I am explicitly stating the AdventureWorksLT DB in the LTM connection string. This is a problem.

Also, note that I did not use a File DSN but a User DSN to create that connection string and when I had another user open the DB they could not connect to those tables. I'm assuming a User DSN does not carry through the DB, only a File DSN?

Albert Kallal 4,651 Reputation points

2024-04-29T20:43:08.8866667+00:00

Ah, very well (my bad). I read that as the user has a DSN, not that you have a USER dsn!

And yes you are correct in terms of "carry though".

I like the term "discarded", since once linked the FILE DSN is no longer used nor required.

And you still see the connection using the LTM anyway even having used that FILE DSN.

So, in fact, the System/User DSN is the one that "always" flows though into Access, and they can be modified without even opening Access....

(so, this is some semantics here - no worries!).

As for only seeing that master table? I would re-make the DSN, and make sure you select the above change database - you ONLY see/get that panel when creating the DSN - not after it's been created.
Sign in to comment