Solved Automated search for Database Corruption Assistance

[Gasman]

I searched a bit and I'm not seeing a Simple way to prevent access to the tables and queries directly and still allow access to the navigation pane.

i.e. we have 7 main forms that users typically switch between on the navigation pane. There are 14 more forms that are hidden by default. I personally have "Show Hidden Forms" enabled, but I don't think any other users know how to do that (and it would just confuse them if they did). Also, they could select tables or queries from the drop-down on the navigation pane, but I don't think they know how or have any reason to do that.

From a search, the only way that I've seen to keep users from access tables and queries would be to hide the navigation pane, disable F11 to show it, and disable the shift key to bypass the code that hides it and show it that way.

That would be workable, but then I would need to make something like a pseudo-navigation pane - i.e. instead of having Form1 open from startup and the navigation pane open to select Form2, Form3, Form4, Form5, etc, I would have to have frmMain open from startup and frmMain would have buttons to show Form1, Form2, etc.

That seems a bit extreme to me, but ... Is there a way to do this so that it WORKS like the navigation pane - i.e frmMain would show in the left 1/5 or so of the Access window, clicking the Form 1 button would open Form1 to the right of it?

I got by with a standard switchboard form. Pat Hartman has an enhanced version which she has posted here several times.
If you can group your forms/reports etc logically it is more than adequate.
For more than the switchboard could offer, I would supply a combo, which would open form/report with possibly a form just to get any parameters. Supplied FE was also an accde.

 

[Marshall Brooks]

How do you prevent users switching to DS view? (Found it - Property Sheet - Allow Datasheet View). (I have one user who will not be happy, but if it prevents the data from being corrupted.)

I have some of what you describe (Reply #47) in place.

They aren't prompted every time. We have confirm and undo buttons that are grayed out. Whenever they make a change, the buttons are enabled. If they try to go to a new record after making a change, they get a "Do you want to save?" prompt. Before we did this, they could type gibberish, overwrite a field, and Access would save the change without them realizing they messed up anything.

Valid point that it isn't checking whether any of the data is ACCURATE, it's just confirming that they did mean to make the change.

For dates, if they click a date field, a datepicker pops up and they enter they typically select the date from that. So dates are actual dates. Now again, they could say that a project is due to complete 100 years ago (although it would take a while to get there in the datepicker.) (Although they COULD click Cancel on the datepicker and then enter 7/3/203 - but that takes extra effort.)

Devil's advocate question: If editing the tables means changing the table structure, and I'm not concerned about them directly changing the table data... And (as I understand), they can't change the structure of the linked tables without exclusive access to the backend, and if they change the structure of a local table that is copied from the backend, it either is erased or crashes the front end when they re-open it ... - Then why am I worried about preventing them from getting to the tables?

I'll look at the switchboard examples - I somewhat like the extra screen real estate with the navigation pane hidden.

Typically, I only allow one form open at a time. I have code in the form load event that closes any other open forms when you select a new form. I also sometimes want to have more than one form open, so I have a macro that allows that.

Its always the small minority who cause the most trouble ... usually those who think they know what they are doing

In this case, the user that I said will be upset if I disable Datasheet view. (We have an inside joke that he broke the database again, but it might not have been that much of a joke ...)

If you really want to go 'the whole hog' you can also hide the entire application interface.

I don't want to go "whole hog". Partly as follows: I have macros that I run sometimes: Database Tools-Run Macro. Most of our users don't know that command is there, but I don't want to put them on the switchboard, and I don't want to hide them and then I have to run the .accdb instead of the .accde if I want to use them.

 

[Marshall Brooks]

Okay - the light somewhat turned on.

I don't think any of the users (except sometimes me) are directly changing the data in the tables. They are using datasheet view sometimes.

The real issue isn't to force them to use forms, but to add some logic to the code on the forms (which I think also works for datasheet view, but I personally almost never use that, so I'm not sure).

