A little advice 1

[unclesvenno]

I have an Access 03 Database, it's split across 5 tables. One of which contains all the GUI and setup data which I have converted to a .mde. This sits on the users pc (front end), and the other 4 sit on the server (back end). The largest of the .mdb back end files is about 12MB and the front end .mde is about 10MB. I have about 40 potential users, of which I believe about 10 - 15 would be the most to use it at one time. Normally there may be about 5 users. Some users open and close their .mde files regularly where as some other users keep their .mde files open for the large part of the day. There seems to be a 20 - 30second delay in opening the .mde which is the reason some leave the application open for so long.

My questions are:
- What sort of impact does leaving the .mde open for such long periods of time have?
- When the GUI is loading how can I create a "loading bar" to add to my splash screen to indicate the db is loading?
- Are there any specific pitfalls anyone can see for such a setup?

Thanks a lot,
Uncle Svenno

 

[Trendsetter]

As far as I know there is no impact in leaving open the mde whilst not being used.. this does not add to the concurrency as that is the number 'using' the application at any one time.

Is the application connecting to the data tables during the 20-30 second period???

You have a classic Access database setup - watch out for the number of users working at any one time as Access is very quirky when it comes to high numbers and un-compacted backends.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[unclesvenno]

Hi Trendsetter,

Thanks for your time. Yes it is connecting to data tables, the first screen to be loaded after the splash screen shows information resulting from a couple of queries on a couple of tables. The tables being queried have approx 25,000 and 5,000 records respectively.

If I wanted to shorten the time it takes to load would it perhaps mean working on smaller tables?

Also when you say watch the number of users, am I currently at the size where I need to consider something else? What do you think is an acceptable number of users to maintain stability and data integrity?

Thanks again,
Uncle Svenno

 

[Trendsetter]

1) What you could consider doing is loading a different form ( as the startup form) with none or very little data.
Something like a logon form that only needs the list of users from a single staff table.
You could load the bulk data in the background whilst the user is doing his thing with the logon form.
2) Concurrency becomes an issue with high usage of larger database tables - the problem manifests itself with slowness of requerying and form loading - but this is also dependent on the network setup - the allocation of memeory etc.. etc...
If you feel the application and its data are going to grow and the usage load is going to increase as well then look at SQL Server. There is a new version coming out soon that overcomes the work throttling problems associated with MSDE - have a look on the Microsoft site and search under SQL Express.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[unclesvenno]

Thank you very much, I'll be sure to look into it. I am pushing to upgrade to a more stable system which will allow for db schema growth and development and which will handle more concurrent users.

UncleSvenno