DSN File not showing up under Link table manager wizard.

Hi Experts,

I just created a system dsn (see 1st attachment).
And when trying to use it in Access wizard to select as data source, its not showing up there...
See second attachment.
Untitled.png
Untitled1.png
LVL 6
bfuchsAsked:
Who is Participating?
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.

David ToddSenior Database AdministratorCommented:
Hi,

My bet is that you've created a 64-bit dsn, and the Access you're running is 32-bit.

Which version of windows are you creating this on? Then I'll try to find the different odbc managers for you. They vary by windows version.

HTH
  David
bfuchsAuthor Commented:
Hi,
Win7Pro 64 Bit.
Thanks,
Ben
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
I find it easiest to create the DSN file you need, and use that to make your connection.

Make a file with NotePad or another text editor with the following, and substitute your differences:
[ODBC]
DRIVER=SQL SERVER    [or whatever! this is an old driver, btw -- still works but is slow]
UID=MyUserID
DATABASE=MyDatabaseName
SERVER=MyServerName
PASSWORD=MyPassword

Open in new window

Once you have a DSN file, you can use it to link to tables in that database from Access.

From the ribbon, choose: EXTERNAL Data.
Then click the ODBC Database icon  in the Import & Link group.
You have option buttons to Import or Link -- choose "Link to the data by creating a linked table"
OK
On the File Data Source tab, paste or type the complete path\file for the dsn file in the box called "DSN Name". For instance:
C:\myPath\dsn\MyDatabase.dsn
OK
In the SQL Server Login dialog box, if you are using Windows* authentication, check the "Use Trusted Connection" box
OK
You should now see a list of tables you can connect to. Select which tables you want.
You may want to check the "Save Password" box.
OK

* for Windows/Active Directory

have an awesome day,
crystal
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

David ToddSenior Database AdministratorCommented:
Hi,

The ODCB Data Source Administrator link in Control Panel says 32-bit. The one from Administrator Tools doesn't mention architecture. Hard to tell from either the inside or outside which is which. This confusion seems to be reduced from Windows 8 upwards.

So All I'm saying here, is if you tried one ODCB Data Source Administrator link, try the other one.

HTH
  David
bfuchsAuthor Commented:
@Crystal,
Followed your instructions & getting the attached.

@David,
When I type ODBC on start menu, only one comes up, dont see option to choose which bit version.

Thanks,
Ben
Untitled.png
David ToddSenior Database AdministratorCommented:
Hi Ben,

Dont type on the start menu.

Can you find Control Panel? There is one ODBC there. In Control Panel there should be a link to Administrator Tools? In there is a different ODBC link.

Here is the catch

The link in Administrator tools, that on my system is marked 32-bit is in c:\Windows\System32

The one from control panael is in c:\Windows\SysWOW64

Both link to exe's named odbcad32.exe, but are slightly different sizes.

Now you see why I'm insisting on doing this very carefually and deliberately, and just searching the start menu!

HTH
  David
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
hi Ben,

for your driver, try:
SQL Server Native Client 10.0

Open in new window

maybe I was wrong about the generic form always working -- or maybe something is not right in other parameters. Eliminate extra spaces in your DSN file. Make sure it is stored as ASCII.

To get the right parameters, link to a table in that database and look at its Connect string:
  • Ctrl-G for Debug Window, then type:
  • ?currentdb.TableDefs("MyTableName").Connect
  • and press ENTER

where MyTableName is the name of the table you want a connect string for

If you didn't save credentials when you connected, UID and PASSWORD won't be listed -- but am assuming that is not the problem (but it could be)

~crystal
bfuchsAuthor Commented:
@Crystal,
Thats still giving me an error., see attached.

@David,
Under control panel there is no ODBC icon, see attached.
Under administrative tools there is the one I created.

Thanks,
Ben
Untitled.png
Untitled1.png
David ToddSenior Database AdministratorCommented:
Hi Ben,

from a command prompt, find the windows folder - often its c:\Windows

type dir /s odbcad*.exe

and wait.

Regards
  David

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
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Ben, can you open what you are trying to connect to?
bfuchsAuthor Commented:
That worked,
Thank you!
bfuchsAuthor Commented:
@Crystal,
can you open what you are trying to connect to?
Sure.
Thanks,
Ben
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ben,

  Just a couple of additional points on this:

1. 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.   You must use the version that matches the Office install.

