DSN File not showing up under Link table manager wizard.

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

Hi,
Win7Pro 64 Bit.
Thanks,
Ben

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

Select allOpen 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

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

@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

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

hi Ben,

for your driver, try:

SQL Server Native Client 10.0

Select allOpen 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

@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

Ben, can you open what you are trying to connect to?

That worked,
Thank you!

@Crystal,

can you open what you are trying to connect to?

Sure.
Thanks,
Ben

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.

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

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

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

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

Hi Ben

That pic looks like the 64-bit drivers


HTH
  David

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

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

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.