Question regarding Access performance (1 Viewer)

Daemetius

New member
Local time
Today, 08:41
Joined
Sep 18, 2017
Messages
5
I have this scenario:

At my place, Access is having performance issues, specifically when users are adding records through Forms. There are total of 14 tables and some of these tables have over 130k records with each one having a sub record (collapsables) which makes for a lot more.

There are various users adding new information to these tables and of course Access is having performance problems since these users access the same file through a shared folder over the network. By performance problems I mean that it takes 5-10 minutes to complete adding a record to the DB.

I was thinking that perhaps Splitting the DB would help, but I don't know if that is enough considering the huge amount of records stored in the file and the ones to come in the future.

Is there anything else besides DB splitting that should be taken into consideration?

I had one other crazy idea which was to export the tables onto another place (something like a copy for archival purposes) and then cut off the information 1 year from now and delete any data older than 1 year to lighten the burden Access is having, but this solution seems more likely prone to disaster.

Any help is appreciated!
 

Minty

AWF VIP
Local time
Today, 11:41
Joined
Jul 26, 2013
Messages
10,354
You definitely should split the database and give each user their own copy of the front end, I'm amazed you haven't suffered any corruption.

How big is your database - there is a 2Gb limit for a single access database file. Have you taken a backup and done a compact and repair recently?

How are your users connecting to the network - directly or remotely via VPN ? Access is not very good over a WAN connection.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,970
Once you split the database (do not pass GO, do not collect $200 - do it immediately!), you will need to distribute the FE to each user. There are simple and there are complicated ways to do this but it still needs to be done. Users should never share the same FE.

Once the db is split and tested, remove the local versions of the tables from the FE and then compact it again to reduce its size.

130,000 is larger than most tables but by no means a problem for Access. I have databases where some tables have 5 million rows and Access has no problem. The key is proper relationships with Referential Integrity enforced and appropriate indexes to facilitate easy data retrieval. You also need to compact the BE on a regular schedule and compact the FE before you distribute a new version.
 

static

Registered User.
Local time
Today, 11:41
Joined
Nov 2, 2015
Messages
823
5-10 minutes to add a record means a badly designed database and a slow network.

There is nothing you can do in Access to speed up the network.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:41
Joined
Feb 28, 2001
Messages
26,996
In a non-split database, your slowdown is almost certainly due to lock arbitration. By splitting the DB cleanly into a data-only back end and an everything else front end, you make the locks on the "everything else" become private i.e. non-shared. Then the only problem will be the load for your users sharing the BE data. If the queries and forms are set for "Optimistic Locking" then you minimize your exposure to lock interference but still have some protection. You could chose No Locks but I'm a little bit leery of that when dealing with parent/child updating. For single-table updates, not so dangerous.
 

Minty

AWF VIP
Local time
Today, 11:41
Joined
Jul 26, 2013
Messages
10,354
Overall Access is great for beginners, as I was reading here but that also means that because of how simple it is, it's not the most powerful and it will lead to performance issues like the this one.

That article is really badly written. It contradicts itself on almost every point it makes, more than once.

Access is by far the most powerful of the systems it compares, and by far the most supported. It will also link to almost all the same backend database files that the competitors do.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:41
Joined
Feb 28, 2001
Messages
26,996
The referenced article is also presenting more of an opinion piece than a technical one, and the author's big-machine bias shows badly. For example, he didn't mention one of the most powerful aspects of Access - using a heterogeneous data source. You can have an Access FE and another SQL flavor for a BE, in which case you can exploit "the best of both worlds" AND you can get there by starting from "pure" Access and then up-converting the BE when you need to.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 19, 2002
Messages
42,970
Lots of inaccuracies in that article. I wouldn't recommend it to anyone for anything.
 

static

Registered User.
Local time
Today, 11:41
Joined
Nov 2, 2015
Messages
823
I guess it isn't saying those other programs are better, it's just saying if you aren't on Windows or want something free, alternatives are available.

After asking 'Why are MS Access alternatives needed?' it gives a wall of text describing what Access is with 'However, it is only available for Windows and comes at a cost.' lost in the middle of it.
 

Users who are viewing this thread

Top Bottom