5.5 Access Untold legions enjoy the power of Microsoft's Access database. For you select speed freaks and efficiency experts, here are some hints that make Access even better. 5.5.1 The Express Route to Creating Tables Creating a new Access table is about as much fun as watching C-SPAN reruns of federal hearings about the European corn borer. So here's a nifty way around the problem: Recycle existing tables instead of creating new ones from scratch. The process is pretty simple: -
Decide what table you want to make a copy of . Choose a table that has the same basic structure of the table that you want to create. -
Open the database that contains a table you want to copy, and then in the Database window, look under the Objects pane and click Tables . When you do so, all the tables in your database appear in a list in the right-hand pane. -
In the right-hand pane, right-click the table with the structure you want to copy, and then choose Copy . Access copies the database to the Windows clipboard. -
Choose Edit Paste . The "Paste table as" dialog box appears (Figure 5-14). -
Give the table a name by typing text into the Table Name box. Then, under Paste Options, choose Structure Only and click OK . Access creates a new table that has the structure of your old table ‚ but not the data. The table appears in the same pane as your existing tables; simply double-click it to open it and add new data.  | Figure 5-14. If you're copying a table and you want to retain only the structure of your old table, not the data, choose Structure Only. If you plan on keeping some of the data, choose Structure and Data, and you can edit the existing data. | | 5.5.2 The Database Diet Databases have a way of becoming fat and bloated, no matter how hard you try to keep them slim and trim. The problem is that when you delete a record, the database still retains the space that the record took up. Thus, over time, as you add and delete records, your databases get larger and larger ‚ even though you think you're keeping them in check. And super- sized databases tend to have data corruption problems. The solution: Compact and repair your database regularly. This process removes all the empty space your deleted records left behind, and it keeps the database down to fighting weight. Simply choose Tools Database Utilities Compact and Repair Database. But you may forget to run the compaction regularly. So here's better solution: Have Access do it for you every time you close the database. Open the database you want to compact automatically. Choose Tools Options, and then click the General tab. Turn on Compact on Close, and then click OK. That's it. Access automatically compacts that puppy every time you close it.
Note: You have to do this process for each of your databases; there is no way to tell Access to compact all of them automatically.
5.5.3 Hiding Top Secret Tables Say you're a CIA agent, and you have tables with sensitive data in them that you don't want anyone to see; here's a quick way to hide them. Right-click the table you want to hide and choose Rename. Type in a new name, but make sure the first four letters are usys . That tells Access to turn the table into a system object , which are elements of the database that you can't see. To make the table visible again, choose Tools Options and click the View tab. Under Show, turn on System objects, and click OK. Presto ‚ your table is visible. Make it invisible again by turning off the box next to System objects, and clicking OK. |
