Question Need tips to avoid corruption for split database

[timf79]

Hello,
I have a database where users have locally installed a 2007 or 2010 front-end (FE). The back-end (BE) is on a file-server accessible by all users.
Unfortunately the network connection is not always the best, resulting in possible interruptions during a write operation.
Which then causes the record in the BE to get corrupted.
As a result I need to go back to the last saved backup and restore the record.
To make things worse, the main table uses an auto number field which logically does not allow me to restore the record with the previous used auto number.
Resulting in the item getting a new ID number.
Also the BE is only 2MB big, but it takes long to open up.

Question:
- What options (VBA, FE/BE design) do I have to make the BE less vulnerable to corruption in a slow network environment?
- What options do I have to make the FE/BE database faster?


Thanks
Tim

 

[timf79]

For some reason, Access databases that are buried deep within directory trees are slow to open. Move the back end as high up in a path as you can to minimize this problem.

It is in the top-level already

Compact the BE at least weekly.

The database get's used maybe three times a month, so would a auto-compact on clos ein the BE be sufficient? (If so how do I avoid it saving a copy everytime?)
The BE is ACCDR, does this still work with auto-compact at close?

Back up several times a day if you can to limit your data loss possibilities.

See above.

Access is the canary in the coalmine when it comes to identifying bad network connections. For this reason, it should never be used on a wireless network because they are even more vulnerable to interruptions.

I have no control over how users connect to it. Can I code in VBA to not allow WLAN?

If the BE becomes corrupted on a regular basis, your only real alternative is to switch to SQL Server which causes less of a problem. SQL Express is free so if you don't have already have a full version installed, you can go with that. It has fewer bells and whistles as far as its GUI goes but doesn't limit the actual usage of the database except to limit its size to 2G which is the same as Access.

Can I put a SQL Express DB out on a normal file-server?

BTW - you can use an append query to copy records with autonumbers from one table to another. Just include the autonumber in the query. This is the only way to actually provide an autonumber. You can't type them in or copy and paste them which are probably the two methods you've tried.

Thanks, I will try this next time.

The most frustrating thing is.
That the intention was to collect input from around 9 sites, but as it turns out only 2 sites are really using it
Mgt request still come per e-mail, instead of mgt using the database for reporting.
So at the end I use the tool to generate reports for my 2 sites to send them out via e-mail.
For that purpose I could have gone with a BE on the local server (no interruption issues) *sigh*
Needless to say, according to our IT dept. the location they gave me is the best we have!

 

[Simon_MT]

How are the two remote connecting to the database?

Simon