Corrupted DB error (and can't load attachments any more)

[zkrucz]

I have a DB of 3 gigabyte (6000 records and about 100 fields) size which includes attachments (images). We use it to load (import) records from PDF files sent in by technicians.



Until recently, we had no issues. I maintained the DB by compacting it weekly.

However, as of a few days ago, the DB appears to have been corrupted and now after compacting the DB (a few times a day now), the "DB corrupted" message pops up and I have to compact the DB again.

Each time, we can load (import) only a few records only and now the images (attachments)) will not load at all.

How can this be fixed for good? Or can it?

Is the DB too big (could that be the problem)? How big can an Access DB be before running into issues?

[ranman256]

you are only limited to 2gig. It starts acting weird at that size.

I would stop attaching images. Instead store the PATH to the image in the table. You can still open them.
The size will be much smaller.

[orange]

How big is the database? If it's nearing the 2GB mark as ranman says, you could be hitting the limit.

[Micron]

Your issue is why many of us advise against storing any attachments in an Access db.
If you must load the images or attachments into tables, you will have to move to a back end database other than Access, such as Sql Server or MySql.
Or you can split into multiple back ends, which might be a pain.
Orange, I think db is reported in original post as 3 GB, which is surprising to me that it could get that big - not that Windows never mis-reports a file size...

[isladogs]

I'm also amazed it reached 3GB before finally kicking the bucket.
The question is how to retrieve the data from it at this point

It can't be imported in SQL Server due to the attachment fields which AFAIK aren't supported (at least they didn't used to be)
My suggestion would be to import tables to several different databases with each table containing attachments placed in its own database
Hopefully that will mean no individual database is anywhere near 2GB
If so, work through step by step removing the attachments & replacing with fields containing the path to the images (as Ranman has already said)
Once all of that has been done, compact each in turn and merge all tables into a single database again & compact again.

Of course, if all 6000 records and 100 fields are in one table (which sounds likely!) you are well and truly screwed....

However, you have of course been keeping regular backups ..... haven't you?
I have a feeling I know the answer to that question is ...NO

[Micron]

I think you can now (as of 2012 at least). I'm no expert on the subject - I just believe (sometimes) what Google tells me.

[isladogs]

That had passed me by ....but being as I never use attachment fields perhaps not surprising.

This link looks useful https://stackoverflow.com/questions/...e-system-files

[zkrucz]

Well, I suspected that size had something to with it. And, I do have backups. Until now, no issues. I will need to discuss with my IT tech to see how we can do what you are recommending.

Thank you for your comments.