Transferdatabase method with corrupt database

msblane

Registered User.
Local time
Today, 15:56
Joined
Aug 1, 2012
Messages
10
Hi,
I have a database split into a back end and front end. I am having trouble with the database corrupting (long story - cannot change the situation). What I want to do is write some code to copy the table structures from the existing BE to a new clean database and also copy the objects in the front end to a new clean database. My question is, if I use the transferdatabase method, will it copy over the corruption? If so, does anyone have an idea of how I can do this?
Thanks,
MSB
 
Describe the corruption, when it occurs, what action causes it.

Sent from my SM-G925F using Tapatalk
 
if the db is corrupted, chances are the transferdatabase method will fail anyway.As Unc has suggested, describe the corruption - there may be a way to recover.Rather than using transferdatabase, try creating a new db, then using the external data tab to just import the table definitions (select 'definition only' when you click the options button).As to whether the corruption is transferred - if the corruption is in the data then definition may be OK, but if the structure is corrupted, it won't transfer anyway.
 
The corruption is caused when the FE loses connection with the BE because of a network failure or power loss (living in SA we are unable to avoid this). The first symptom is usually that some of the queries in the front-end seem unable to recognise the field names - so that they read Expr1:FieldName, Expr2:FieldName - and then, obviously, all the forms and/or reports based on them do not work. In the past I have found some records in the underlying tables to be corrupt. I have so far recreated the database manually to fix - both the BE and, if that doesn't work, the FE as well - at the first sign of corruption. I am hoping for a faster method to fix the problem. I have used code to write the data into Excel to clean and then reconstructed the corrupted data from a backup. I have had to use code because there are some memo fields and the data gets truncated if I just export it either through a query or through the transferdatabase method.
 
You could create a bat file to copy the db running it every hour or half hour (incorporate date and time to make the file name unique), then you could copy the latest good copy back when the corruption occurs.


Sent from my iPhone using Tapatalk
 
From your description, it may not be the backend has become corrupted, but the front end has lost the link - it may be that by refreshing the links the error will resolve itself.

With regards, copying, although I have never experienced it, the problem with copying whilst the backend is live is it may copy a half completed transaction resulting in a corruption. Also, your bat file will need to be run from the server where the back end is located, not from a connected pc.

I would also investigate using begintrans/committrans method for updating the backend - all db events between the two calls will not be committed to disk until committrans is run. Downside is if there is a failure then the entire transaction can be lost so user needs to be made aware so they can enter again, or you have some routine to refresh links and try again.

You haven't said, but I presume each user has their own copy of the front end? users sharing the same front end can lead to corruption.
 
There have been several posts over the years of Access' apparent corruption when network (especially wireless) fails. Most advice is to replace the Access BE with SQL Server (often the free Express version) if this is a "routine/regular Access application".

You could try CJ's suggestion, but my guess is you would do better by revamping the network to make it more stable. Not sure of what options you may have.

Google with these terms "ms access corrupt when network interrupts"
to see related info.
Good luck.
 

Users who are viewing this thread

Back
Top Bottom