The way I have it set up, they can copy and paste gibberish and corrupt the table from the user form or from datasheet view or from the tables, so hiding tables and datasheets from them isn't really going to help anything.

OTOH - I don't think the backend has ever been exported to text and re-imported in over 20 years, so that might help.

 

[Marshall Brooks]

I'll look at Reply #55 later. I think (and hope) I'm the only one that knows how to delete records. We have a copy record button - it doesn't use an append query, it creates a new record and then copies SOME of the fields from the previous record (and prompts to change other fields).

We don't copy multiple records at once. (At least I don't. The user that likes DS view ended up with three exact duplicates of one of his records that I couldn't delete without doing a C&R on the backend, but he probably would have figured out how to do that in form view also.)

Test code is not working.

I only tried one table. Let's say my table is called Names.

I copied the structure for Names from the backup file to a new table named Names in the blank new file.

ExportData worked. I used a different destination directory and I had to create that directory, but I ended up with a Names.csv file in the destination folder.

When I ran ImportData with this code:

DoCmd.TransferText acLinkDelim, , "LinkNames", strPath & "Names" & ".csv", True
strSQL = "INSERT INTO LinkNames  SELECT LinkNames.*  FROM Names;"
db.Execute strSQL

I got an error on line three, something like "Syntax error in SQL Insert statement".

And when I looked at my database, Names was still empty, but I have a new linked table (different icon) named LinkNames and the data is there. (And the PK still has a 9-million number for the 4 records re-added yesterday - not sure if that is a problem.)

And if I run it again, I get a new linked table LinkNames1, LinkNames2, etc.

So ...

 

[Marshall Brooks]

I might have mistyped my reply:
strSQL = "INSERT INTO LinkNames SELECT LinkNames.* FROM Names;"
should be:
strSQL = "INSERT INTO Names SELECT LinkNames.* FROM LinkNames;"

But that also gives me a syntax error and creates a linked table named LinkNames3.

I'm missing something somewhere (probably something obvious ...)

I didn't use the import function. I created a new table design and then I went into design mode in the old file and copies all of the field rows and pasted them into the new table, set the primary key field as primary key, and saved it with the same name as it had in the old file. Hopefully that was what I was supposed to do.

The new table doesn't have any data, but the linked tables that are being created do have data.

 

[Marshall Brooks]

Still not working and still giving me a syntax error on the SQL INSERT INTO line.

I tried stepping through the code and I see where it creates the linkNames table.

Also I tried:
strSQL = "INSERT INTO Names SELECT * FROM LinkNames;"
and that failed also.

I'm not sure where the error is b/c it looks similar to code I have that clears the data from a local table and then copies the data from the same table in the backend to the local table - that code looks like this:

strSQLinsert = "INSERT INTO table1 SELECT * FROM table1 IN '<backend path and file name>';"
CurrentDb.Execute strSQLinsert, dbFailOnError

 

[Marshall Brooks]

Manually copied a record from LinkNames into Names and that worked, so the structure is okay - it just isn't liking the SQL statement for some reason.

Also - I think I will need to delete all the linked tables at the end of the procedure, but I can do that manually.

 

[The_Doc_Man]

I have primary keys. Don't have unique indexes, but I think that is what a PK does, and don't have relationships, but so far haven't needed them.

By its nature, a PK will have a unique index. The "don't have relationships... haven't needed them" is hard for us to tell, but if there is any data with a parent/child relationship in real life, the corresponding relationship if expressed with your databases would provide some part of validation.

 

[Marshall Brooks]

