File vs Machine DSN

Posted on 2007-10-16
Last Modified: 2008-05-01

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?
Question by:Kelvin Sparks
    LVL 6

    Accepted Solution

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

    Expert Comment

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

    Author Comment

    by:Kelvin Sparks
    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.


    LVL 22

    Author Comment

    by:Kelvin Sparks
    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.



    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    733 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

    24 Experts available now in Live!

    Get 1:1 Help Now