Access file corrupt

[Bieke]

Hello Guys,

A few months ago my database did not respond right anymore from time to time. For instants, sometime when i want to add a record or so i’ll get errors and it is random in all forms. When i then use the function compress and repair the problem is solved. in the begining it happens once a month but today it happens almost every day. I’m affraid that one day i can’t fix it anymore.
Is there anything i can do? I saw on the internet that there are programs who can fix this but i am skeptical and affraid that i pay some money with no result. If somewone knows a program that can be trusted or an other sulution i like to hear it

Regards,

Bieke

 

[Bieke]

There are some steps to start with.
1. compile the application and fix any compile errors.
2. compact the database
3. run the decompile action
4. compact again
5. open a new, empty database
6. if the db is not split, start now by importing all the tables into the new, empty db
7. open a second, new, empty database
8. link all the tables from the new BE
9. import the FE objects.

you might not need to do all the steps but do at least 1-4.

Here's the directions for the decompile:

Thanks Pat, i’ll try this and let you know if the problem is solved, thanks in advance.

Bieke

 

[isladogs]

I would have given the same advice. Decompiling often fixes issues if the corruption is in your code.
None of the methods will fix corrupted data.

I've tried several of the recovery programs. In general, they do little that you can't do yourself. Often they only do a partial repair.
Usually not worth the price unless you want convenience

 

[Bieke]

I would have given the same advice. Decompiling often fixes issues if the corruption is in your code.
None of the methods will fix corrupted data.

I've tried several of the recovery programs. In general, they do little that you can't do yourself. Often they only do a partial repair.
Usually not worth the price unless you want convenience

Thanks, i‘ll come back with the result. Have a Nice weekend.

Bieke

 

[Bieke]

Hello Guys,

I`ll tried to run the decompile function but when i prepare this by compiling the code everything looks to do well, the compile function in the menu turns grey and there are no faults. This is the only thing that i do at that time. When then i close the database i´ll get the message that the database is damaged. Also if i try to change something in the code and close the db (save it) when i open it again the db is corrupt. As long as i do nothing with the code everything is stil function. Soweone that experience with this problem? Any ideas what i can try.

Regards,

Bieke

 

[CJ_London]

Decompile your code before compiling

 

[Bieke]

Nothing helped but i have seen what the problem is, the db size is 2GB and i read that is the maximum.
I have made a front end but the backend is still almost 2GB after compression. I want to split the backend in two different db's both with a part of the tables. After the db is split in front-end the tables geting all a link to the created backend . Because i want not all tables from 1 backend i want ot redirect the link of certain table to a second backend. I can delete the link from 1 BE and make a new link to the other BE but when i do this my relationship of this table is gone. I can setup this relation again but because of the existing data in this table i can not select the referential integrety. Is there a way to just leave the linked tables in the FE and just redirect to another BE without removing the relationship?

 

[GPGeorge]

It sounds like you've hit the point with Access where you must seriously consider upgrading the back end to SQL Server or another server-based database engine. I seriously discourage splitting tables among multiple back end accdbs for the reason you have already noted: Having multiple backend end accdbs eliminates Referential Integrity. There is no way around that.

SQL Server Express is a free download. It is more secure than Access, plus it can have up to 10GB of data per database.

 

[isladogs]

Agreed.
However, if you've hit the 2GB maximum file size already, your database may well now be corrupt and impossible to recover.

 

[Bieke]

It sounds like you've hit the point with Access where you must seriously consider upgrading the back end to SQL Server or another server-based database engine. I seriously discourage splitting tables among multiple back end accdbs for the reason you have already noted: Having multiple backend end accdbs eliminates Referential Integrity. There is no way around that.

SQL Server Express is a free download. It is more secure than Access, plus it can have up to 10GB of data per database.

I have never used sql server. Can i reuse my access program or code when i switch to sql server or do i have to start from scratch?

 

[Bieke]

Agreed.
However, if you've hit the 2GB maximum file size already, your database may well now be corrupt and impossible to recover.

No it is stil working but if i try to change my vba code en save it than it is corrupt. Luckely i made a copy of the original before testing. But it is a matter of time when i put in new data. :-(

Thanks

 

[GPGeorge]

I have never used sql server. Can i reuse my access program or code when i switch to sql server or do i have to start from scratch?

It's possible, yes. However, it's not trivial. Performance can actually deteriorate, so careful design (and/or re-design) of your interface could be required. Given your situation, I'd recommend you investigate it because you'll continue to have problems with an accdb anywhere close to the 2GB limit. However, don't rush into it. Bingoogle Access with SQL Server. You'll find tons of resources.

 

[Bieke]

It's possible, yes. However, it's not trivial. Performance can actually deteriorate, so careful design (and/or re-design) of your interface could be required. Given your situation, I'd recommend you investigate it because you'll continue to have problems with an accdb anywhere close to the 2GB limit. However, don't rush into it. Bingoogle Access with SQL Server. You'll find tons of resources.

I´ll give it a try, thanks.

Bieke.

 

[oleronesoftwares]

I have never used sql server. Can i reuse my access program or code when i switch to sql server or do i have to start from scratch?

I think you have to write SQL procedures, but there might be some tools only that can convert the access tables to SQL tables

 

[Bieke]

I think you have to write SQL procedures, but there might be some tools only that can convert the access tables to SQL tables

Thanks, i keep it in Mind.

 

[CJ_London]

It may be that compact/repair isn't working because of the 2Gb limit, so rather than compact/repair of your BE, try creating a new db then import some of the tables from your old BE and check how the file size grows - keep going and checking.

You also you have to ask yourself why you are at the limit. Just speculation but using attachment fields is one thing that will lead to bloat (and can't be ported to SQL Server anyway). Importing large amounts of data and then only referencing a limited number of columns/rows is another. Better to only import the columns/rows required.

And with regards relationships, they can only be defined in the BE. Have two backends then keep tables which are closely related in one or the other BE. As you say you cannot enforce relationships between tables in different BE's

 

[Bieke]

It may be that compact/repair isn't working because of the 2Gb limit, so rather than compact/repair of your BE, try creating a new db then import some of the tables from your old BE and check how the file size grows - keep going and checking.

You also you have to ask yourself why you are at the limit. Just speculation but using attachment fields is one thing that will lead to bloat (and can't be ported to SQL Server anyway). Importing large amounts of data and then only referencing a limited number of columns/rows is another. Better to only import the columns/rows required.

And with regards relationships, they can only be defined in the BE. Have two backends then keep tables which are closely related in one or the other BE. As you say you cannot enforce relationships between tables in different BE's

Hey CJ,
I discovered that 1 table ‘products’ is the one with almost 2GB filesize. This table has indeed a field with attachment wich contains photos. These are responsible for the big filesize. I now going to replace this field by a hyperlink field and store then the photos on server. Thanks for thinking along with me.

Bieke

 

[isladogs]

Hyperlink fields are also a problem. Change it to a text field in which you store the path to each photo

 

[Bieke]

Hyperlink fields are also a problem. Change it to a text field in which you store the path to each photo

I first test with replacement to hyperlin, if not satisfied I change it to textfield.
Thanks.

 

[isladogs]

SQL Server doesn't support hyperlink datatype (or it least didn't when I last looked...)
So you'll be just wasting time with no real benefit if you try that first.

But its your database and your choice ....