• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 77
  • Last Modified:

Linking to Backend SQL Server DB Using Linked Table Manager

Short version:
How do I find the System DSN I created when trying to link to a SQL backend from the Linked Table Manager in Access?

Long Version:
My main work computer crashed yesterday and I'm trying to get things going on another computer in the office.  One of my Access 2013 apps uses a SQL Server back end.

SQL Server is loaded onto the machine and I restored the backup of the SQL DB from the crashed machine onto on the new machine.

I also restored all of my Access projects, most of which use an Access FE and BE.

I had someone else set up the SQL Access link on the old machine but I'm tyring to work my way thru it, with out any success.

Here's what I've done:
I created a DSN within the ODBC Date Source Administrator.  It  is a System DSN named 'JTSDSN',  

On the crashed machine the table were all linked to the SQL back end.  On my new machine I open the front end accdb, then the linked table manager.  I click the 'always prompt for new location' box, select all of the tables and click OK.

At that point I'm presented with a 'Select Data Source' box.  This selection box has two tabs 'File Data Source' Machine Data Source', not 'System Data Source.  The default location is the 'MyDocument' folder but my DSN is not shown there.  What directory do I have to navigate to, to find my DSN.  When I created the DSN I was given the opportunity to select the location where it was created, so it went wherever the default location is.

Any help or suggestions in getting this linked will be appreciated.  This is all unproductive time and have deadlines approaching on this application.
1 Solution
Ian BerrymanCommented:
If you saved the DSN as a system data source, it should be under the Machine Data Source tab. Then click the System Data Source radio button and click next. You should then see a list of existing system DSNs.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Humm .... seems it should have just relinked,
On a non crashed machine, can you right click over an SQL table, open in design view ... right click and select Properties ... you may be able to see it here.
Kelvin SparksCommented:
If you use a 64 bit machine and have 32 bit office, you must create the dsn using the 32 bit ODBC Manager. If not in your start menu is located in C:\Windows\SysWOW64 and is the file odbcad32.exe

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Pushpakumara MahagamageVPCommented:
The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.
The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder.

The Odbcad32.exe file displays the following types of data source names (DSNs):

System DSNs
User DSNs
John TsioumprisSoftware & Systems EngineerCommented:
You can always work with DSNless philosophy and stop chasing DSNs...check this here
mlcktmguyAuthor Commented:
Thanks for all of you interest and suggestions but I'm still stuck.  I am attaching a document with snapshots of the entire process I went thru to create the DSN and then what I see (acutally don't see my DSN) when I try to link the files from the linked table manager.

I experimented with the DSNLess but so far that hasn't been successful either.
mlcktmguyAuthor Commented:
I finally was able to get the tables to link to the SQL backend DB by creating a 32 bit DSN.

Thanks to all for your help and interest in my issue.

I have a followup issue that I''m going to post as soon as I close this one.  That one will relate to creating a valid connections string to use when calling a stored procedure.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now