Saving Table Designs and Data
Are you confused about saving tables and data?
Access certainly works a bit differently that the programs you may be used to (such as Word and Excel).
In Access you have to save the database file before you put anything in it.
Once you save the database file, you can create objects and enter data. Let me tell you how objects and data are saved:
- You need to save object definitions. For instance, the table definition is the stuff that you see in the Table Design view, the names of the fields, and any field properties, including the data type.
If you change anything about the table definition, you need to save the table again.
- Data is stored automatically, you do not have to click a save button. Each time you move to a new record, the data you have entered or changed is automatically saved.
Using Persisted Recordsets
While bound Recordset objects can be useful, sometimes an application needs the same Recordset data in multiple forms, and thus the data must be retrieved multiple times.
When the data usage is read-only and seldom changes, a quick and easy method for caching the data locally is by using "Persisted Recordset objects".
This also improves overall performance and reduces network traffic because the data is temporarily stored locally and is not pulled down every time the data is requested.
Persisted Recordsets can be extremely effective when read-only data is frequently accessed. In an ACCDB or MDB file, there is always the option of storing data locally in tables.
Even though local tables are easy to populate by appending data from a linked ODBC table, it is not as convenient as when the data has been retrieved via an ADO Recordset.
Moreover, storing the data in a local table cannot be done in an ADP file because all of the tables, views, and data must reside on the SQL Server.
Fortunately, the ADO Object Model allows for a simple method of saving data to a local XML file and quickly recreating it as an ADO Recordset when needed.
This often overlooked and underutilized feature of ADO can dramatically reduce network traffic to increase application performance when used correctly.
The best scenarios for using Persisted Recordsets is when data is read-only and rarely changes, and is used in multiple locations and/or is frequently accessed throughout an application.