Compact & Repair and Corruption

[AlienV]

Hi Everyone,

I'm new to the forum, Access and DBs since the late 90's in general. I used to do some DBA on a DOS based DB called R:Base. I am the sole user on a work DB that was created by another employee before he left. Three months later I said to myself "Self this thing probably needs some maintenance."

So I back it up and do a Compact and Repair...

My Monthly report is suddenly missing data! I decide to do a query for one of the specific months that are missing and guess what? It shows up. I update this database manually every day with copy and paste append from Excel csv files to which I add a month and date field. So I decide to use the backup, delete the data that is not showing up (except for one table) in the "compacted/repaired" DB, re-import and then do another compact/repair. Same results. There is one table which is not easy to wipe out because it has to be pulled day by day as opposed to a range. This table is the "left join" table on which all queries/subqueries are built. All indexes are autonumber. Should I export all the data and re-import? Can I just delete the indexes and it can rebuild?

Thanks in advance for your help.

 

[CJ_London]

It is always possible you have a corrupted row of data somewhere and compact in some way is removing data. But I don't understand this statement

delete the data that is not showing up

how can you delete data you can't see? Are you saying the missing data is in Excel but not being imported?

Rather than compact and repair, you can try creating a new db and then copying the data across.

I presume you have a front end/back end? Even with a single user it is advisable to keep data separate from everything else.

One other thing I have found in the past when importing data from a excel or a csv file is if there is a rogue semi colon in there somewhere it can cause the import to stop - even halfway through a row, though I don't know about copy and paste.

 

[AlienV]

Thank you CJ for your response.

Yes, I split the database. To clarify my Monthly Report query is missing a few rows of data which represent specific months. For example this years months are now missing from the report. However if I query those specific months (e.g. Month =1 or 2) the data will show up in the datasheet. I see the daily data in the tables its just not reporting it anymore in the query after the Compact & Repair. The data is paste appended and consistent from a web source. I think the data is clean so I will try to export it and have Access re-index.

 

[CJ_London]

is it possible you query is not correct - perhaps you can post the sql?

For example a common mistake is to group by month - but this means that Jan 2014 will be grouped with Jan 2015 - it works fine for 12 months and then....

 

[AlienV]

Hi CJ,

No everything is correct as the query has not been modified since inception. It's not until I did the compact and repair did it do this. My backup (pre Repair and Compact) gives me the data correctly. Interesting update though... I ran the C&R a few times with the same results at work but I take a copy home to run on my Laptop to do a C&R and guess what? It works fine after one try however this doesn't solve the root cause of the problem. Maybe it was luck (one data point). Home and work are both using 2013 with the only difference being Win 7 at work and 8 at home (much more powerful home laptop). The wrong results from the C&R at work were however consistent. It was the exact same month summaries (From daily data) that were not reporting even after I deleted and replaced the raw daily data (which was never missing only specific months of the aggregated monthly query) before attempting each new C&R. Strange indeed. Thank you again for your assistance.