0

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?

Fattire
  • 1
  • 2

2 Answers2

2

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.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • Albert, Ty for your input. The mapping was done via UNC's...so far so good. Permissions are good as well as I have admin rights to everything. I have not looked at my references lately but the libraries I typically use are newer. I will look for better references and implement should those exist. The 6.5M row is the 900MB, its actually really fast. The 1.2G rarely goes above 1.5G, 1.2G is after the C+R but rarely goes up or down, but the DB is growing at about 5% every month Like I answered below I will update once I find out the answer. – Fattire Sep 23 '14 at 23:06
1

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

Community
  • 1
  • 1
Tim
  • 756
  • 1
  • 7
  • 12
  • but none of your post address or EVEN talks about why a C+R would stop the application from working? And WHY AFTER a C+R is a re-link required? The poster states the application RUNS JUST FINE AFTER a re-link. So while your links are all great for fun and games, and perhaps point to some good practices, nothing address the issue and question at hand. – Albert D. Kallal Sep 23 '14 at 20:39
  • 1
    Thanks guys, this is awesome information - while the answer is going to be hidden deep in the foundation of Access I think that the best thing to do is get this thing into a much bigger platform like SQL Serv as you suggest or Oracle (which is what we use here). I appreciate all the help! When I find out something from the links provided I'll be sure to update this post! – Fattire Sep 23 '14 at 22:45
  • @AlbertD.Kallal - actually I did offer a suggestion as to the cause of the behaviour early in my reply, but (as demonstrated by your suppositions which proved to be irrelevant) at this stage it is really a guessing game unless you can actually try some known techniques out, none of which can be implemented with a few clicks - which is where the links come in. thanks for your constructive comments however :) – Tim Sep 24 '14 at 03:13