Client & Server Scenario Access Databases

[Phonik]

This post is really aimed at someone that has experience of network databases and the problems they can pose but if you can help at all that would be great!

I work for an insurance company and we have created a database for registering complaints on. The one we currently use is a single .mdb file which can be shared by up to 60 people at any one time. This is creating a lot of problems when the queries and some VBA code are run. I therefore made a server file which is just an mdb file with data tables in and NO forms or queries. This file is stored on a shared drive on the network server. We then created a client file which contains all of the forms and queries and code. This file contains linked tables to the server so when data is entered into the form, it "Sends" it to the server mdb file. The client is installed on each of the local machines c:. The problem with this was that if we made a change to the database, we would need to reinstall the client on every single user pc. This would of took ages. I therefore made an auto-update function that checked the version number on the server and if the server number was greater than the client number, a simple .bat fiile was run which copied the updated client file from the network to the relevant users local disk.
The problem we have now is that our IT department are concered that if we make a change to the client and all the staff log in at 9am for example, it will start doing multiple copying of a file around 8mb in size to around 60 machines. They are only running on a 2meg pipe so this could cause some problems. We are not looking to change the client & server idea but does anyone know if this will have a big impact and infact if the updates for 60 machine is the equivalent or lesser of 60 people sharing the single file i mentioned earlier. If the IT dept are happy with 60 users accessing the same file at the same time, which they currently are, why are they unhappy with it updating these machine using the new method.

Would really apopreciate any thoughts anyone has..

Thanks a lot.

Gaz

 

[gemma-the-husky]

i think your scenario is a typical set up for split front end back end database.
It is normal to update the users copies of code databases in the way you suggest, and this should not cause a problem. You are presumably only changing your source code occasionally, anyway.

However, you say your network is only running on a 2Mb pipe. A normal wired LAN would work at ethernet speeds of typically 100Mb. Does this mean that some of your users are on remote sites? If so, presumably they are already experiencing performance issues, as Access works by transferring its datasets to the users home machine for processing, and they would already be affected by such a slow data rate.

The effects of a slow connection on normal running is the key issue, I think not so much an occasional update of presumably a (relatively small) front end database. (How big is the code DBS? )

 

[Phonik]

DBS? What do you mean.

The client file contains a fair amount of code and the file is about 8mb however on average I can get this down to about 6.5 after compact/repair etc.
The server file is static and nothing ever changes as it just holds all the fields that were agreed by the business.

 

[Phonik]

ps none of our users (yet) are on remote sites. we are all in the same building with the database server stored on a network (S drive.

 

[Pauldohert]

The split is the way to go, and the update =hould be done when its required (ie when they attempt ot log on to the access app) - if thats all at the same time with an access FE/BE 60 concurrent will be a major problem anyhow.

 

[The_Doc_Man]

Concur - FE/BE split (which you can search within this forum) is the way for you to control the situation.

Even a client/server model is going to give you headaches after a while, though. I know your company probably won't want to hear this, but if your real user load is 60 concurrent, you are verging on the need to upgrade the BE to something like SQL Server. Which would resolve many of the network guru frustrations and concerns.

Further, if you did the FE/BE split as a first measure to help with network load, the effort is NOT wasted by choosing to go to SQL Server later. If anything, it becomes EASIER to do.

 

[Phonik]

Ok great thanks! Also picked up your other post and appreciate your comments. At least I have some steer in using this forum now.
Thanks

Gaz