Want to read more gem tips? See the entire list of gem tips from expert Access developers to help you build better Microsoft Access database applications with less effort, while avoiding many of the headaches.
(We've moved a few tips, so if you have an old link, please update your bookmark.)
Split the database into a front end and back end when you're finished developing the application -- By D.C. Conlin
Are you building a multiuser database for your organization? When you are finished developing your database, split the production database into a front end and a back end. The back end will contain the tables, as well as the relationships you have defined for those tables in order to maintain referential integrity. The front end of your database will contain the application objects: queries, forms, reports, data access pages, macros, modules and temporary tables. Lookup tables that rarely change are also good candidates for the front end. The front end will also include the links to the tables contained in the back end database.
The production back end will reside on the file server, while the production front end will be distributed to individual users to reside on each workstation. When you set up your database this way, you provide your organization with the following:
Easier, faster maintainability
Less likelihood of database corruption
Improved performance
Noninterference from other users
Easier, faster scalability
Easier, faster maintainability -- When you update the database with new features or bug fixes, only the front end will be affected, so keeping the data separated in another file makes updating less time-consuming. You should automate the distribution of new versions of your front end so that this job remains less time-consuming.
Less likelihood of database corruption -- A common cause of Access database corruption is dropped network connections while writing to the database file. When multiple users open a single Access database file on the file server, the chance that a network connection will be lost while one of them is writing data to the MDB file is much higher than when each of these users opens a copy of the front end Access database file on his workstation, which links to tables located in the back end Access database file on the file server.
Improved performance -- Placing only the shared database objects (tables and relationships) on the file server improves performance by cutting down a significant amount of network traffic. Only the data moves back and forth across the network wire, not the application objects. Opening each application object from the local workstation is significantly faster than opening the application object in a database on the file server and retrieving it across the network to the local workstation.
Noninterference from other users -- When temporary tables reside in the front end on the user's workstation, no other users can access or interfere with the records in these tables. Individual users can customize some of the settings in Access without bothering other users' settings. Also, when a user applies a filter to a form and later closes the form, Access will save the filter in the form. If other users are sharing this form from a shared database on the file server, then when they later close the form, Access will warn each of them that the form has been changed since the form was opened. Access will prompt them for whether they would like to disregard the changes made by another user, or whether they would like to save the current form under a new name. This is just plain confusing to most folks and completely avoidable by using the split database methodology.
Easier, faster scalability -- Once the tables have been linked, it's fairly easy to change those links to an ODBC data source for a real client/server database system. The issues regarding linked tables have already been resolved in the database, so they need not slow down the migration. (Warning: migrating to a client/server database is usually not a simple task. It's merely easier than it might have been if the database weren't already split.)
Beware splitting a secure database with the wizard -- The Database Splitter Wizard is commonly used to quickly and easily split the database, but if it's used on a secure database, the back end database file will be left unsecured. Please see the following Web page for the link to the tip for how to avoid unsecuring the back end when splitting the database.
Compact and repair the database regularly -- By D.C. Conlin
Make sure that you compact and repair the database regularly, especially if the database application experiences frequent record updates, deletions and insertions. Not only will this keep the size of the database file down to the minimum -- which will help speed up database operations and network communications -- it performs database housekeeping, too, which is of even greater benefit to the stability of your data. But before you compact the database, make sure that you make a backup of the file, just in case something goes wrong with the compaction. And when you create the backup, make sure that all Jet clients are disconnected from the database file. Failure to do so could result in the data being stored in an inconsistent state in a file that you may desperately need during disaster recovery.
Jet compact process -- Jet compacts a database to reorganize the content within the file so that each 4 KB "page" (2KB for Access 95/97) of space allotted for data, tables, or indexes is located in a contiguous area. Jet recovers the space from records marked as deleted and rewrites the records in each table in primary key order, like a clustered index. (Note that this physical arrangement of the records doesn't necessarily stay in this order after compaction, so you won't find anyone claiming that Jet has "clustered" indexes.) These steps will improve the speed of the read/write process. Jet also updates the table statistics during compaction. This includes identifying the number of records in each table, which will allow Jet to use the most optimal method to scan for records, either by using the indexes or by using a full table scan when there are few records. After compaction, run each stored query so that Jet re-optimizes it using these updated table statistics, which can improve query performance.
Access objects compact process -- In Access 2000 and later versions, all Access objects, such as forms, reports, and modules, are stored in a single, monolithic record in a system table called MSysAccessObjects, instead of a separate record for each object as was used in Access 97 and earlier versions. (That's why it takes so much longer to save a simple change on a Form when there are lots of forms, reports, macros, and modules in the database. All of these application objects are being saved in one record, not just the one object you made the change to.) Since this is a single record, Jet compaction doesn't reorganize the data within the record, even though over time the monolithic record can become fragmented due to changes, deletions and insertions.
Microsoft realized this, so they modified the compaction process executed from the Access menu and the "Compact on Close" database option to defragment the monolithic record during compaction as well. However, programmatically compacting the database still had no effect on the monolithic record, so Microsoft added the CompactRepair( ) method in Access 2002. If you are still using Access 2000 and want to programmatically compact the database, then using the command-line switch /repair will actually compact the Access objects. ("Repair" is no longer a separate function from "compact," and Microsoft didn't want to eliminate this command-line switch, so /repair actually compacts the Access objects in the monolithic record in Access 2000.)
Repair process -- The repair process is combined with the compaction process in Access 2000, 2002, and 2003. These two processes were also combined after Access 97 was released, apparently when either Jet 3.51 SP-2 or SP-3 was installed. (Available documentation is sketchy on which service pack contained the improved utility. Earlier releases of Access 97, including SR-2b, didn't include this combined compact/repair utility.) The repair process accomplishes a number of tasks to help keep your data safe and accessible. The repair process:
Cleans up incomplete transactions
Compares data in system tables with data in actual tables, queries and indexes and repairs the mistakes
Repairs very simple data structure boo-boos, such as lost pointers to multi-page records (which isn't always successful and is why "repair" doesn't always work to save a corrupted Access database)
Replaces missing information about a VBA project's structure
Replaces missing information needed to open a form, report and module
Repairs simple object structure mistakes in forms, reports, and modules
Access 2000 and later versions won't repair the database unless it's necessary. Access 97 and earlier versions have separate compact and repair methods, and a manual "repair" shouldn't be executed in these versions unless necessary, because it could accidently cause ill effects on the data stored in the file. You will receive a prompt to repair the database if Jet deems it necessary, and if the database is acting really, really bizarre, then you should probably repair it, so there's no need to "guess" if a repair is necessary.
Compacting a split database -- When compacting a split database, opening the front end (the file with the database application objects, like queries, forms, reports, etc.) and then compacting will only affect the objects in the front end file, not any of the database objects, such as the linked tables, that are actually contained in the back end of the database. Therefore, you will also need to open the back end database file to compact/repair the tables, as well.
Don't compact from across a network -- Compaction should never be done from one computer to another across a network. The most prevalent cause of Access database corruptions is interruptions (such as a dropped network connection) while writing the data to disk. Even if the Access database file is stored on a computer that does not have any version of Access installed, you should not attempt to compact it from across the network. That doesn't mean that it's impossible to compact the database in this situation, because the database can certainly be compacted while the Windows System Administrator is logged into the file server, itself. (System Administrators are very protective of their servers, so you probably won't have the opportunity to log into the Windows server for compacting your databases.)
Compacting without Access -- There are two methods available for compacting a database without using Access: the JetComp utility (available for free download from Microsoft's online support Web site) or a Windows script that can be run on the file server where the Access database needs to be compacted. Since the file server (Windows 2000 and up) should already have both Windows Script and MDAC 2.1 (or higher) installed, copy and paste the following script into Notepad, then alter the database path and file name in this VB script to match your own database path and file name. Save the file as "CompactDB.vbs" on the file server. Now, when the need arises to compact the Access database, the Windows System Administrator can run this script while logged into the server.
' This script can be used to compact an Access ' database on a Windows 2000 or 2003 server that ' does not have Access installed. This script can ' compact any Microsoft Access database version ' 2000, 2002, or 2003, unless it has a database ' password.
' Windows Script and MDAC 2.1 or higher must be ' installed. '====================================
Option Explicit
Call getFileInfo
Public Sub getFileInfo( )
On Error Resume Next
Dim sJetCnxn Dim sDir Dim sDB Dim src Dim dest Dim fso Dim ErrDesc
sJetCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" sDir = "C:\Data\" ' Set directory path here. sDB = "MyDB.mdb" ' Set database file name here.
Set fso = CreateObject("Scripting.FileSystemObject")
If (Not (fso.FileExists(sDir & sDB))) Then MsgBox "Database not found:" & vbCrLf & _ sDir & sDB, vbExclamation, _ "Compaction Failure!" Else If (fso.FileExists(sDir & "Temp" & sDB)) Then MsgBox "Previous compaction incomplete." & _ vbCrLf & "Deleting old database.", _ vbInformation, "Compacting . . ." fso.DeleteFile (sDir & "Temp" & sDB) End If
ErrDesc = compactDB(src, dest)
'----------------------------------------------------- ' Determine whether compaction was successful. ' When error description = 0, then it's successful. ' Otherwise, textual error description was a failure ' that needs to be reported to the user. '-----------------------------------------------------
