In an application that uses linked SQL Server tables, two different database engines are at work: the Office Access/Jet database engine that runs on the Office Access client and the SQL Server database engine.
The interaction of these two engines can sometimes yield results that are inferior to those obtained by using only the Jet database engine with native Office Access tables.
Although my consulting company had offered only desktop solutions up to this point, it decided to enter the client-server market.
By contrast, if you link to SQL Server data, the data remains on the SQL Server computer, and Access maintains a connection to that data.Andy Baron November 2006 Applies to: Microsoft SQL Server 2005 Summary: One way to create applications that use Microsoft Office Access for creating user interfaces and that use Microsoft SQL Server for data storage is to link Office Access tables to SQL Server tables.This is the type of application created by using the SQL Server Migration Assistant for Office Access.When we had initially set up this client's Access system, we had separated the data (i.e., tables) and the code (i.e., queries, forms, reports, macros, and modules) into two files.The data file contained about 7MB of data in 36 tables. Because we always normalize to at least the third normal form and enforce referential integrity, we had created 39 enforced relationships. (Cascading updates weren't necessary.) In every table, we had used an Auto Number field as a bookmark primary key to eliminate multiple-field primary keys, which can greatly slow insert and update performance. As Figure 1 shows, the intersection table between tbl Trainee and tbl Course is tbl Trainee Course.To add one record to a table, you must use the field list to define which fields to put the data in, and then you must supply the data itself in a value list. For example, the following statement will insert the values "1", "Kelly", and "Jill" into the Customer ID, Last Name, and First Name fields, respectively.