Well - I found the problem. I don't have a table named Names, but I didn't want to post the actual table names. I do have a table named Workspans and I tested it with that and it worked fine. 8 of my tables have dashes (-) in the name and two of them have underscores (_). (And I didn't see it originally, but one of the tables has a space in the name). I guess those count as special characters. Is there an "easy" workaround for that? (I know if needed, I can rename the tables to remove dashes in the old file, create tables without dashes in the new file, run the procedure and then re-name the tables in the new file to add the dashes back in, but ...

Or probably easier, in the new FE, I can open the table tblTable-Data and the linked table LinktblTable-Data and manually select all the records and move them. (I'm assuming this would be okay since the data was exported to text and imported.)

Sorry for the miscommunication, but the actual table names would reveal a bit of who and what programs I work for/on and I don't want to disclose that publicly in a public forum. (At least not more than I have in other forum posts.)

I tend to delete them before running a procedure rather than after running the procedure. You are less likely to break saved querydefs that way.

I think you mis-understood my comment. The way the functions are structured, the ImportData TransferText line creates a linked table in the new backend. The SQL INSERT then copies that data to the newly created table. But nothing deletes the linked table after the procedure finishes. I'd either need to do that manually (not a big deal), or add lines of code to do it (not a big deal either).

The "don't have relationships... haven't needed them" is hard for us to tell

To clarify - there are no parent-child (or other relationships) in the tables - there are some, but not many, in some of the queries in the front end. I could probably combine 7 of the tables into one and use queries to filter the data on the forms into the seven forms we use now. I have read a bit on normalization and this database does NOT have it. (But it works for us (when it doesn't crash) and at this point, restructuring and re-designing all of the tables and then correcting all of the form fields and VBA code that reference tables that no longer exist is not going to happen.)

 

[Marshall Brooks]

Insert into a table which has a dash in the name

I have a table called concept-relation and I want to insert into it. for ($i = 0; $i < count($sources); $i++) { $sourceID = $this->getConcpeptID($sources[$i]); $desID = $this->

stackoverflow.com

backticks for the win!!!

strSQL = "INSERT INTO `tblTable-Data` SELECT * FROM `LinktlblTable-Data`;"

Seems to work fine. I don't know of the backticks would work for a table without dashes also.

Two issues:

Originally I tried:
strSQL = "INSERT INTO `tblTable-Data` SELECT `LinktblTable-data.*` FROM `LinktlblTable-Data`;"
And that gave me an "illegal bracketing" error, so I changed it to just * and that worked, but I don't know if that makes a difference in the output.

The Primary keys are still in the 9-millions and if I added a new record, it gets the next sequential primary key, also in the 9-millions. I don't know if that matters at all, but it bothers me. Is there a way to rebuild the primary key field, or does that not matter, or would that create issues?

 

[jdraw]

I recommend that you do NOT use "-" the dash/minus sign in any field or object name.
Use a-zA-Z,0-9 and "_" in your names to reduce syntax\errors.
Access/vba uses double quote chr34 " and single quote chr 39 '.

Normalization is a basic tenet of relational database; should not be ignored

The purpose of a PK is to ensure uniqueness of a record within a table, so the specific number/value is not important.
If you are using autonumbers for PK, they are not necessarily sequential.

 

[The_Doc_Man]

If you have no relationships whatsoever, then a relatively simple renumber operation is possible, but if this is an auto-number PK, it is not necessary. Just so you know I'm not dusting you off here, I'll show you how to do what I don't think you need to do:

Content retirement

The content that you're looking for is now retired.

learn.microsoft.com

Since an auto number can reach very large numbers, like 2 billion, having a PK at 9 million leaves you some room as long as you don't have too many more gaps. As to arguments for leaving it alone, the PK auto-number is simply a way to give a record a unique identifier usable for rapid search operations. This number has NO OTHER PURPOSE and NO OTHER MEANING. We call this a "synthetic" PK because you synthesized it as needed and the only meaning it can EVER have is as a reflection of the order in which the record was created.

By contrast, suppose you needed to assign the number based on computations using some criteria (unspecified for this discussion). In that case, the number would have implied meaning derived from the way it is computed. That could still be a PK but it would be a "natural" number as opposed to the "synthetic" auto-number type of PK. You would never have to reset a natural PK but the link I showed you describes how to reset synthetic PKs in bulk.

Some accounting situations require contiguous numbering of certain types of records. These often cannot be auto-numbered because if you back out of creating an auto-number PK, you consume the PK (and discard it), thus leaving a gap. This case usually happens because of auditors who are so literal minded that they cannot abide by gaps. If you don't have an auditor looking over your shoulder, the auto-number PK is perfectly fine.

 

[Marshall Brooks]

New information and questions:

@Pat Hartman : Could you please clarify whether the SQL statement with just * instead of LinkTable.* is acceptable? It seems to work fine, but I'm not sure how it is supposed to work.

@The_Doc_Man : I guess it doesn't hurt anything, but having a 300 records table with PK's up to around 350, then 4 PK's in the 3000's and then 4 or 6 PK's in the 9-millions bothered me. Since I was working with a test DB file, there was an easier way to reset the PK's than the query and make new tables methods in the article that you linked to. It worked fine to just go into design view, delete the PK field, and then add a new PK field with the same name as the one I deleted. As I see it, this will do two things: My validation reports will need to be re-baselined - they do export the PK Field (so that the Excel Files are sorted the same way). So the first time I run a compare between the old and the new validation reports, I'm going to have probably 300 differences in the records, but the next comparison that should be sorted out. Some of my VBA code uses the PK of the current record as a marker, but if that is different from what it was two days ago, it won't care.

@jdraw : Understood on both counts. Dashes were a bad idea, so was not prefixing table names with tbl and form names with frm. However, to fix it at this point, I would have to change the names in the backend file, change the names in the forms that reference the backend tables, and change the VBA code references in the front-end - and then distribute the new front-end and backend files and in the mean time catch up any changes /additions to the tables from when I started. That's the easier change b/c the overall structure stays the same. If I change the table structure to normalize the data, essentially, I am creating a new database and it is a much more complex process.

I can see that you are loath to actually fix any existing problems so I'm not sure how much help we have actually been.

That's a bit harsh. I'm loath to restructure the database and keep everyone from using it for several days while I get it working, which I really don't have the skill set for.

New question:
One of the records in the table was missing today. It was there yesterday. There was no error messages. (I need to read Reply#55, but I'm trying to get the backend rebuilt first. It is a record belonging to the user that likes DS view. I talked to him and he said he likes DS view to filter the data and then he goes to form view to make the changes. He agreed to stop using DS view, but I'm not exactly sure how that would be the issue - From what you said, I got the impression that I could have data validation in form view (but I would think that applied to DS view also), but I don't really have validation in form view either. He asked if it was possible to have DS view still available but have it read-only where you can't make changes in that view. I don't know if that is possible.

 

[jdraw]

We're at ~70 posts in this thread, and seem to be "treading water" to some extent. Perhaps its time to show some of your table designs, relationships or specific code. No one here is trying to "steal your info", they want to help. It seems to me you're a little hesitant, but it would/could make communications more efficient.

We all know it is difficult to change something when the application is in production.

Do you have a prioritized list of issues/concerns?

For consideration-
In a test database: Copy from Production, Break the issues into manageable pieces, prioritize, mock up a scenario, test it thoroughly, show the user, get whatever approval(s) is needed, devise a plan to implement, monitor. Move onto the next issue.

 

[Marshall Brooks]

@Pat Hartman - Could you please answer my question about "*" vs. "LinkTable.*" in the SQL insert statement.

I didn't exactly say the database had to be taken offline to make any changes. I frequently change the front end and then release a new version. I occasionally change the backend. Typically backend changes require co-ordination. For example: Let's say I want to change table "tbl with space" to "tblWithSpace" To do this smoothly, I would get exclusive access to the backend, copy "tbl with space" to "tblwithspace". (Copy so either version can work with the old or the new front end.) Release the new backend. Release the new front end. Ensure nobody is still using the old front end, and then get exclusive access to the BE and remove "tbl with space". That's for a relatively static table. If the table is updated often, then basically as you said, I have to do the update during off-hours and then I replace rather than copy the table and if the old front end is used, users will get errors. Things line normalization and restructuring the data I don't understand, so once I understand that, and start working on it, I have to ensure any changes to the records in the production database from when I started the update are copied to the development database (likely in a different field or table) before I release it). (And since I'm not familiar with things like data validation and normalization and DDL it might be quite a while between start and finish of the updates.)

@jdraw I wouldn't say we are treading water. The thread started with "How do I search the entire database for "###"?" I figured that out. We've moved on to cleaning up the back end. That is in work. The final questions were database design.

I'm sorry, but the database would be considered proprietary by the company I work for. (Moreso the data, but the database structure and most of the forms/views it uses would be also.) I could possibly share it if there were a NDA, TPPA in place, but it would take 12-months to propose that and probably would be disappoved afterward. There have been times that I have changed field names for the forum to avoid giving away proprietary info and that has caused problems. Even if I could share the data, there are almost 91,000 lines of VBA code in the database. (I'm not saying that as bragging - anyone can write 200 lines of code that could be done in 5 lines with a loop or a subroutine. It more points out inefficiency of the code). What I'm saying is that it isn't something that I can post the code here - even with an NDA - and have someone in two days say "You have an error in this subroutine and it should say this."

Essentially, prioritized list would be: How do I get the database stable to where records aren't disappearing and/or if isn't saying it is in an inconsistent state or unrecognized format. As someone mentioned on Page 1, where do I buy the sunblock instead of the stronger aspirin?

(And I'm not implying that members have not tried to help me with that.)

 

[jdraw]

On response to #72, Agreed. And we're not saying you have to normalize the database. or that you have to rename and rebuild/redesign the whole thing. The suggestions I made were some thongs to consider to make communications more efficient.

Understand that we do not have the database, nor the code, nor forms, nor vba etc to see the issues in context. There may be some obvious thing(s) that someone reviewing the materials could see and advise.

You could get a list of your table designs and rename or obfuscate to remove company identify/proprietary info for sharing.

I do understand the company's concern, but there has to be some method/scheme if you''re going to get specific help/advice on a specific issue.

As for How do I get the database stable to where records aren't disappearing and/or if isn't saying it is in an inconsistent state or unrecognized format.

I think the underlying question(s) is Why did a record disappear? What caused the record to disappear? Do I have any backups I can review to see if other records have "disappeared"? Is there a pattern to this issue?

Anyway, readers are offering suggestions/advice/thoughts as best we can given the circumstances. Hang in.

 

[LarryE]

@Marshall Brooks You said: How do I get the database stable to where records aren't disappearing and/or if isn't saying it is in an inconsistent state or unrecognized format?

You re-design it properly in the first place from the ground up. You are attempting to use ACCESS in a manner in which it was never designed to be used. The ACCESS program itself will fight you every step of the way. Live with it or re-design it. Your choice.

 

[Marshall Brooks]

Select * and Select Sometable.* are equivalent. If there is only ONE table, then they can be used interchangeably. If the query joins two or more tables, you need to be specific and use the second syntax.

Thank you!

Professionals do not make BE changes during work hours except in emergencies. You do it as a matter of course.

Negative. I don't do it as a matter of course. I don't do it at all if I can avoid it. And typically my shift starts and ends a bit later than everyone else, so I take the last hour of the day for me and say "I need exclusive access to the back end and then you can use it again." It works out almost the same as non-work hours. (It helps that there are only 8 users.)

Let me explain something - not that it really matters to the forum. I am not a professional. I was not hired to maintain this database. My day job for the company has nothing to do with the database or the database design or even coding. I was an end user of the database and someone said "Hey, it would be cool if the database could do this." I enjoy coding and I've made a lot of progress with the database and done some cool things with it and it has almost become a full-time job and they are okay with that - to a point. If I say "I have an idea that will save our users ten minutes per record, but it will take me two weeks to implement it." They are fine with that. If I say "I want to spend a couple of weeks getting the database stable so I don't have to keep everyone out and repair it every couple of weeks.", that's fine also. If I say "I really don't know how to do this, but let me spend two weeks reading forum threads, and then I think we need to normalize the database. I'm not sure how to do that, so it will take me another 4-6 weeks to learn and implement that. Then I'll have to test and verify it works. Then I'll have to spend a few days updating all the records that were changed while I was developing it. Then we'll have to retrain our users b/c it won't look or act like they were used to." - not so much.

I consider myself moderately skilled with Access and VBA. I'm good at finding code online and modifying it to do what we need. I'm not and never claim to be at the level of most of the users on this forum.

If you start by cleaning up the schema, the database engine will help with preventing some of the errors but only if you are willing to normalize and enforce RI and enforce the use of Forms only for data updates.

RI - Record Integrity ???

I do have two questions:

• You mentioned validation code and The_Doc_Man mentioned constraints. I'm not sure most of the data supports this - lots of it is text fields, but when is it implemented. In other words - there are for example 40-70 fields per record per table. Do I have code on the Form BeforeUpdate event to validate all 40 fields, or do I have code on each field to validate it and if it passed that I don't need to check again, or do I check both places.
• As I understand it, the main problem is I am not doing any true data validation. I think the validation rules would apply to either DS or Form View, so I'm not sure why input via DS view is an issue. (I do understand that DS view more obviously lends itself to right-click delete and right-click copy/paste entire records, but otherwise??? (It seems to me like users would be just as likely to corrupt the data by pasting into form view as into DS view, but I don't understand the issue.)

I don't think anyone has asked. Do you have Option Explicit defined for ALL your modules? You should if you don't. Then make sure that all variables are defined and the code always compiles without errors.

Yes Option Explicit is defined. I verify code compiles. I do a decompile/recompile before release.

That said, I'm finding some "rookie" mistakes:

• In repairing the backend, I noticed a LOT of my tables don't have a primary key DEFINED. They have an autonumber field named PrimaryKey - but they never got the little key icon next to it.
• I'm sure you know this, but Option Explicit isn't foolproof. For example, you can declare a Public Variable (Global Variable) in a module and declare the same variable in a subroutine of the same module and the variable is no longer Public. Also, the same thing happens if you declare the same variable as Public in two different modules. (And Option Explicit won't flag this - it will if you do it twice in the same module or subroutine.) I think I caught all of those b/c the database didn't work as I expected it to, but ...

You could get a list of your table designs and rename or obfuscate to remove company identify/proprietary info for sharing.
I do understand the company's concern, but there has to be some method/scheme if you''re going to get specific help/advice on a specific issue.

Yes - we have about 10 replies in this thread where I said code didn't work but I changed the name of the table to Names which doesn't have a dash in it b/c I was trying to obfuscate. I understand where you are coming from also.

Also - I mentioned to my supervisor - not about this forum, but in general:

• The company is fine if I ask on a forum for help, someone posts a code snippet and I use that in my code. (Although they'd be somewhat upset if the code I copied deleted all the files off the servers without my knowing it or sent the data to an adversary.)
• Officially, the company considers any code that I develop during working hours (even if I just fix an error in the code I downloaded) to be intellectual property of the company. (That's a general guideline, not a hard-and-fast rule).

I pointed out that it will be hard to get help in the future or at least make me unpopular on the forum if all I do is post "Your code worked great, thanks!" or "I found a typo in your code and I fixed it and got it working, but it's now IP of my corporation so I can't tell you what I fixed."

He more or less said that general items are okay - his opinion, though - meaning he probably won't defend what he says they try to fire me for it. So to paraphrase - where earlier I posted the code to search every field of every table for "###" is probably okay. If I posted code with our network server names in it, and/or code that the company uses that a competitor might find useful - possibly termination offense.

I think the underlying question(s) is Why did a record disappear? What caused the record to disappear? Do I have any backups I can review to see if other records have "disappeared"? Is there a pattern to this issue?

1 and 2 - I wish I knew. If I did, it wouldn't be happening. (If I knew where I was, I wouldn't be lost.)
3 - As I said initially, I export some key and relatively stable fields of each table weekly to Excel Reports and then use a program called Beyond Compare to check for differences in the reports - Particularly Excel Rows that were in the previous report and are not in the current one and that I don't specifically know of a reason for anyone deleting them. Excel Rows that are in the current report and were not in the previous ones and I don't remember adding, or records that clearly from the data don't belong in that table. But I only implemented that in November of 2022. Prior to that, we would only know a record was missing if someone searched for it and couldn't find it and I'm sure we lost some records that we don't know about.
4 - Hard to say. Typically the records are "active" - i.e. something viewed or changed on the day they disappeared, but not always. (It happens, but it would be unusual for "Hey, this record from 2005 is gone." But as I said, prior to last November, we wouldn't have known. The database was inconsistent, the BE was C&R. It opens again. All must be good.) Most often, it is a record that either the user that likes DS View or I was working in, but we're also probably the heaviest users of the DB, so that doesn't surprise me. Can't really verify, but it seems possible that it was records that we both might have been in - so not necessarily both editing at the same time, but perhaps one of us editing while the other user was viewing. Makes me wonder about record locking and I looked into that before but never really understood it.

 

[Marshall Brooks]

Google helped me out on RI - Referential Integrity. Really, this isn't a relational database. Meaning:
There are seven main forms that link to tables in the backend. Each form links to it's own table.
There are three subforms on each of the 7 forms. Two of the subforms are linked to additional tables that are not the same as the tables linked to by the main forms.
The remaining tables are just reference tables that the VBA code performs DLookup (actually ELookups) on. (Typically things like Network Locations, Email Recipients, etc.

 

[Marshall Brooks]

Excel records coming and going are a different problem. You aren't using OneDrive to share files are you?

No we do share data on the servers though. And we export most of the database internally to Excel so we can have 200 people look at the data without giving them access to our database.

You misunderstood. We don't have Excel records coming and going. I going to obfuscate here, but ... Let's say I have tblClients. In that I have a PK and fields the don't change often such as First Name, Last Name and field the do change often such as LastOrderDate, LastItemOrdered. I have a qryClients query with the PK, First Name and Last Name fields (sorted by PK ascending).

I have a weekly macro (VBA code, not inbuilt Access macro) that deletes archive files older than 60 days, then takes tblClients_Prior.xlsx and moves it to an archive and saves it as tblClients_20230720.xlsx (whatever the file creation date was). Then it renames tblClients_Current.xlsx as tblClients_Prior.xlsx. Then it exports qryClients from the database and saves it as tblClients_Current.xlsx.

Then I have a batch file that calls beyond compare and shows me the differences between tblClients_Current.xlsx and tblClients_Prior.xlsx. If there is a row in tblClients_Prior that is not in tblClients_Current.xlsx and the record was not intentionally deleted from the database, I know it went missing. If there is a row in tblClients_Current that is not in tblClients prior, I know either it was intentionally added, or it is data that somehow got overwritten into the Access database table.

As I said initially, it's complex, but it works for us (well, me).

It would probably make more sense to you - another database admin in the company recommended, IIRC a left join of the current query and a copy of the query from the backup database, but this seemed simpler to me - (I see you shaking your head now, but that's okay ...)

Otherwise, you should decline and let your employer hire someone competent to do the job.

Sadly, the company somewhat thinks I'm a guru at coding ...

Maybe it's time that you copied the FE and the BE to your private drive and started actively trying to break things by being sloppy with copy/paste.

The front end is on my private drive. I have a copy of the BE in a "test" folder that I use for development so that I can change and create records without people wondering what is going on in the "live" database. Doing it this way allows for things like network latency that I wouldn't be testing if they were both installed locally.

I think you were kidding, but I don't think I've ever had an inconsistent state error with the development files, but that is single user.

I could add code that checks for a .laccdb file and says "Someone else has the database open currently, please try again later ..." (Now I am kidding ...).