Simple ODBC Connection Problem (MS Access / Windows 7)

Posted on 2012-08-14
Last Modified: 2012-08-16
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:
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:

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.

Question by:Oliver Wastell
    LVL 15

    Assisted Solution

    >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
    LVL 8

    Assisted Solution

    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:
    LVL 84
    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.
    LVL 3

    Author Comment

    by:Oliver Wastell
    Hi Guys,

    Thanks for your help and clarifications.  I discovered that my 4 ODBC linked tables were using different connection strings:
    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:

    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.
    LVL 84
    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.
    LVL 3

    Author Comment

    by:Oliver Wastell
    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
        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?
    LVL 84

    Accepted Solution

    You can just use DSNLess connections instead of building a DSN:
    LVL 3

    Author Closing Comment

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now