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.
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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.
0
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

Kelvin
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
0
John TsioumprisSoftware & Systems EngineerCommented:
You can always work with DSNless philosophy and stop chasing DSNs...check this here
1
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.
Mike74DSNAttempPics.docx
0
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.