Microsoft Access 2010 application relinks to sql dsn ODBC every time there is an update in the app

Posted on 2012-08-20
Last Modified: 2012-09-17
Hi all,
We have a microsoft access application, front end is on each local pc, we connect to the sql db with dns odbc. We were using access 2003 (both full and runtime for some pc's), all was working fine.
We upgraded the application, all works fine, until we start upgrading to office X on local pc.
The problem now is that every time there is an update for the application, we push the mdb to each local pc, when this happens, the first time user opens the application, it takes longer than usual like if it was relinking tables, finally it opens and works fine until there is another update of the application and same thing happens.

I do the updates on the mdb using access full version, so I don't have this problem, I assume that it's because I do the modifications on my own pc so I keep the 'same' file just make changes, I wonder if rewriting the file on the other pc is causing a kind of problem with the odbc trying to identify the file as the same one???

I have tried on pc running win 7 and xp and same problem, it doesn't make any difference if I use full access or the runtime version.

I have tried with users being administrator, providing full access to the folder containing the application and error persists.

When this happens, the size of the mdb file grows a few K's.

Has any body experienced a similar issue?
there is no problem at all if using access 2003, and the problem is not linked to the version of windows.

Any ideas??
Thanks in advance.
Question by:mmpopins

    Author Comment

    sorry, I mean to say dsn not dns!!
    LVL 9

    Accepted Solution

    not sure but it might happen your db currupted and having major entries of error files due to which its taking time to open.

    you can use "compact Repair" functionality of ms access which might helps you to resolve your problem.

    1.Instruct other users to close the database. You must be the only user with the database open in order to run the tool.
    2.Click the Microsoft Office button.
    3.From the Office menu, choose "Manage", then "Compact and Repair Database"
    4.Access will present the "Database to Compact From" dialog box. Navigate to the database you wish to compact and repair and then click the Compact button.
    5.Provide a new name for the compacted database in the "Compact Database Into" dialog box, then click the Save button.
    6.After verifying that the compacted database works properly, delete the original database and rename the compacted database with the original database's name. (This step is optional.)

    1.Remember that compact and repair creates a new database file. Therefore, any NTFS file permissions you applied to the original database will not apply to the compacted database. It's best to use user-level security instead of NTFS permissions for this reason.

    or you may also refer below link...

    Author Comment

    Thanks for your reply Keyu, every time I made changes to the 'original' on my computer, I do the compact and repair, then push it out to local pc, and even if I compact and repair on one of those pc's, next time there is an update, this happens again.

    I'll review the links  you sent me as an additional work around and will let you know.

    LVL 84
    Have you tried moving everything to a new, blank database? Anytime you run into issues like this it's a good idea.

    You might also try to Decompile your database. To do that, build a shortcut with this as the Target:

    "Full path to msaccess.exe" "full path to you database" /decompile

    "Run" this shortcut, and then do this:

    1. Compact the database
    2. Compile the database - from the VBA Editor click debug-compile. Fix any errors, and continue with the debug until the menuitem is disabled.
    3. Compact the db again.

    Please be SURE to make a backup before doing any of this.


    Are the target machines fully up to date re: Office and Windows? Be sure of this.

    Are you sure the references needed by the program are available on the machines? This doesn't really sound like a reference issue, but it's worth checking out.

    Finally: Sometimes this is caused by an errant linked table - are ALL of the tables linked to the same datasource, and are you SURE that all those tables exist? If not, Access can spend some time searching for the resource.

    And if you're using mapped drives for links, you might find that the relink can take a loooong time. Better to relink with UNC paths ...

    Author Comment

    Hi all,
    I tried all suggestions but still same problem, but I believe I figured it out what is causing the issue.
    Althought some users are using runtime xp and others runtime X, the changes/decompile/debul/compile/repair  are done with access xp, then I  and push this one file to all users, (one same mdb for both xp and X), so access full/runtime X doesn't like the compile/repair from xp.

    Shouldn't a newer  versions recognize files from an older without problems?

    I assumed that access X would 'read' these changes without problem, but no, If I do the compile/repair with X and copy this file to computers using runtime X, it works perfectly, so I will need to have 2 versions of the mdb (one for access xp and one for acces X) while we move all users to X.

    Thanks everybody for your comments and suggestions!.
    LVL 84
    It's often best to have version-specific fiies, but I've successfully deployed the same version to multiple runtime versions. You must be very careful to build the final file in the lowest version (XP in your case) before you deploy, but other than that you should be okay.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    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.

    737 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

    23 Experts available now in Live!

    Get 1:1 Help Now