Access software size and users

[Matti_Haapaniemi]

Hi

We have in business and producion Access software. Simultaously users is max 30 and size about 200 to 400 mb. But system crash sometimes ( 2 times a day) and make backup copy. Is there way to test what

happened when system crash? Before crash system slows and size is expanding,.

Matti Haapaniemi

[#2]

Hi Matti,

Some questions about your system:

1. What files does your Access application consist of?
2. Is the application split into frontend and backend file?
3. Does each user have his own frontend file?
4. Which file exactly is inflating?
5. Are there any error messages before Access crashes?

Servus
Karl
*********
http://AccessDevCon.com
https://www.donkarl.com

[#3]

Hi

1. One file in server Access 2019 contains 10 tables
One file in workstations contains 200 queries, 100 forms, 30 reports , 120 macros
2. Backend in server and frontend in workstations
3. One frontend
4, File in server is inflating (maybe one of reports is doing this)
5. No error. Just slowing and stop system. New backup file is coming to server.

Matti

[#4]

@Matti_Haapaniemi 

I hope you mean one frontend for each user on their own workstation.

Are you using a persistent connection to the BE file? If not, I would recommend it

Does the frontend contain any tables e.g. 'temp' tables that are being written to on a regular basis?

Is Compact On close enabled in the FE? If so, disable it

One way you could test what happens before a crash is to test for available connections.

See Available Connections - Mendip Data Systems

If the number of available connections drops significantly, the FE will slow dramatically and then crash. If that is the cause, you can take remedial action to close objects when not in use

[#5]

@isladogs 

Each user has own frontend.

We have localnetwork 1 gb to server.

All tables are in server not frontend.

CompactOn Close disabled.

But available connections is intresting. we have lot of queries and reports and users

keep all open. 

I try to test but error comes (form and report is copies9

[#6]

That sounds like a missing reference issue.
DAO is part of a standard reference library. For ACCDB files, you need the Microsoft Office xx.0 Access Database engine object library. For older MDB files, use the Microsoft DAO 3.6 Library.

Once that is installed the code should then run successfully.
You should soon notice why its a Good idea to close forms and reports when not in use as well as setting recorders to nothing when you finish using them.

[#7]

@isladogs 

Now working and when I test available connections are about 230. Thats not a problem.
What you mean setting recorders to nothing ?
Our system consist two main tables and users update these sometimes 30 users simultaneously,
We use same forms for viewing and editing. All is fine when users amount is about 10.

[#8]

Sorry that was a typo caused by autocorrect
It should have been...set recordsets to nothing.

It may be that you have hit the limit of what Access can handle in terms of users working simultaneously on the same table. Perhaps it would be best to consider moving the BE to SQL Server.

Almost 20 years ago, we added a feature to one of my databases for schools for teachers to record tutor group / class attendance. At that time the BE was in Access. The feature worked well in testing but in practice the database dramatically slowed to a crawl as anything up to 80 users simultaneously tried entering data to the same tables. It also led to crashes and corruption. This was seriously bad news both for users and for the program admins. The feature was withdrawn whilst we moved the BE to SQL Server. It took a while to convert but we never looked back. After that, no more slowdowns or crashes and no more corruption.

[#9]

Ok. I test sql server but is Access good Frontend program ? What is best user interface program ?

[#10]

Yes. Access makes an excellent FE for use with a SQL Server BE. Try the Express version. Its free and has a 10GB file size limit

[#11]

One question more.
Is it enough only making ssma to copy tables to sql server ?
We use hyperlink in our system and that not working in SQL.
Is there anything else to know?