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.
If your department or workgroup uses Microsoft SQL Server to store data, you might encounter scenarios where you need to work with some of the SQL Server data in Microsoft Office Access 2007.
If you are not familiar with SQL Server and want to learn more, visit the Microsoft SQL Server home page.
UPDATE is especially useful when you want to change many records or when the records that you want to change are in multiple tables. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the United Kingdom: Sub Update X() Dim dbs As Database Dim qdf As Query Def ' Modify this line to include the path to Northwind ' on your computer.
Set dbs = Open Database("Northwind.mdb") ' Change values in the Reports To field to 5 for all ' employee records that currently have Reports To ' values of 2. Execute "UPDATE Employees " _ & "SET Reports To = 5 " _ & "WHERE Reports To = 2;" dbs.
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.
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.Introduction Understanding and Addressing Performance Issues Understanding and Addressing Updatability Issues Addressing Application Logic and Coding Issues Creating Unbound Office Access Applications Conclusion Microsoft Office Access supports three primary options for connecting to data stored in Microsoft SQL Server databases: This paper focuses on the challenges encountered by Office Access developers who rely on the Office Access (Jet) database engine to connect to SQL Server over ODBC.The most common way this is done is by creating linked tables in Office Access that use the SQL Server ODBC driver to connect to tables in SQL Server databases.There are essentially two methods for adding records to a table.The first is to add one record at a time; the second is to add many records at a time.I work for a small consulting company that has provided clients with Microsoft Access desktop solutions since the early 1990s.Three clients had recently outgrown their multiuser Access applications and were looking for help with upsizing to SQL Server.The SQL Server Migration Assistant (SSMA) for Office Access enables you to convert an Office Access database to this type of application by moving your Office Access data to new SQL Server tables and linking to these tables.Any forms, reports, queries, or code that previously worked with the original Office Access tables are automatically connected to the new SQL Server tables.Follow the link in the See Also section of this article.You can bring data from SQL Server objects (tables or views) into Office Access 2007 in either of two ways — by importing, or by linking.