Access Database Corruption 1

[avarga82]

Please help!

My organization was running Access 97, and sharing a file across 75 machines. It corrupted appr. 3x per week. We narrowed it down to a couple machines on the network with file & version inconsistencies, and it was running great for a week or so. We just recently upgraded all workstations to Office 2003, and upgraded the file to 2000 format. Every machine using the database is identical, and are running the most current version of Office, as well as all current updates, Jet service packs, MDAC, etc. The first day in use it corrupted 9 times! It runs very slow also.
I've read countless articles on maintaining Access files, on preventing corruption, etc. I've disabled opportunistic locking on all client machines as well as the file server, I've made it clear not to shut down Access forcefully, I've monitored our network to see any spikes in traffic or unusual activity, it's a split DB with both sides using Access 2000 file format, etc. Short of calling Microsoft, I'm all out of ideas. Does anyone have an idea....?

 

[psemianonymous]

75 people is just a lot of people. I assume you've already split into a frontend/backend scenario? That helps.


Otherwise, consider upsizing your backend if that's feasible. You can also maybe use replication if data 'freshness' isn't too big a deal, and if you're willing to try.


75 people is a lot. All I can say is try and minimize contact with the backend (less bound forms, especially bound to huge tables).


But again, 75 is a lot of users. I don't think you're doing anything wrong; I think you're stretching Access to its limit.

 

[Hap007]

Dear avarga82,

You state that 75 users are sharing the file.

1) Do you mean 75 users are opening the same file at the same time?
or
2) Does each user have a local access file that is linked to the shared Access data(Backend)

Although access will support 75 users(or more), to handle that work load, you should have your application designed so that you have a FrontEnd(Program with code, forms, queries and Reports) that links to a backend(Access file with just the tables) This would be method 2 mentioned above.
The FrontEnd should be on each user's local drive and the shared backend has one copy located on the shared network drive and folder.


I need to know this inorder to offer more suggestions.

Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[Hap007]

opps, sounds like foolio12 and I have the same questions for you, but he types faster

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[Cretin]

I would also suggest you compact and repair your database regularly like once a week or once every other week. If access databases get to big they do tend to get corrupted more often.

Cretin

 

[avarga82]

Thanks for the quick responses!! Here are a couple more specifics...

1. Yes, it is split. The backend is strictly tables, and the frontend is everything else. However, both files are on the file server. It doesn't make sense for every client to have their own file (although that would decrease corruption). Development is constantly being done, and distributing the versions while assuring everyone has the same version is unrealistic for our environment.
2. Both files are compacted and repaired every day.
3. We have roughly 50 tables. Most of which are small enough that Access can handle no problem. For 5 of our larger tables (currently 3 million records in one), we use SQL Server 7.0. Another thing I need to look into...our file server that holds both files is also the SQL Server.
4. We have about 75 users that access the DBs at any given time, but only about 30 are using it at once. Another thing I need to consider is my VBA coding practices. I use a lot of ADO recordsets, and each one opens up a separate connection to the backend database.

I'm aware Access is limited, but we don't have any other choice at this point. The amount of time it would take to build the app from scratch is nonexistent. If you guys can provide any other ideas...they'd be much appreciated.

Thanks!

 

[GDGarth]

I'd still put a copy of the front end on the local drive. What I do is have a shortcut that copies it over before they open the database. That way, all users have a new, fresh copy of the front end every time they start.

 

[psemianonymous]

Moving the frontend to client machines may help, but I wouldn't bet on it.

My main advice is to minimize the connection(s) to the backend.

I'd also advise that you have a hidden 'always-connected' connection to the backend, I don't know if anyone else has mentioned that yet. A quick way to do this is on startup, open a form and immediately make it hidden. This form should be bound to a (small) table on the backend.
This prevents your users from 'logging on or off' from the backend repeatedly; instead, they 'connect' on login to the database and 'disconnect' when they close the app down. At the least, it will boost speed.




Yes, I would recommend you centralize the ADO connection object so that you only create a connection once per user. I'm an Access 97 user (less ADO friendly), so I don't have details on this; I just know it exists, in a vague, handwaving fashion. It's there...somewhere.

Don't keep a constant connection to the SQL Server db, though.



I'm pretty sure, no, let me reprhase: I am 100% certain, given the information you've provided, that the corruption is due to too many users. I've provided a few tips, and they may totally fix everything. Moving the frontend to client machines may fix everything (in my experience, my corruption events went from daily to semi-weekly, before I figured out the OpLocks were the root cause).

I even wrote a FAQ about this a while back: How many (max) users can Access support? faq181-4462 --But it doesn't matter, because you already know the problem, and we're trying to help you find a solution. I've given you a few tips that allow you to stay in Access as a backend, but I recommend you upsize the backend to something else. You already have SQL Server 7 installed, right?

 

[rkasnick]

I have seen this before where the upgrade to the backend was done through the built-in wizard, and it doesn't decompile and recompile /convert code properly. To fix this, if this is the case, create a NEW database in ACCESS 2003 (or whatever version of A2K you are running) then IMPORT all objects from the old database. This of course assumes you made a backup of the old one before you started upgrading. What this does is create a fresh new compile which doesn't have any 'old stuff' stuck in the background.
Hope this helps...

Regards
Rod

 

[avarga82]

The files were brand new files with imported objects. I'm pretty sure I found out the problem...

3 computers out of the 75 clients we have using the DB were using their own laptops, with Win XP on them (as opposed to our Win2K Pro standard we have in house). The only noticeable difference with those machines were their MDAC versions were 2.8, where ours were 2.5. I yanked the laptops off the network, and what do ya know...4 days without any corruption. Not sure if it's the MDAC, but I'm 100% certain it was the inconsistent laptops.

THANK YOU EVERYONE for your help!

-Aaron