Corrupt Multiuser Database

[MissyEd]

Hi All

I have a db (access 97) being used as a backend by about 40 - 50 users. Its over 120MB compressed and is only accessed directly by administrators to make changes. It sits on a server and up till a week ago the network was netbeui, it is now TCP/IP.

Im sure this is not related, but since we went to TCP/IP, the backend database corrupts on a regular basis - at least twice a day. The front end sits on each users' machine and uses linked tables to access the backend.

I've considered making compiled versions of the databases, but Im unsure wether to compile both front and backends. Any suggestions on this or alternative reasons for the sudden crashes ?

Thanks in adv.

Missy Ed
Looking to exchange ideas and tips on VB and MS Access development as well as office 97 development. Drop me a line: msedbbw@hotmail.com

 

[RickSpr]

By "compiled versions" do you mean MDE files? That wouldn't help on the front ends, and would be pointless on the back end, which I imagine contains no code.

I don't know why switching form NetBEUI to TCP/IP would make it more likely for the back end to become corrupted. Is the server going down while the administrators are working in the database? Does Repair Database seem to be fixing the problems? Rick Sprague

 

[MissyEd]

Hi RickSpr

Yes, repairing then compacting the database works OK, but I need to prevent the corruption as its making us lose time and money.

From annecdotal evidence, it seems networks can affect databases, here is some information I got off deja news. I'll be discussing the points with my boss to see if we can come up with a solution.

As I dont have much experience in this area, I would be grateful if anyone can come up with some verification of the below or some suggestions on how to proceed.

*** An archived message from DejaNews ***
Peter,

I am presently running an Access97 app for 20+ users over a network that has some 500+ users talking daily. It is important that the network be setup correctly and that it have a server (or servers) which have the juice to handle the job. We have old servers supporting the MS Office software and needed to purchase a new server for our database to bring us up to speed, literally. The older servers were so slow we would corrupt the database in half a day.....not anymore! In our case, though, the NT Dept. tried to save money at the users expense and are now being forced to replace ALL of the older servers.....it pays to have the right tools for the job.

Also, I think it is fair to suggest that you develop with a FRONT- END/BACK-END structure. The GUI as the FRONT-END and the tables as the BACK_END. Most texts discuss this approach and it provides some great alternatives from loading the FRONT-END on each machine and leaving the BACK-END in a shared folder to having both located in the shared folder. With a BACK-END approach, you can develop various interfaces to the data - whether multiple Access FRONT-ENDS or even a VB or Web FRONT-END. All while linked to the same data. Upsizing to SQL Server is even a viable option while maintaining the core FRONT-END.

True, Access is not a true client/server, but most users have no clue as how an application is brought to them, they are concerned with whether it works or not and for how long before a problem occurs.

I've had to deal with a NT Dept. which claimed Access was not a multi- user application and that the problems we were experiencing over our network were caused by Access.......well, they were flat-out wrong and were proven wrong and with a little cooperation we got back to solving the problem and getting on with the work. The gentlemen that have already spoken were instrumental in reinforcing my belief that Access - when properly supported - is a very successful "client/file-server" tool.

Access is less overhead (if already a part of the desktop environment) and much quicker to develop than using an all VB/SQL (or other)
approach.

But, then again, this is just my opinion and I can only hope it helps!!

--
Thank You,

David
*** End ***

Missy Ed
Looking to exchange ideas and tips on VB and MS Access development as well as office 97 development. Drop me a line: msedbbw@hotmail.com