Q. Here is a list of common issues and solutions:
The system does not allow multi-user access
- All Access database applications must be split between a front-end (queries, forms, reports, logic, etc.) and a back-end (data only). This is the gold standard for system deployment. Any other design is flawed and will result in program crashes and data corruptions.
The database application is crashing, and/or the back-end database is getting corrupted
- The most common cause of database corruptions is when multiple users open the same copy of the database file at the same time. For example, the database file resides on a shared, network folder, and two or more users open the same file. Imagine editing the same Word document stored on the network at the same time. You would be overwriting each other’s work. Same goes for a database. The front-end (see above) should be stored on the local (C:\) drive and linked to the back-end stored on the network. With this configuration, data entered by one user is immediately available to all other users. It also facilitates nightly backups. If you or your IT team insists on running the front-end over the network, you should have separate copies of the front-end for each user.
The database file is too big, rapidly approaching the 2 Gb limit
- Once the database file exceeds 2 Gb, it will stop functioning. You may just have more data than Access can support. If the data is not needed daily, we could archive historical data to a separate database to reduce the size. If the data is needed daily, we may need to migrate to SQL. If you’ve been adding and deleting large volumes of data, you may just need to run a simple Compact and Repair. Record deletes flag the records for deletion, but it doesn’t really remove the records, causing the database to be larger in size than is really needed to store the data. Compact and Repair permanently removes those deleted records and reduces the file size accordingly.
System response time is slow
- It could be a function of your tables not being properly indexed, queries not being properly written, inefficient code, or more data than Access can handle. If network response times are fine for all other files, there’s a flaw within the application that needs to be addressed. Your data may need to be migrated to SQL to harness its processing power.
The application won’t launch
- Will the Visual Basic code compile? Are there undeclared variables? Are there missing references? Corrective action may be a quick fix or a time-consuming, complicated process, but these are the first items we would review to arrive at potential solutions.