This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(182.40000000000003, 50%, 27%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EE%3C/text%3E%3C/svg%3E)
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?
@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
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(256, 41%, 34%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETV%3C/text%3E%3C/svg%3E)
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.
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.
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
Thank you for this links, but it has not offered anything new.
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).
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(262.40000000000003, 46%, 35%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAK%3C/text%3E%3C/svg%3E)
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.
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?
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.
So I figured this out. When you are in LTM a second window pops up asking for which server and the credentials you are asking for. You need to select the "Options" button. This will give you a second chance to select which database you are actually trying to connect to. Now you will see all of your custom tables in the DB you have selected. It's very strange that even though you paste in the server URL, the DB, and the credentials in the LTM custom SQL connection string that it doesn't recognize any of it and makes you select everything all over again. Very strange.
After you add the custom connection string into LTM a popup window appears asking you to reenter the URL and credentials - here is where you need to click on the Options button where you can select the default database. It doesn't care what you put in the connection string, it uses the drop down in the extended form instead - then your tables appear.