2. The 32-bit version of the ODBC Administrator tool displays 32-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs. The 64-bit version of the ODBC Administrator tool displays 64-bit system DSNs, 32-bit user DSNs, and 64-bit user DSNs.

   They do this because of backward compatibility issues and it can make it very confusing on why you are seeing something or not in something like Access.  

   So when creating DSN's, it's always best to create a system DSN and suffix it with the version you created it under.   i.e. you use  bit version, put _64 on the end of your DSN name.  That keeps everything nice and neat and you'll never mix things up.

HTH,
Jim.
bfuchsAuthor Commented:
Hi Jim,

Couple of questions.

1- Does it mean I cannot use the wizard to create odbs they way I used to, instead always have to open the odbccad file from that folder mentioned using the win browser?
2- How can I see which bit version of Office we have (Office365)?
3- Why is the file named odbcad32 if its the 64 version? .
4- How do I find out the version of the ODBC Administrator tool?

Thanks,
Ben
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<1- Does it mean I cannot use the wizard to create odbs they way I used to, instead always have to open the odbccad file from that folder mentioned using the win browser?>>

 It depends on the OS.  With Win 10, you get both choices.  For earlier OS's, from control panel you will always get the 64 bit version if your using a 32 bit OS, otherwise you get the 32 bit version.

<<2- How can I see which bit version of Office we have (Office365)?>>

https://support.office.com/en-us/article/about-office-what-version-of-office-am-i-using-932788b8-a3ce-44bf-bb09-e334518b8b19

<<3- Why is the file named odbcad32 if its the 64 version? .>>

  you'd have to ask Microsoft, but I would assume a backwards compatibility issue.

<<4- How do I find out the version of the ODBC Administrator tool>>

 Your mean 32 or 64 bit?  you can't tell in the applet.   Only by the drivers available and the DSN's it creates can you tell what your working with.

 Your best bet is to create a shortcut for each, and name the DSN's as I suggested.  You'll never get them mixed up that way.

Jim.
David ToddSenior Database AdministratorCommented:
Hi,

It was really annoying that both the 32-bit and 64-bit applet is called odbcad32.exe. It is what it is. And This really first appeared as an issue on Windows 7. Newer versions of Windows manage this a lot better.

HTH
  David
bfuchsAuthor Commented:
you will always get the 64 bit version if your using a 32 bit OS, otherwise you get the 32 bit version.
Is this a typo or why is that so, for 32 I get 64 and vice versa?
So being that on the PC in question have OS Win7 Pro 64, means the ODBC tools is 32 bit, and my office version is 32 (thanks to the link you posted...-:), why did I had to create a dsn using 64 version?
Only by the drivers available and the DSN's it creates can you tell what your working with.
Can you tell from the attached list of drivers which version do I have?

Thanks,
Ben
Untitled.png
David ToddSenior Database AdministratorCommented:
Hi Ben

That pic looks like the 64-bit drivers

Both odbc administrators from Windows 10
HTH
  David
bfuchsAuthor Commented:
Hi,
That pic looks like the 64-bit drivers
Are you referring to your pics or mine?
Yours have (32-bit) showing on top, mine don't.
Thanks,
Ben
David ToddSenior Database AdministratorCommented:
Hi,

Your pic

My pic shows BOTH 32bith and 64bit administrators side-by-side.

Your pic looks more like the 64-bit one in my pic than the 32-bit one.

HTH
  David
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
you will always get the 64 bit version if your using a 32 bit OS, otherwise you get the 32 bit version.
Is this a typo or why is that so, for 32 I get 64 and vice versa?
So being that on the PC in question have OS Win7 Pro 64, means the ODBC tools is 32 bit, and my office version is 32 (thanks to the link you posted...-:), why did I had to create a dsn using 64 version?

  Yes and no.   I typed "if" instead of "unless".   What I was trying to convey and didn't say well was the obvious; on a 32 bit OS, you would always get the 32 bit version and under a 64 bit OS, you would always get the 64 bit version.    But under a 64 bit OS, you will have both versions and must use the one that matches the "bitness" of the Office install that you have.  Otherwise, the apps won't be able to see the DSN that you created.

 It's only the ODBC applets that show both 32 and 64 bit user DSN's, which makes it really confusing.

 Create shortcuts for the two applet versions and suffix your DSN's with _32 or _64 and you won't have any issues.

Jim.
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
Databases

From novice to tech pro — start learning today.