Split MS Access DB Needs Compact/Repair as well as Re Link on Front and Backend, Why?

Question

I have an ACCDB that I split a while ago that contains many forms with sub forms (based on tables) and over two hundred tables in the BE (almost all are small lookup tables for vehicle objects) and 400+ queries. There also happens to exist another ACCDB with a single table in it with 6.5M rows that the FE links to with basic history info. The two backends do not link to each other in any way. The FE is 14MB, BE is 1.2G and the single table DB is 900MB, all with primary keyes and indexes setup appropriately. The DB is 100% normalized. Both BE's grow 5% every month. The DB is currently slated to be migrated to an Oracle 11G environment later this year.

Question: I found out recently that if I compact and repair the back end or front end that none of the forms containing subforms open; the whole FE just freezes to white. Even if all 3 are repaired I still have issues. BUT if I compact/repair all 3 as well as relink the entire front end to the two backends the forms all of sudden start working. It was only recently that this behavior began.

Why do I have to relink to make the forms work again?

Answer 1

You should not have to re-link anything here at all after a C+R.

The only thing that comes to mind is the user who is doing the C+R has some restricted rights in the folder or directory where the C+R occurs.

Remember, when the user does the C+R, then a COPY of the file is created – and thus possible inheriting of the CURRENT user’s rights can occur WHEN the NEW file is created. So it sounds like some permissions issues exists on the folder, or the user that is doing the C+R has some special (different) rights. (perhaps some inherited rights do to membership in some security group).

Of course one should ensure that you are using UNC path names, and of course the front end needs to be placed on each machine.

Perhaps again the user doing the C+R has “different” drive mappings and thus links to the back end databases are thus wrong due to different drive letter. So if not already, as a general rule I would STRONGLY avoid drive letters and use NC path names (if you not already).

If you are using UNC path names, then the likely issue is permissions.

There also a possibility that the new user doing the C+R is running the front end from a “non” trusted location.

Also, the table of 6.5 million rows seems a bit large, and I assume the 1.2 gig size is RIGHT AFTER a C+R? (but this issue is for another post).

This suggests a drive mapping issue, a permissions issue, or perhaps the user launching the application is messing up references. I would shift by-pass into the application and ensure that the user doing the C+R can compile the application, and would from VBA editor take CAREFUL note that say office 14 references are not being hi-jacked to office 15 references for example.

Answer 2

You're reaching the "hassle-free" viable (as opposed to "documented") limits of Access as a database. remember the queries need to be compiled which means resolving all the table links, and verifying existing indexes and other meta-data. it's possible that simply over-writing this information by manually using the linked table manager as you have, may be more efficient.

Here's a few prescribed tips which might help you out: http://office.microsoft.com/en-gb/access-help/improve-performance-of-an-access-database-HP005187453.aspx

And some more... http://www.fmsinc.com/MicrosoftAccess/Performance.html#Linked%20Tables

And a related thread from this site: Proper way to program a Microsoft Access Backend Database in a Multiuser Environment

Issues which may not be helping you:

• queries which don't restrict the dataset sufficiently, particularly those running a dynaset
• backed database files sitting too low in the windows folder structure (the higher the better)

As the 2nd link suggests, the truth is there are so many variables at work that resolving this will require some tinkering, with trial & error playing a major part.

All that, or you can upsize to SQL Server Express :) http://office.microsoft.com/en-gb/access-help/move-access-data-to-a-sql-server-database-by-using-the-upsizing-wizard-HA010275537.aspx