Access file corrupt (1 Viewer)

Bieke

Member
Local time
Today, 10:19
Joined
Nov 24, 2017
Messages
57
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 19, 2002
Messages
42,970
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:


The Microsoft Access/Decompile command

Fortunately, Microsoft Access offers a way to fix the VBA compiled state with the /decompile command line option. The Compact and Repair feature only applies to the tables and does not impact VBA code. The decompile command does.
When Access is started with this and you open your database, it discards all the old VBA compilations, and leaves just your VBA source code. You'll need to compile again to get the compiled state. After compacting, your database may be significantly smaller.
To decompile your database, follow these steps:
  1. From Windows Explorer, locate where your Access program is installed by searching for MSACCESS.EXE. If you have multiple versions of Access installed on your machine, you need to identify the path to the Access version you want.
  2. From the Windows, Start, Run command line, type: msaccess.exe /decompile where msaccess.exe includes the complete path. For example, the 32-bit version of Access 2016 is commonly installed here:
    C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE /decompile
  3. From Access open the database you want to decompile (with trusted authority for Access 2003 or later)
  4. Open up any module. Compile it via Debug, Compile.., then File, Save.
  5. Go back to the database and Compact it. The location of the Compact command varies by Access version.
The database size should be reduced and the strange errors related to the VBA code should be gone.
The example above is for Microsoft Access 2016, but it also applies to Microsoft Access 2013, 2010, 2007. 2003, 2002, 2000, 97 and earlier.
 

Bieke

Member
Local time
Today, 10:19
Joined
Nov 24, 2017
Messages
57
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

MVP / VIP
Local time
Today, 09:19
Joined
Jan 14, 2017
Messages
18,186
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

Member
Local time
Today, 10:19
Joined
Nov 24, 2017
Messages
57
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

Member
Local time
Today, 10:19
Joined
Nov 24, 2017
Messages
57
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

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2013
Messages
16,553
Decompile your code before compiling
 

Bieke

Member
Local time
Today, 10:19
Joined
Nov 24, 2017
Messages
57
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

Grover Park George
Local time
Today, 02:19
Joined
Nov 25, 2004
Messages
1,775
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

MVP / VIP
Local time
Today, 09:19
Joined
Jan 14, 2017
Messages
18,186
Agreed.
However, if you've hit the 2GB maximum file size already, your database may well now be corrupt and impossible to recover.
 

Bieke

Member
Local time
Today, 10:19
Joined
Nov 24, 2017
Messages
57
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

Member
Local time
Today, 10:19
Joined
Nov 24, 2017
Messages
57
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

Grover Park George
Local time
Today, 02:19
Joined
Nov 25, 2004
Messages
1,775
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

Member
Local time
Today, 10:19
Joined
Nov 24, 2017
Messages
57
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

Passionate Learner
Local time
Today, 02:19
Joined
Sep 22, 2014
Messages
1,159
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
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:19
Joined
Feb 19, 2013
Messages
16,553
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

Member
Local time
Today, 10:19
Joined
Nov 24, 2017
Messages
57
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

MVP / VIP
Local time
Today, 09:19
Joined
Jan 14, 2017
Messages
18,186
Hyperlink fields are also a problem. Change it to a text field in which you store the path to each photo
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:19
Joined
Feb 19, 2002
Messages
42,970
Although you cannot enforce RI between separate BE's, you can still have them if necessary. Frequently, I need to link to tables in the BE's of other Applications. Sometimes I even have links to SQL Server as well as other ACE BE's.

However, I agree with the plan of action. Removing the embedded photos will probably solve the problem.

BUT since the db isn't split, that should be the first task.
 

Users who are viewing this thread

Top Bottom