Solved Automated search for Database Corruption Assistance

[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.

 

[Pat Hartman]

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.

Post all the code, and ensure that the .csv files are all linked with correct names.

 

[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:

Reset AutoNumber value in Access - Microsoft 365 Apps

Provides step-by-step methods to reset an AutoNumber field value in Access. This includes reseting an AutoNumber field in a single table or in a table with referenced tables.

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.

 

[Pat Hartman]

I think you mis-understood my comment.

No. I don't think so. You would delete the links.

there are some, but not many, in some of the queries in the front end.

Those are not relationships. They are joins. Relationships are permanent. They are created using the Relationships window and are used to enforce Referential Integrity. Joins are transitory and can be made between any two fields of the same data type whether the join makes sense or nor. You can join lastName to Address if you want. You are unlikely to get any results unless your data is a mess but you can do it. You would not be able to create a relationship between the two columns because the child table would contain values that do not already exist in the parent table. Plus the fact that you have joins between permanent tables implies that you in fact need relationships. Defining relationships also helps to protect data from being accidentally deleted. If the joins are from the child table to an autonumber, you will need to retain the autonumber values or your joins will break. If the joins are not from the FK in the child table to the PK of the other table, then you have built your relationships incorrectly.

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.

 

[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.

 

[Pat Hartman]

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,

Harsh? In what way? You've been given lots of advice ( as @jdraw pointed out, this is post 70+ and you have not made any progress) on how to attack the "corruption" problem but you have discarded all of it because it seems to you like too much work. You are only interested in automating your patchwork fix up routine rather than getting to the root of the problem. OK, just don't blame Access for the corruption because as we've delved into the situation, we've identified several suspect areas.

You keep assuming that you need to take the database off line to make changes. That isn't how development works. When you do development, you have your development copy of the FE and you have a development copy of the BE. Changes to the FE only are not a problem. You change your development FE. Test your changes. Then replace the previous master FE with the new version and relink from your test BE to the production BE. I do the distribution in this specific order. Why? It minimizes the potential that I accidentally link my test FE to the production BE and forget to change it. Moving the FE first works because 1. I always do this during off hours. 2. If I forget to relink, no damage is done. I just get embarrassed when a user calls me to report an error first thing in the morning before I've had my coffee. I don't distribute non-emergency changes to the FE during working hours. I do them after hours or on weekends. Then when the users come in the next morning and open the app, they get the new version.

Changes to the BE are much more complicated because unless you work weekends and can get the job done over one weekend, you will always have structural changes to make while you have live data in place. Again, I never make non-emergency changes to the BE during working hours. For simple changes to the BE that can be made without affecting the current application, I back up the BE and make the changes offline to the production version. Then I copy the production BE to use as my new test BE and go from there. If the changes will break the current app, then you have no choice but to apply the changes off line just before you distribute the modified FE. The safest way is to build a database that runs DDL (Data Definition Language) queries that modify the tables. Every change you make to your test BE requires that you immediately add the necessary DDL query to make the change to the production BE. Then you recreate your test BE by copying the production BE and running your DDL queries to change it to what you need for the new version. This way, if you've made a mistake in the DDL, you can pick it up in testing.

Development can be tedious but discipline is critical to your ultimate success. Stop taking shortcuts. Make sure you dot your i's and cross your t's. Professionals don't take shortcuts. If they've made a poor design decision, they recognize that it is more trouble working around the issue for the remaining lifetime of the application than fixing the problem and moving on with a solid foundation. Even changing the names of all the objects to get rid of the spaces and special characters isn't as hard as you think it is. You just need a tool like MZ tools that can help you to find and replace all names. Find and Replace is a very dangerous operation. If you're not careful, you can make a real hash of your code so frequent backups are critical.

These links will get you started with DDL. This technique is the safest and easiest method to make design changes to your BE. You can develop the DDL queries as you discover changes you need to make. Remember - DISCIPLINE is your friend. Don't take the shortcut of making the changes to the BE directly. Create the DDL and run it to make the changes. That way, you have some assurance that there won't be any changes you forgot when it comes time to release the new version.

Access Data Definition Language (DDL): Creating and Altering Tables On The Fly | Database Journal

One of the first things an Access developer learns is how to write SQL Queries. A simple request for data might look something like this: SELECT

www.databasejournal.com

Data definition language (Microsoft Access SQL)

Office developer client VBA reference documentation

learn.microsoft.com

 

[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.)

 

[Pat Hartman]

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.

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

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 have to start listening to what we have been telling you. And, you have to do it all by yourself because you can't even post the schema. It is your own forms, your own code, your users switching form views, etc. You need to look at all of it.

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.

I have some pretty large applications and they don't come near to 91,000 lines of code.

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.

 

[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.

 

[Pat Hartman]

We do know you are not an expert. But, you are performing a technical job and you should endeavor to do it the way a professional would do it. You have a fiduciary responsibility to your employer to ensure the best data you can provide. Otherwise, you should decline and let your employer hire someone competent to do the job.

RI - Record Integrity ???

RI = Referential Integrity
Declarative RI is whatever the database engine can perform to ensure the validity of data. Things like only allowing numbers in numeric fields and dates in date fields. In neither case does the database engine have any domain knowledge so it has no way of knowing if future dates are invalid or if 0 is invalid. All RI can do in this case is to ensure something is not null. RI also applies to parent/child relationships. For example, if you use a State table and create a relationship between StateID in tblState and StateID in tblCustomer, then you can define StateID as required, in which case null will not be allowed. But even if null is allowed because the field is not defined as required, you cannot enter a state that is not defined already in tblState. Then once you have established a relationship for a record that has an address in Connecticut, RI will prevent you from deleting Connecticut from tblState. However, if you delete the only Connecticut customer, then the database engine would allow you to delete Connecticut from the state table.

Then we'll have to retrain our users b/c it won't look or act like they were used to." - not so much.

But the point should be made this way - something the users are doing is causing valuable company data to be lost or changed. I need to find it and fix it but it could take a month and the changes required might cause some interface changes. You are not making arbitrary changes just for the sake of change the way MS does to sell new versions of Office. You are making changes to ensure that valuable company data is not lost or corrupted. It is a point of view. One will get you a hard no, the second will get you a hard yes.

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.

Every place I mention validation, I mention the Form's BeforeUpdate event. That is the best place to put the code if you only want to have it in one place. In some situations, you might want to validate closer to the point of entry and in that case, you can use the Control's BeforeUpdate event for certain things such as uniqueness. However, if you are validating that the control is not empty or that the data is >= to some other value such as StartDT <= EndDT, the best place to do it is the FORM level BeforeUpdate event. By the time that event runs (It is the last event to run before data gets saved) all the data that the user planned to enter has been entered so it makes sense to perform relationship edits there. Also, control level events never run at all if the control is never entered or dirtied. So, checking in the Control's BeforeUpdate event for a value is pointless because all situations cannot be handled so that is why I recommend using the FORM level BeforeUpdate event. That way, all your code can go into a single event which makes it a little easier to control.

You need code for each field if you are validating contents. Some people use a loop to validate presence of data. I use the Tag property of a control to handle that. Not all controls are required to have a value. For those that are required, you place a value in the Tag property such as R (for required). Then you loop through all controls and for each one where the Tag property is "R", you do the "required" validation. If you look at the sample I posted that goes with the videos about validating data, you should see how validation is handled.

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.

One of the reasons I'm very careful with my variable declarations. I also very rarely use global variables.

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

I named that link I posted for you - "bad data is bad for business" because it is. Perhaps, your company should hire a professional to see if they can clean up the database and ferret out some of the data destruction problems. I am not volunteering.

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.

 

[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 ...).

 

[jdraw]

Further to Pat's comments /advice, several on the forum(s) have evolved from enthusiastic user to DIY programmer/analyst/maintainer/bottle-washer and cook. However, if this database is critical to the organization, then it is time to recognize the importance of the data and database. Since the company has highlighted/recognized its IP value, it would seem prudent for them to train you to better handle your "database activities" and their critical data. I'm sure the company wouldn't take a landscaper or a janitor and put that person in (part time/after hours) charge of Finance --that's just an analogy to stress the role of you/your database in the organization.

You haven't told us the industry the company is in, nor whether or not there are other databases and groups within the company. Perhaps you could tell us a little about the work in general terms (not divulging any secrets). To me it seems the concern with IP and dependence on msAccess that there may be more to your environment than this thread represents.

Your position at the moment reminds me of someone learning to sail. You learn by doing,but you have to get wet from time to time to understand how sails, sheets, weight transfer work together to propel the boat where you want to go.

There are some excellent youtube videos, some great books, many forums that can help. Google can be your friend. But I think the company should support your efforts with some training. Remember, training puts info into your head and the training and experience helps you as well as the company.

We are trying to be helpful.