Missing Records

frimy
frimy used Ask the Experts™
on
Hello All,

I up-sized a 2010 mdb Front-End to 2016 Accde but the Back-end is still mdb format.
it was working fine for four weeks.
Now my customer calls me that they are loosing Detail records from the Invoice table.
i checked and checked for any possibilities that we might be changing that can cause to delete records.
But i don't see anything, i also checked the Advanced Options it's the same as the 2010, Record level-locking etc.
Does anybody have an idea what it could be because it's a big problem?
Thanks in advance
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

Commented:
Are they seeing gaps in the records when they look at query results?  Or when they look directly at the data in table view?
Did the now-missing records exist prior to the front end conversion?  Or are they records that were created after the conversion?

Author

Commented:
1)  in the table view I see missing ID's (auto numbers), in a invoice with ten Detail recs can be missing like four recs (not consecutive)
2)  the recs are before the conversation, there were there two weeks ago.
Paul Cook-GilesSenior Application Developer

Commented:
Well, that blows the theory that a query was somehow not picking up records that it should have grabbed.  :(

If you have a pre-conversion backup of the data, you should be able to restore the missing records (by running an append query that selects IDs from the backup table that are not in (select IDs from the current table) ;  if not, you're probably hosed.  


A few more thoughts:
Don't let your users see the table view;  lock the db so that they can't get at the data directly;  require that they use forms and buttons to manipulate the data.
Consider setting the forms that display the records (invoices and details both) to not allow deletions.  If a record needs to be excluded from future consideration, give it ObsoleteDt and ObsoleteBy field, create a button that populates both those fields, and then set your forms, queries, and reports criteria to exclude any record with a populated ObsoleteDt.

Anyone else have suggestions or useful thoughts?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
my users don't have access to the tables only with forms with Header and sub form for the Detail.
Paul Cook-GilesSenior Application Developer

Commented:
That's good.  I definitely recommend setting the subform to not allow deletion of records, and creating a button to exclude (which will look to the user like deleting) records.  :)
David BernsteinChief Developer/ Microsoft Access

Commented:
Database corruption is always a possibility.
Sean SmithSQL DBA

Commented:
I agree with David, there might be database corruption. Try to run Compact and repair.
Paul Cook-GilesSenior Application Developer

Commented:
frimy, where are you with the resolution of this problem?
Commented:
So far I didn't found any solution.
but I think the problem was,
The customer just told me that many times he is opening the Invoice Details from my Continuous Form and then he right clicks to see in data sheet view for more options.
On the form I locked the controls but I wasn't thinking about the DS view.
I'm almost sure since he was playing around with the data, he was probably deleting some records and didn't realize it has an effect on the data.
He thought it's like Excel view (because I have an option to export the data to Excel.
Thanks you all

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial