Disappearing PKs

[nrgins]

Anyone ever have a problem with primary keys disappearing on their own? Every once in a while I look at one of my main tables, and I see that it has no primary key, and all relationships to the primary key are gone. I'm the only one who touches the tables, so it wouldn't have been someone else. This is just happening on its own, apparently. Has anyone ever experienced this?

I do a nightly compact and repair on the back end. And I know that a C&R actually rebuilds the file as a new file. So maybe something is happening there? This is very bizarre.

 

[theDBguy]

Are you doing the C&R over the network?

 

[Gasman]

Crossposted https://www.utteraccess.com/topics/2064616?post=unread

 

[nrgins]

Are you doing the C&R over the network?

Yes, the back end is on a network server. The file is set to compact on close, and it is opened with a command line argument that tells it to automatically close.

 

[The_Doc_Man]

First, welcome to the forum since that was your first post.

Second, you referred to a "back end." Is it a "pure" back end with nothing but tables?

Third, next time you do this, examine the file to verify that you still have PKs, then make a backup file, then and only then do the C&R.

Fourth, does any other program besides Access ever touch the file?

Fifth, are the PKs all auto-number or are some of them natural PKs?

Sixth, does the field go away or do you just lose the PK designation?

Seventh, if you lose the PK designation but keep the field, do you keep the index?

This is not the only time someone has reported such a thing. Our member Gina Whipp encountered something similar a few years ago in another forum.

Redirecting

Redirecting

EDIT: Your reply and my post came up at the same time almost so I didn't see your response right away. Auto-compact has been reported here as having problems. It might be better to C&R less often but to do it manually. In fact, given your description and the idea that you are the sole user, I would COPY the back-end to your local machine, do a local C&R, and then copy it back to the targeted folder.

 

[nrgins]

In fact, given your description and the idea that you are the sole user, I would COPY the back-end to your local machine, do a local C&R, and then copy it back to the targeted folder.

No, I'm not the sole user. When I said that I was the only one who touches the tables, I meant in the back end. No one else touches the back end. But there are about 10 users who use the database through a front end file, stored locally on their machines.

 

[The_Doc_Man]

OK, with that information I'm going to GUESS that there might be an issue with that auto-C&R with multiple users. My original advice was to not do the auto-C&R. Now I'll strengthen that suggestion. If you have even ONE user still in the DB when an auto-C&R is triggered, you have the potential of partial file locking that will cause many headaches. Since the index of a PK is probably hit more often than any other element of a table except the FieldDefs collection for that table, you have good odds that it might be busy, particularly if a table is open. In Windows, "busy" implies "locked" and that prevents clean index updates.

The way I approached the problem for a 40-user department was to have a scheduled time for DB maintenance during which I would make the back-end disappear from its normal location. That would cause the front-ends to fail since the connection is done by file path/name.

I made my backup copy in a folder dedicated to the purpose. If you use the Access "File" tab, you can do a Save As to a backup file that can be tagged with date/time info in its name. Performed the C&R in another designated folder. If the C&R worked and everything looked OK, I then moved the freshly-maintained DB back end back to where it was expected - which allowed the FE files to connect again.

It might be difficult to persuade folks to have the DB unusable during the work week but it makes for safer DBs.

 

[ebs17]

There is a method DBEngine.CompactDatabase.
This allows you to compress the backend specifically.

Before you do this, you should make sure that you have exclusive access to the backend, i.e. that there are no other accesses to this file.

 

[nrgins]

No, the C&R is done at 3 AM when everyone is gone. They only work during the day.
I also have code in the front end that triggers an auto-close if the back end has a certain value set (the front end checks for the value once per minute). So, before the C&R is done at 3 AM, I set the autoclose value, so that any front ends that are still open will close themselves. Then, after a few minutes go by, the C&R is performed.

Still, if a front end has a dialog box open, then the auto-close won't work. So you've given me a good idea. I'm going to add some code that checks for the back end .laccdb file after the autoclose sequence. And if the .laccdb file exists, then I'll cancel the C&R for that night. So that would be a good precautionary measure. Thanks!

 

[Pat Hartman]

No, the C&R is done at 3 AM when everyone is gone.

You have the BE set to compact on close. That means it compacts EVERY TIME IT CLOSES. NEVER set the BE to Compact on Close. It can open and close thousands of times each day. You'll wear out your hard drive with all that compacting

 

[nrgins]

No, that's not true. The Compact on Close only kicks in when the actual BE file is opened directly, not when it's opened in the background by the front end.

 

[Pat Hartman]

Thanks for that. Access has gotten smarter over the years