What are the limitations of MSAccess over a network setting

[fishhead]

Hi I have an application i created for multiple users to use over a network and have run into a serious speed issue when access data (read/write).
the front end application is being used on the workstations via MSRuntime and the backend tables are all ACCDB files (which i believe is the most current).

I used to have 10 users running ( a few years a go mind you) without any impact to speed - were were all flying!
now If there is only 1 user on the network the speed is great but as soon as 2 or more users log in the lag time is terrible (front end is always showing "calculating" in the lower left hand corner of the screen).

I thought perhaps the network was slow and have just recently upgraded everything with a 1 GB 48 port switch and a high performance NAS unit. Basically i have tons of performance built into the network now. I feel i can reasonably check off network for issues as the cause of the slow running speed.

I'm wondering now if MSAccess has a max user over the network problem? How many users is considered reasonable?
The backend tables are split between 5 .accdb files for a total of approx 100mb of backend data (each table no larger than 30mb)

The last thing i can think of (or actually wish not to think of - ha) is the program itself.... If i have to start looking for areas in the application causing the slowness i really don't know where to start. Any suggestions?



appreciate any incite you can offer in my nightmare....

tks,

[Micron]

the db is properly split; i.e. each user has their own fe copy? I presume network performance in other applications such as Word, Excel, retrieving files etc. is all ok?
You could try compact & repair on the back ends but before going ahead with a bunch of suggestions, I think more info on the method of db splitting is the starting point.

[fishhead]

the db is properly split. the FE is on each workstation - each user has their own copy. The Word, Excel docs all retrieve off the network without any issue. All the BE files are compacted nightly too.

[Micron]

You mentioned that years ago, no issues. What are the versions of the front and back ends? Do the fe's get compacted occasionally and how big do they get?
There used to be explicit limitations that I think were generally considered to be overly generous, but I do believe that from Jet 3 on, there are no limits. In the case of proper splitting, the limitations would be on the be, not the fe.

Also wondering if the performance took a hit gradually or practically over night. If gradual, you might have reached a point where the efficiency of the queries or calculations for controls and such is getting bogged down due to lack of or poorly designed indexes on what has become a much larger set of records. What about the original sizes of the be files compare to now?

[orange]

The backend tables are split between 5 .accdb files for a total of approx 100mb of backend data (each table no larger than 30mb)

An accdb file is a database which would contain your Tables.

Please tell us more about the 5 accdb files that make up the backend.

[fishhead]

there are 5 accdb BE dbases in total

accdb 1 = 35 mb and only has 1 table in it with 19,000 records in it. I just looked at it and it appears there is no PRIMARY KEY currently. This is surprising to me as i'm certain the PRIMARY KEY was set to a AUTONUMBER field i added. This table was originally created many years ago and originally was a .mdb file I"ve recently changed the BE databases to accdb to keep them current. Is it possible the change removed some of my primary keys? Also would this cause the slow down? Is there a maximum amount of records a table can have and still supply decent speed over multiple users? This table has 19000 records and i really don't want to delete any. (the reason this accdb only has one table is because of the size of this table) the other BE accdb files have multiple tables in them). Checking my other tables (in other ACCDB files) have Primary Keys - i usually make sure each table i create has one. So it does't appear to be something deleted in the transfer from mdb to accdb.

accdb 2 = 29mb and has approx 70 tables in total
accdb 3 = 36mb and has approx 70 tables in total
accdb 4 = 6mb and has 12 tables in total
accdb 5 = 1mb and has 10 tables in total

I'm wondering if the 19,000 records in a single table that doesn't have a primary key is the cause of the speed problem? (again 1 user is super fast 2 or 3 users drop the speed considerably)

[fishhead]

I just noticed that the table with 19,000 records doesn't have the autonumber field set as indexed either. the point of this field is to create a unique numeric record (autonumber) that the users would search by from the FE. is the speed issue a result of the autonumber field not set as a primary key and also not indexed? i really don't know why this has changed - can't see myself creating this table without them.

Another thought i just had is the 5 accdb files are being backed up every 10 min. the reason for the frequency being so high is if i get a corrupt records or accdb i can pull the most recent one and at most only lose about 10-20 mins of user data updates. We are constantly updated and adding new records to the BE tables and every once in a while we get a corrupt records that reeks havoc for us.Backing up nightly would not provide enough of a backup plan for us - 24 hrs of data changes over 4 users would be next to impossible to remember and redo . could this be contributing to my speed issue? (I've been backing up like this for years - before the speed problem - without any issues)

[orange]

Every table in a relational database should have a Primary key.
What is the business and why 5 back end databases? What in general terms is the business/processing that requires backup every 10 minutes?
Have you tracked down the cause of the corrupt records?
You may have some poorly designed tables and/or queries or routines. If you have sequential reads against 19,000 records that could be a factor if that is part of major processing.

You mention backups, but do you do any compact and repair in a regular maintenance cycle?
It's also possible that you could archive some of the data, but that depends on your business needs.

I am not a SQL server person, but your set up would indicate to me that it might be more appropriate for your back end. It would remove some of the potential for network corruption that Access suffers. Others, more familiar with it may have some ideas and advice. You seem to have some almost online/real time processing, so transactions might also be a key factor to overall design. Transactions on SQL server would remove most of the manual intervention after failure.

Some links to review:
Database split and performance
Linked table performance

Good luck with your project.

[Micron]

You didn't answer as to whether or not the slowdown was incremental or sudden.
I've never heard of a PK field being lost because of a conversion. The lack of a pk field should not cause the problem but a lack of indexes can. So can keeping attachments in any tables. So can too many calculations in forms and queries if a lot of them are being opened simultaneously. 19000 records isn't a lot. IIRC, the max table size is 2Gb, but then so is the whole db, including overhead. Size isn't just about records, but the gb size you report doesn't seem real onerous either.

How is the backup performed? Simply copying a file in use should have no impact. Frequent corruption smells like there's an underlying issue which could be a lot of things. Fixing that might remove the need for so many backups, which depending on how that's being done, might speed things up. However, potential issues that I've mentioned in this post should be considered. BTW, it sounds like you are not using autonumber fields correctly if you mean those generated by Access.

the point of this field is to create a unique numeric record (autonumber) that the users would search by from the FE.

You should NOT be attempting to use them as meaningful data. If you are, suggest a peek here or similar that you find
http://www.utteraccess.com/wiki/Autonumbers

[fishhead]

I split the BE into 5 to lessen the accdb file sizes.
each evening they are all compacted via a bat tile o created to run at midnight .

we are a logistics company and each shipment is constantly getting updated with new data etc.

the 10 min backups were to provide a constant supply of somewhat recent data to pull from in the event of a bad debase or record etc

[orange]

How big is this database operation in terms of activities per minute or hour?
Do you have a DBA group or are you it?

Where are the users of the database located --local??
Can you give us any more detail on the contents/structure of your 5 accdbs?

Do you have any facility (development/test area) to do some analysis of your various processes/queries/reports...?
Do you have any network techs to review network performance?
Basically is it network or database--then what specifically..?

[isladogs]

All of your BE files are small and 19000 records isn't a lot.
Not having an index and a PK is a serious mistake.
Indexing dramatically speeds up searches. A PK is required for update queries.

Regular backups are a very good idea but every 10 minutes is a bit extreme.
As Access is single thread, it can't do anything else whilst a backup is being run.
You really need to find the cause of your data corruption that you indicate is occurring regularly

[CJ_London]

to add to the other comments, your performance can suffer if queries utilise tables in multiple databases