We help IT Professionals succeed at work.

Simple ODBC Connection Problem (MS Access / Windows 7)

Hi Experts,

I have very limited experience with setting up ODBC connections, but I'm hoping this is a simple problem to solve (I've set this at 500 points though as I need an answer within the next 12 hours please!).

I'm having a problem getting my Access 2010 database to find a 64bit ODBC connection on a Windows 7 machine.  I've installed Sage 50 2012 (version 18) and ran the ODBC installation executable.

It appears that the latter created the DSN 'SageLine50v18'.  I found this in the 64bit registry and also using the 64bit ODBC Administrator (%SystemRoot%\SysWow64\odbcad32.exe).  It does not appear when I check with the 32bit ODBC Administrator (%SystemRoot%\system32\odbcad32.exe).

My database is using the following connection string:
"ODBC;dsn=SageLine50v18;uid=MANAGER;pwd=;"
however the connection fails.

This setup was working in MS Access 2000 on XP with Sage 50 2009 (Version 15) with the following connection string:
"ODBC;dsn=SageLine50v15;uid=MANAGER;pwd=;"

I'm guessing that I have to modify the connection string to find the DSN declared in the 64 bit registry, but how I would do this is beyond me.  Am I on the right track or is there something else that I have to do?

Thanks for your help in advance.

Oliver
Comment
Watch Question

Top Expert 2012
Commented:
>and also using the 64bit ODBC Administrator (%SystemRoot%\SysWow64\odbcad32.exe).
ATTENTION: This is 32bit ODBC Administrator.

>It does not appear when I check with the 32bit ODBC Administrator (%SystemRoot%\system32\odbcad32.exe).
This is 64bit ODBC Administrator.

Do you consider that a 64-bit Windows has:
- two different versions of the system directory:
      System32 - Windows System folder for 64-bit files
      SysWOW64 - Windows System folder for 32-bit files

- two versions of the regsvr32.exe file:
      The 64-bit version is %systemroot%\System32\regsvr32.exe
      The 32-bit version is %systemroot%\SysWoW64\regsvr32.exe

     (open up the command prompt with "run as administrator")

To manage a data source that connects to a 32-bit driver under 64-bit platform,
use c:\windows\sysWOW64\odbcad32.exe
Commented:
My first take is that the first poster has put his finger on the matter.  

But if that doesn't work, maybe there is a solution in the following article:
http://www.accountingweb.co.uk/group-thread/linking-sage-access-programatically
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
To clarify a bit (no points please):

You should use the applet in the SYSWOW64 directory to build your DSNs, not the one in the System32 directory.

Author

Commented:
Hi Guys,

Thanks for your help and clarifications.  I discovered that my 4 ODBC linked tables were using different connection strings:
 "ODBC;dsn=SageLine50v18;uid=MANAGER;pwd=;"
 "ODBC;dsn=SageLine50v15;uid=MANAGER;pwd=;"
I thought that relinking them all to ensure they were all using the same DNS would solve the problem but it didn't.

By using the simple connection string "ODBC;" the application lists all available DNS and SageLine50v18 was listed as an available system DNS.  I selected this and entered the uid (leaving the password blank), but I received Error No 3146 'ODBC -- call failed'.  I'm not sure why this is.

I tried again with "ODBC;" and this time selected the 'Machine Data Source' tab and selected New to create a new User Data Source.  Following the wizard, I selected 'SageLine50v18', clicked Next and Finish.  I then entered:
 - DNS: SageLine50v18_Whitehead
 - Data Path: P:\Accounts\Sage Data\Company.000\ACCDATA (path to Sage account data)

I then amended my connection string to:
 "ODBC;dsn=SageLine50v18_Whitehead;uid=MANAGER;pwd=;"

This worked but is not ideal as I've used a User DNS and therefore I've had to set these up on all workstations that will be using the application (only 3 workstations fortunately).  

Obviously the ideal solution is to use the System DNS.  For future reference and for the benefit of others with a similar problem, any ideas why I couldn't connect using the System DNS generated by Sage's :\ODBC Install\setup.exe (Error No 3146  'ODBC -- call failed').

Would recreating the System DNS using the applet in the SYSWOW64 directory fix this do you think?   I assume in this case that 'applet' refers to c:\windows\sysWOW64\odbcad32.exe.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
I've always had better luck building the connections directly in Access, and not by using the external applets. Access always builds the one that it needs, and always sets the params and such correctly.

If you're running 32-bit Office, you must use the applet in the SysWOW64 directory. Using the other will result in a 64-bit DSN, which 32-bit Office can't use.

Author

Commented:
Hi LSMConsulting,
I'm currently using the following to establish the connection with Sage when the application starts up so that the user doesn't get prompted for Sage login details whilst using the database.  

    Dim td As DAO.TableDef
    Dim db As DAO.Database
    Dim strConnection As String
   
    strConnection = "ODBC;dsn=SageLine50v18_Whitehead;uid=MANAGER;pwd=;"
    Set db = CurrentDb()
    Set td = db.TableDefs("COMPANY")
    With td
        .Connect = strConnection
        .RefreshLink
    End With
    Set td = Nothing
    Set db = Nothing

Are you suggesting that I can also build the DSN from Access?  If so how would I do that?
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
You can just use DSNLess connections instead of building a DSN:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

Author

Commented:
Thanks Guys, all your posts were very helpful and relevant to my problem.  I'm now a lot wiser on the ODBC front!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.