Lost relationships & primary keys

[hartster]

I'm having lots of erratic problems with our Access database. Back end tables on the server, front ends on workstations. Running Windows 2000 server, Access 2003 SP2 on Windows XP SP2. I'm not the network administrator so don't have much detail on network.

For instance, the back end is 'losing' relationships between tables, with the primary key on the 'one' end of a 'many' relationship disappearing - the relationship link between the tables also disappears.

Other errors are:

- erratic problems with indexes which are sometimes fixed with a compact / repair, sometimes not
- data on forms disappearing
- data on forms appearing in the wrong field
- queries that ask for parameter input twice when only programmed to so once
- sorting orders suddenly going wrong way round
- inability delete a record via a form, caused Access to completely crash with Microsofts 'sorry unrecoverable error' message.

To fix the query problem, I created a blank query and imported the old one into it, problem solved. But what caused the old query to act weird in the first place?
To fix the inability to delete a record problem, I created a whole new blank database and imported everything into it. But what caused the database to repeatedly crash?
The suddenly missing relationships I have no cure for.

I'm at the end of my tether - can't figure out any pattern to this.

Any suggestions? Network errors? Read / write on server? Disk space / fragmentation? Fundamental problem with the database?

Any help much appreciated.

Sorry if this post should have been in the tables forum.

 

[Dennisk]

sound like you have a corrupted FE and BE.
Create two blank dbs and import the Fe objects into one and the tables into another, then check PKs and relational Integrity and test again.

 

[hartster]

Thanks for that - any idea what might be causing the corruption? This is the key because I need to stop it happening again.

 

[Dennisk]

What causes the problem, In my experience its a combination of network errors, and the seemingly strange case of Microshafts OS unable to run Microshafts own software securely.

Or this is what you get with MS software and networks a never ending series of database corruptions. Roll on next month when I switch my home PC to a MAC and 3 years time when I retire and never have to open a MS Access db again.

 

[rpadams]

I had a similar problem in an ordering database. The user first put in company info (name, address etc.) and I generated a unique primary key with autonumbers for this order. This was then related (one to many) to an order details table with the items to be ordered. Each item had a transaction number that matched the primary key.

What frequently happened is that the network didn't properly record the primary key (even though it had been generated and made visible to the user). I suspect it was "buffered" someplace but never got permanently saved. The net result was that the order details became "orphans" and since relational integrity had been set, Access dropped the relationships and the primary key designation.

Given the sloppy network, my only recourse was to close the order file after it was created and then have the user re-open it before adding the order details. This way I knew the initial info had been truly saved. If it somehow got lost, the user starts again but it doesn't mess up the data. It slowed down the use of the program, but seems to have cured my problem.

 

[Dennisk]

I've had to remove dbs from networks and place them on HDs for a couple of weeks whilst the network gets over a 'bad patch' before now.

 

[The_Doc_Man]

There are a literal myriad of possibilities with MicroDumb s/w, but a common cause of corruption in Access has to do with users' sloppy log-off habits.

If a user exits the DB cleanly, you will have fewer problems. If your users TRY to exit but it takes too long, so they get frustrated and turn off the computer anyway, you have BIG problems. If your network is not stable so the link is like a yo-yo (up and down constantly), you have SERIOUS BIG TIME corruption problems.

See, the mechanism for this is because Access treats remote MDB files as FILE SERVER operations, not SQL SERVER operations. So the updated buffers for what you want to do are on YOUR workstation - not the server. If you start making changes, you can have some things "deferred" - that is, committed to memory but not yet sent over the network. If your network dies or your user does an "END NOW" (to "Program not responding") over your slow network, that update doesn't get fully written back to the remote MDB file. Particularly if the affected area WOULD have released some records due to a DELETE query, but never got around to it, your pointers are now messed up. I love Access (in the sense of a full-blown love/hate relationship, I assure you) but there are times when I have to recommend another way of thinking.

If your network is highly unstable and your network gurus cannot fix that problem in your lifetime (or it merely seems that way), you seriously need to consider something that makes transactions "atomic" with respect of write-back operations. This happens when you do FE/BE where the BE is something like SQL Server or ORACLE. This is not because Access cannot do a good job on your database - but because of Windows reactions to bad networks, it cannot do a good job on your SPLIT database.