File vs Machine DSN


I am migrating (upsizing) an Access 2002 Access database to Access 2007. Data is in SQL Server 2000. Has a lot of precise numeric data (16 decimal places). In the old version I used a file dsn for the ODBC link - worked great - no issues having dsns configured onto PCs etc....

When I migrated to Access 2007 (staying with SQL 2000) I created a new file dsn and used the convert functionality to create an accdb file. Numeric data on showed to 2 decimal places. I could format the field to 16, but would show as rounded to 2 deimal places with 14 further zeros!

I have created a machine dsn, and data links happily. I've checked the dsn settings and they are exactly the same. Can anyone explain and/or provide a way to make file dsn work?
LVL 23
Kelvin SparksAsked:
Who is Participating?
sweekes283Connect With a Mentor Commented:
If you compare the settings in the registry here:

To the File DSN here:
C:\Program Files\Common Files\ODBC\Data Sources

You may be able to locate the problem.  You'll need to open the DSN in notepad.  The only other item to look at is the connection strings in Access.  If everything matches up, then I'm not sure where to go from there.
If you'd like, export and post the Registry Branch for that DSN, as well as the text in the .DSN file and I'll see if I can find the discrepancies.

I should ask though - why do you need a File DSN, if the Machine DSN works fine?

If it's just a matter of loading that DSN onto other PC's - I believe you would only need to import that registry data onto the new PCs, and you could even have Access do that for you, if you were feeling adventurous.  
Kelvin SparksAuthor Commented:
Have to check when I'm next at that PC. Have historically used a file dsn - was only a chance thought to try a machine dsn to solve the issue.

Will get back to you in a few hours.


Kelvin SparksAuthor Commented:
Had a look at these two. Absolutely nothing there other than one says driver = SQL Server and the other the path to the SQLSERV32.dll. Both have exactly the same server name, database name, trusted connection. No settings there that would be of use at all.

It's not important which way I go, - more so if I could find out why in terms of building my own knowledgebase. To a degree you did that by showing me the registry side of the machine DSN. Happy to reward for your effort.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.