Losing Records in Tables

[Shayne]

My company had an Access db created back in 2002 in order to create file names (Legal Labeler). The db consists of many reference tables (Figure 1). The person who created the db created a form to use in order to create the file name and labels (Figure 2). As you can see there are various drop downs where the end user can choose which information to include. I am not an Access guru by any means but I do not think that the tables are linked. Once the end user fills in the form they click Save Label Data and the data is written to a master table (Table tblLevelOneSubject).

One of the problems we are having is that records in any one of the extraneous tables (Company Name, SubCatetory, SubCategories L1/L2, etc.) just disappear. For example, in Figure 3, we have all the entries for the drop down labeled SubCategory (“A” codes are depicted) but, for some reason, we are randomly missing some of the codes (typically all the “A” codes and “B” codes); however, nothing from the master table is missing (that is why I do not believe they are linked).

Another major issue we are running into is in the table named tblLevelFiveSubCatTwo (SubCategory L2 on the form) (Figure 4), which is an auto-numbered table, we are also losing records (as you can see it table starts at 7). When I created a file last week I needed to create a new entry in the drop down SubCategory L2 and it was auto-numbered 8 when it should have been 734. Also, the table shows that there are only 697 records but if we are auto-numbering there should be 734 records. Therefore, we are losing records in this table as well.

Does anyone have any idea as to why this is happening? Is there any way we can secure the tables so this does not happen in the future?

When I export the data into Excel it looks as though all of the data is accounted for - it is just missing in the reference tables.



Any and all assistance would be greatly appreciated. If you need additional information please do not hesitate to ask.

Thank you.

[June7]

If you want to provide the database, will analyse. Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post or upload to fileshare site such as box.com and post link to file.

[Shayne]

If you want to provide the database, will analyse. Make copy, remove confidential data, run Compact & Repair, zip if large, attach to post or upload to fileshare site such as box.com and post link to file.

Thank you for your response; however, after checking with others in the office I am not allowed to do so.

[orange]

I have no idea why it appears to be missing records, but I agree with June7--- a simplified version of your database would be required to do some debugging/testing. It would not require a full copy. It would require table structures and forms/processes, but only enough records in the tables to show the problem. You could edit the descriptive text in the records to be things like "A123Lvl4Dummy" etc., but leave the relevant Id or keys.

We are not interested in the data values per se; just trying to help resolve an issue.

Regardless of what you decide, can you post a copy of your Tables and Relationships?

[tanvi]

If you did not find the Linked Tables, it means there is any issue in linking:


1) Did you move your database?


If yes, you can use the Linked Table Manager to refresh links.


2) Do you have change the name of Microsoft Access tables after they were linked?


If yes then you must delete the current link, after that link such tables again.


There are some other possibilities, but these two are most common.


To solve the issue of missing records, first you should need to investigate- why the records are missing?
You can easily investigate the reason after reading this article: http://office.microsoft.com/en-us/ac...001151085.aspx


You can recover missing records by using Compact and Repair utility.