• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • Last Modified:

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

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.
0
mmpopins
Asked:
mmpopins
  • 3
  • 2
1 Solution
 
mmpopinsAuthor Commented:
sorry, I mean to say dsn not dns!!
0
 
keyuCommented:
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.)

Tips:
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...

http://office.microsoft.com/en-us/access-help/help-prevent-and-correct-database-file-problems-by-using-compact-and-repair-HA010235583.aspx

http://databases.about.com/od/tutorials/ht/compact_repair.htm
0
 
mmpopinsAuthor Commented:
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.

Thanks.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Also:

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 ...
0
 
mmpopinsAuthor Commented:
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!.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now