Navigation skips records ?

[ahmed_optom]

Hello,

I think I have another database corruption problem.

When I use the navigation buttons at the bottom to skip forward or backwards through the records on my form, it skips certain ones. I can see them in the backend and the information all appears there and intact, however it refuses to show the record on the form.

eg, if im on record 500 and I go forward one it will go to record 502 and miss 501, even when 501 exists and I can see it in the backend.

Any one have any ideas on how to fix this?

 

[CJ_London]

Any one have any ideas on how to fix this?

first, determine why the problem happens, then you can fix it

perhaps it is something to do with your sort order (or lack of) or filter/criteria

 

[The_Doc_Man]

A couple of questions to help narrow this down:

1. This is on a form. What is the form's RecordSource? (query or table)

2. Does the form have any type of filtration or ordering enabled?

3. Does it always skip the same records or is the skip apparently random?

 

[ahmed_optom]

CJ, I have no idea why it skips these records. When I look at the data in the backend, it's there.

1. Query
2. No filters
3. Always skips the same records.

I have found this database is corrupted, it's not syncing with the other front ends. Eg if one user makes a new record, it's saved on the backend, and that particular user can see the record, but no one else.

I used a recently backed-up back-end and the problem persisted. It only goes away if every user uses the backed up front and backend. However, I would like to know how to avoid such issues in the future.

 

[CJ_London]

I have no idea why it skips these records

that is what we are trying to find out. Before assuming you have a corruption, first check out other possibilities

It only goes away if every user uses the backed up front

please clarify - if all your users are using the same frontend, then almost certainly you have corruption - each user should have their own copy of the front end on their local machine

If each user does have their own front end then

what is the recordcount on the form v the query?

you haven't commented about sorting - it can be retained(as can filters) by the form if the form has been saved - and have you tried sorting on the id field?

 

[Mark_]

If you view the query the report runs on, can you see all records?

Have you deleted the locking file to make sure someone doesn't have records locked?

 

[Pat Hartman]

If you hover over the linked table name, you will see the name of the database that table is linked to. Or, better yet, open MSysObjects and check all the links. Perhaps one table is linked to the wrong BE or perhaps all of them are.

Corruption in a BE is not unheard of. But it is very rare.

 

[ahmed_optom]

I have partly identified the issue.

Sometimes the network drives would not reconnect automatically, this would mean the FE would not be updated on some users computers. I have found that different age FE do not seem to work together.

When I removed all FE, and put same fe on all, they were then in sync and each user could see all the records. However I have still lost some data, but i can re-add. I am unsure why access has removed some records in this situation.

 

[The_Doc_Man]

Sometimes the network drives would not reconnect automatically, this would mean the FE would not be updated on some users computers. I have found that different age FE do not seem to work together.

Could you clarify that? When you say "the FE would not be updated" - are you talking about some type of auto-update script that brings the latest copy of a single, common FE down for local use? Or did you mean something different than that? I think you meant simple differences in maintenance versions but I want to be sure.

How different are the different FEs that don't work together? I.e. are they accidentally different or intentionally different? Simple generational differences after maintenance and modifications of some functions? Or do the differing FEs contain some locally persistent data?

Normally, if an FE fails, but then the network is restored, it is enough to close the FE completely, do a Compact & Repair (which is an FE-only action), and then re-open it. If an FE stops working after a network dropout, it should not do anything damaging to itself. So what is going in the FE?

Also, at least in most cases, if the network is down, your ability to do much of anything with the FE by itself is limited. Not zero, but limited. The most that I ever made my split DB do was set up a protected function to test whether it could read my table of authorized users. If not, rather than crashing the app, the function issued a Modal Message Box that said "Goodbye, we don't have what we need right now." Then it did an Application.Quit and I very often heard about this RIGHT AWAY. Often in a chorus from all directions.

 

[ahmed_optom]

Could you clarify that? When you say "the FE would not be updated" - are you talking about some type of auto-update script that brings the latest copy of a single, common FE down for local use? Or did you mean something different than that? I think you meant simple differences in maintenance versions but I want to be sure.

How different are the different FEs that don't work together? I.e. are they accidentally different or intentionally different? Simple generational differences after maintenance and modifications of some functions? Or do the differing FEs contain some locally persistent data?

Normally, if an FE fails, but then the network is restored, it is enough to close the FE completely, do a Compact & Repair (which is an FE-only action), and then re-open it. If an FE stops working after a network dropout, it should not do anything damaging to itself. So what is going in the FE?

Also, at least in most cases, if the network is down, your ability to do much of anything with the FE by itself is limited. Not zero, but limited. The most that I ever made my split DB do was set up a protected function to test whether it could read my table of authorized users. If not, rather than crashing the app, the function issued a Modal Message Box that said "Goodbye, we don't have what we need right now." Then it did an Application.Quit and I very often heard about this RIGHT AWAY. Often in a chorus from all directions.

Yes, for some reason if we use different versions of an FE, the autonumber gets out of sync and then it all goes to hell.

Haha, i think your modal message when the database loses connection may create me more agro than the corruption potentially does.

 

[The_Doc_Man]

Creates more aggravation for your users, but saves you some. Because then you know immediately when something is wrong. It also makes you look like a hero since you can then respond more quickly.

