Database Sharing & Performance

[Danielbryanuk]

Hi there,

I have a question regarding the performance of a database I am designing. It will be roughly 40-80 Mb in size, have approx 40 users at any time over a WAN, stored on shared network drive, mostly running macros and queries (code limited to database navigation.)

From previous experience I know the performance will not be satisfactory, so the database will need to be split. Also, I understand that to ensure the design cannot be changed, and to increase performance, the mdb can be converted to a mde.


My questions therefore are threefold:

a) Can you split an mde database?

b) Will performance improve if, rather than all users sharing the FE via a link, the FE was physically copied to each user's hard drive (or available on CD)?

c) If (b) is possible, can all the FEs be replicated so I do not have to resend FEs, and how does this work?

I know these are probably quite wide ranging questions, however any advice you can offer or sources of information you can point me towards would be much appreciated.

Thanks
Dan

 

[Ed2020]

Dan,

To answer your questions in order:

a) Yes, an MDE file can be split. Your frontend should contain all forms, reports, macros, modules and (optionally) reference data tables. Your backend should contain the remainder of the tables.

b) If the system is designed correctly yes, performance should improve with the frontend stored on the local drive rather than on a network drive.

c) I believe there is a tool available for auto-updating frontends installed on users' local drives. Hopefully somebody will be able to post a URL for it - I can't remember where I saw it! I usually have a table in the frontend and a table in the backend for storing current version number. This is checked on startup to ensure the user is running the latest frontend version. You could create a simple batch file for copying the master frontend to the local drive so the users can easily update as and when required.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Dan,

I also meant to say:

Your file size is not the issue in terms of performance. The number of users, what they are doing, and network bandwidth will be what causes any performance issues you may encounter.

Network bandwidth will likely be the most difficult one to work around. Most other issues should be addressible in the system design. I've had fairly complex systems with well over 40 users with no performance problems at all.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Danielbryanuk]

Thanks very much Ed, very useful stuff - one question though, what is a batch file and how do they work?

Also, just something to bear in mind; I am not an IT person at work, (finance person actually with a working knowledge of MS Access - dangerous some might say!).

Therefore are there any issues that my security concious IT colleagues are likely to raise as regards installing databases on users machines and updating over the WAN? The reason I ask is that I have only recently been able to send links to my databases via email (due to new company security policy), so am concerned that I may be heading down a one way street so to speak?

Thanks for your assistance

 

[Ed2020]

Dan,

A batch file is a text file (with a file extension of .bat) containing one or more commands to be executed by Windows' command line shell. You could set one up to copy the necessary files from the network drive to the local drive. Users could simply double-click the .bat file to run it.

There is some information here:

http://www.computerhope.com/batch.htm

Here is an example of one we use on my team:

md "c:\road\"
xcopy "Y:\Shared\ServerName\Directory\Road.mdb" "C:\Road\Road.mdb" /Y
"C:\Road\Road.mdb"

This first line creates the local directory, the second line copies all files from the networked directory into the local directory, the third line launches the system.

Ed Metcalfe.



Please do not feed the trolls.....