The best approach to creating a client/server application in Microsoft Access is to create the tables that will contain the data on the server itself. However, you can also create the tables in your local Microsoft Access database, export them to a server and, as a final step, link the remote tables to your local database. This second technique may be more convenient—especially if you are still in the process of designing your database—but moving the data to the server can cause some complications you’ll want to avoid. If you do decide to start with Microsoft Access tables and move them to a server later, keep the following issues in mind.
Indexes When you export a table to a server, no remote indexes are created. If you need to update the table’s data, create a unique index on it by using a pass-through query or a separate server-based tool before you link the exported table. You’ll probably want to define other indexes as well to improve query performance.
AutoNumber Fields Because most servers don’t provide the automatic-numbering functionality of an AutoNumber field, Microsoft Access exports AutoNumber fields as Long fields. To mimic an AutoNumber field, you either create an insert trigger on the server that provides the next sequential value or you write an event procedure in your Microsoft Access application to provide this functionality.
Default Values Default field values aren’t exported with a table, but since most servers support default values, you can create them directly for the fields in an exported table. However, Microsoft Access won’t automatically display the values in a new record in a form or datasheet; they are displayed only after you save the record without entering a value in the field.
Validation Rules You can use a remote table that enforces your validation rules, or you can define rules for your form. Using form-level rules is easier, but not as reliable because invalid entries are rejected only by the form, not by the server itself. However, if you’re using a server-based trigger, be aware that many Visual Basic functions may have no equivalent on your server (such as the DatePart and Format functions).
Relationships Microsoft Access doesn’t recognize relationships that may be defined on your server. If you attempt a data operation that violates referential integrity on the server, an ODBC error occurs. You can mimic relationships defined on your server by using server-based triggers or local form-level event procedures. You can mimic relationships with enforced referential integrity by prohibiting operations that violate the relationship, and you can mimic cascading relationships by manually synchronizing key values when you update related tables.
Security Any security you establish in your Microsoft Access database doesn’t affect security on the server. Microsoft Access isn’t aware of server security, although it can’t violate that security. For example, if you’re editing a remote table for which you don’t have permission to insert data, Microsoft Access doesn’t restrict you from typing a new record. However, when you try to save the new record, the server returns an error message and the record isn’t inserted. You can minimize these discrepancies by synchronizing local and remote user permissions and passwords. This means you only log on once, because Microsoft Access automatically attempts to log on to the server by using your local user permission and password, and prompts you only if the attempt to log on fails.
Table and Field Names With Microsoft Access, you can use almost any character in a table or field name, but many servers have more restrictive naming conventions. Therefore, when you export a table, each nonalphanumeric character in every name is converted to an underscore (_). As a result, queries, forms, reports, and other Microsoft Access objects in your application may no longer work. To solve this problem, you can create a name-mapping query (this process is also called aliasing). For example, suppose you have a local table called Order Details with three fields named Product ID, Quantity, and Price/Unit. After exporting and linking the table, you could map the names by following these steps:
SELECT Product_ID AS [Product ID], Quantity, Price_Unit AS [Price/Unit] FROM Order_Details
All existing queries, forms, and other objects that use the local table can continue to work with the remote table without imposing any extra overhead or limiting functionality in any way.
Properties You can’t change most field properties on linked tables. Even for those that you can change (such as the InputMask, Format, and Caption properties), you must manually copy their property settings from the local table to the linked remote table.
Visual Basic Code If you export and then link a table, you may need to modify modules containing code that uses the table. For example, you can’t use the Index property or the Seek method on remote tables. Additionally, data-definition methods and properties—that is, Data Access Objects (DAO) methods and properties that change table structure—are usually invalid on remote tables; for example, you can’t add a field to the Fields collection of a linked remote table.