Data Issues...

[AZSEEK]

we have an Access 2000 database (about 61MBs) that we use extensively... the database gets compacted and repaired on an every other night basis...

has anyone hear of Access changing data in a field?

the field that gets changed is the same one -- and it doesn't happen to a great number of records but one or two at a time which is enought to cause headaches -- happens every so often...

 

[randysmid]

Hi,
First, you may want to check the field type, and make sure that it is large enough to accomodate the data being stored.
Second, you may want to add some code that will keep track of each individual who enters the database, along with the date and time. This info would be kept inside a table which is hidden. I wrote an FAQ on how to determine the logon name of the user in Windows 2000. It is faq705-2814
Third, using the code to determine the user name, you may also want to track each time a form is opened, especially any forms that are capable of changing the data you mentioned.
Finally, an additional security idea is to have all your code in a front-end database, which is then compiled into an MDE file.

HTH,
Randy Smith, MCP
California Teachers Association

 

[willir]

AZSEEK

Hope you are on the way to resolving your problem. Randy is a seasoned vetran and has given you excellent advice.

My thoughts are that you need to determine if data errors are occuring through a user or software. I gather from your query, that you may think that the compact / repair process may cause the error. I guess this is possible, but I think you need to explorer other avenues.

Do you indicate the same field gets changed which suggests a pattern. "One or two at a time". Do the values get swapped? Or nonesense values appear? Is this a primary or foreign key that gets messed?

Per Randy's advice, have you been able to track down which form or forms is used to enter / edit the data? Is it new data or existing data that gets messed?

Next, how comforatable are you with the database being 61 MB in size? Is it time to archive old data?

Richard

 

[randysmid]

Richard,
You have provided some excellent additional pointers!!!!! Anyway, I have a feeling that someone is intentionally changing their data. I've been working with Access for 5+ years, and have never seen Compact & Repair modify data. However, I have seen this happen on importing, when the field size is not adequate to accomodate the data being imported.

HTH,
Randy Smith, MCP
California Teachers Association