How to avoid database repair message in a multi-user environment. 2

[mlrmlr]

Just recently my users have been receiving the database error message below. I have this database that is located on a share drive and is accessed by 15 users. On busy days, all users can be logged in and inputting data at pretty much the same time.

Users have been using this database for at least a year. It is not until recently that I’m seeing more and more issues pertaining to the message below.

Can anyone help me out with this one? Is there someway to avoid this type of issue in a multi-user environment?

<BEGIN ERROR MESSAGE>
The database ‘WO.mdb’ needs to be repaired or isn’t a database file.

You or another user may have unexpectedly quit Microsoft Office Access while a Microsoft Office Access database was open.
Do you want Microsoft Office to attempt to repair the database?
<END ERROR MESSAGE >

Thank you so much!

 

[webturner]

Is the database split into the interface and data using linked tables or are all the users opening the same database with both the data and apps in on a shared drive?

If it's the latter then splitting the files would help.

It could also be an ingrained problem that the repair is unable to fix. This can usually be cleaned out by importing all the objects into a fresh database.

15 users is pushing the practical limits of msaccess databases, depending on the amount of data they are inputting. It might be time to think about a MySQL/SQL server solution.

Steve

 

[mlrmlr]

Steve,

Yes, it is the entire database on a share drive being used by multiple users.

I noticed that when I repair the database and open the table there are pound signs (#) in each field. I delete those records and the database is working for a while then the problem occurs again.

So you suggest for starters splitting may help this issue?

 

[JerryKlmns]

Besides the split (FrontEnd/BackEnd) many, many, many, others (me included) - if not every1 - shall suggest to compact & repair the BE .mdb file in a daily basis. At least weakly.

And BTW! pound signs (#) means lost record! Dreadfull?!?

 

[mlrmlr]

Well, I'm doing something right. I do compact and repair several times a week.

Thanks again for your help.

 

[webturner]

I'm afraid its not right if you are being forced to do a compact/repair. JerryKlmns is talking about preventive maintenance, not bug fixing.

Splitting the database FE/BE will greatly reduce the load on the file on the server. It means there will be less chance of contention and clashes that could be contributing to your loss of data. It will also greatly improve performance as your users main interface will be on a local file rather than networked. It also means that most of the development doesn't need to be on a live database.

 

[mlrmlr]

Excellent!

Thank you for your explanations. I am definately going to split the database.

Thanks again Webturner!

 

[MichaelRed]

An issue w/ data entry in a multiuser project often is involved with the primary key being duplicated on record generation / save. It is particularly vorulent using Ms. A's Autonumber. There is a FAQ (faq700-184) which explains this problem and illustrates a possible work around.




MichaelRed