I have a problem with your statement "the autonumber gets out of sync" with different FEs. THIS CANNOT HAPPEN in a properly split DB, because there should be nothing with which to sync. The BE should be a single file that holds ALL of the shared table definitions for all things that have - or could have - autonumbers. The current value for the next autonumber will be in the TableDef which, for a shared table, MUST be in the BE. The BE TableDef will identify the table as "local" (to the BE) file but the same table's TableDef in the FE will identify the table as "linked" (i.e. not actually in the FE). If for some reason the FE's TableDef isn't set up correctly, then you have multiple holders of the "next" autonumber and all sorts of mayhem will follow.

Therefore I have to assume your problem is based on an "improper" split. I have no knowledge of your level of Access experience so have to make what might be taken as unneeded suggestions. If you already have done what I suggest, ignore it and take it as a misguided attempt to be helpful.

You need to use this forum's Search function, which is in the thin blue ribbon just under the things at the top of the page that identify your current topic and your user identity. The Search option is 3rd from the right on that ribbon. Please look up topics on "Splitting a database" and "sharing a database" because it sounds to me like your corruption issues originate from an incorrect split architecture. NONE of the things you mention should ever happen in a properly split FE/BE pairing.

 

[Mark_]

eg, if im on record 500 and I go forward one it will go to record 502 and miss 501, even when 501 exists and I can see it in the backend.

In your table, is record 501 empty? If so, do you have any code that fills in values for your records PRIOR to the form normally saving?

If you have the form put in default values that are being saved, THEN the user looses their network connection, you will find partial records in your database. This should only happen if you are doing something that triggers the form to save prior to the user completing all data entry.

Does this describe what is happening?

 

[CJ_London]

one other thing to throw in the mix. Are your users using wired connections to the network or wireless? If the latter, that can lead to all sorts of issues

 

[ahmed_optom]

In your table, is record 501 empty? If so, do you have any code that fills in values for your records PRIOR to the form normally saving?

If you have the form put in default values that are being saved, THEN the user looses their network connection, you will find partial records in your database. This should only happen if you are doing something that triggers the form to save prior to the user completing all data entry.

Does this describe what is happening?

in the table some of the records that are skipped are present, some are missing completely.

I do have some "default" values that add on load, such as date, user who is opening the form etc.

However, I still dont understand why I cant view these records via the record navigation.

Also I found 2 forms were corrupt, they were giving memory errors, when replaced with an identical, but older backup, they work fine. One report also is corrupt, says it was saved in an invalid format and can not be read, Im in the process of getting an older backup and copying that report over as it hasnt changed in months.

I have about 10-20 users using simultaneously, mostly via wireless.

My experience of access so far is great UI, easy to use, but I seem to be fixing serious errors too frequently, I seem to get serious faults about once a month. If I moved the backend to sql would it be more reliable?

 

[Mark_]

However, I still dont understand why I cant view these records via the record navigation.

If they are in your table, check the record source for your form to see if they are being excluded. If you run your form off of a query or if the form is filtered, that could cause you to not see records that you expect as they don't meet the criteria.

I would check with who ever is in charge of your wireless network to see if they can find troubleshoot your network. It sounds like your network is not stable, thus causing corruption.

 

[Pat Hartman]

mostly via wireless

Jet/ACE are quite unstable on a wireless connection. If you are not using a wired network, you really need to switch to SQL Server ASAP. You will still have problems with the FE becoming disconnected but you are much less likely to have data issues.

However, I still dont understand why I cant view these records via the record navigation.

Is there criteria that is preventing this?

Be very careful when dirtying a record. You do NOT want to dirty a record before the user does. That means that any code you have that dirties the record needs to go into either the BeforeInsert event (if you only want to do it for new records) or in the on Dirty event (if you want to do it for all records). Code in the form's BeforeUpdate event does not dirty the record since it is already in the save process so this is where you put code that logs changedBy and ChangedDate.

 

[The_Doc_Man]

I do have some "default" values that add on load, such as date, user who is opening the form etc.

However, I still dont understand why I cant view these records via the record navigation.

When you have default values to load to a new record, the new record INSTANTLY becomes "dirty" and has to be saved. But if you don't correctly back out of that record AND do something to not save it either, you have an allocated record in an unsaved state - which is somewhat ambiguous. I don't know offhand why the table view would show a record and the form's form view wouldn't - but we would need to see the query that is driving the form - i.e. what the recordsource either contains or points to.

 

[Pat Hartman]

Doc, defaults don't dirty a record. User code is what dirties the record. That's why it is so important to place the code in the correct event. If you don't update the record until the BeforeInsert or Dirty events, then the user has already dirtied the record. You are just adding additional data. All the normal validation applies. The user won't be confused because HE knows that HE typed something into some control so he will understand why he is being asked to complete the record or discard it. You would NEVER have to do auto cleanup if YOU don't dirty the record first.

 

[The_Doc_Man]

OK Pat, but I've seen oddities involving defaults. Does your comment count in the case where the record is autonumbered AND has non-zero / non-blank defaults?

 

[Mark_]

IIUC, you also dirty the parent if you add child records prior to completing your parent entry.