?
Solved

Simple ODBC Connection Problem (MS Access / Windows 7)

Posted on 2012-08-14
8
Medium Priority
?
5,328 Views
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:
"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
0
Comment
Question by:Oliver Wastell
8 Comments
 
LVL 15

Assisted Solution

by:eemit
eemit earned 400 total points
ID: 38294286
>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
0
 
LVL 8

Assisted Solution

by:ee_reach
ee_reach earned 600 total points
ID: 38294398
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
0
 
LVL 85
ID: 38295512
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Author Comment

by:Oliver Wastell
ID: 38296453
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.
0
 
LVL 85
ID: 38299783
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.
0
 
LVL 3

Author Comment

by:Oliver Wastell
ID: 38300218
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?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 38302168
You can just use DSNLess connections instead of building a DSN:

http://www.accessmvp.com/djsteele/DSNLessLinks.html
0
 
LVL 3

Author Closing Comment

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…
Suggested Courses

839 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