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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I'll review the links you sent me as an additional work around and will let you know.
Thanks.
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 ...
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 ...
ASKER
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/co
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!.
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/co
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!.
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.
ASKER