ch03.htm

Microsoft_Access_97_Quick_Reference.zip

			Right-click the file name and choose P<U>r</U>operties. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Database: Compact</B></FONT></H2>
			<P>Access stores all of its <I>objects</I> and data in a single file. As you delete
			the information in <I>tables</I> and the tables themselves, not all of the space
			is reclaimed efficiently. Therefore, every so often you should compact your <I>database</I>
			to shrink its size, remove free space, and improve performance. During compacting,
			Access checks data and validates database structure.</P>
			<P>To compact the current database, choose <U>T</U>ools, <U>D</U>atabase Utilities,
			<U>C</U>ompact Database.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Close your current database and have any connected users close their
				session to the database you intend to compact.
				<P>
				<DD><B>2. </B>Choose <U>T</U>ools, <U>D</U>atabase Utilities, <U>C</U>ompact Database.
				<P>
				<DD><B>3. </B>Select the name of the database you want to compact in the Database
				To Compact From <I>dialog box</I>; then choose <U>C</U>ompact.
				<P>
				<DD><B>4. </B>Enter the name, drive, and folder for the compacted database in the
				Compact Database Into dialog box; then choose <U>S</U>ave.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If you delete records at the end
			of a <I>table</I> with an <I>AutoNumber</I> <I>field</I>, Access normally skips these
			numbers. When you compact a database, Access resets the AutoNumber field so that
			the next <I>record</I> added is one more than the largest existing AutoNumber. Because
			compacting improves the efficiency of your database, you might want to programmatically
			build compacting into your application. The <I>VBA</I> statement for compacting is
			<TT>DBEngine.CompactDatabase</TT> olddatabase<TT>,</TT> newdatabase. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Database: Convert</B></FONT></H2>
			<P>When you try to open a <I>database</I> created in a prior version of Access, you
			will be prompted on whether you want to convert the database or Open the database.
			When you open the database you can use a prior version to enter data, but you cannot
			change the design or create a new <I>object</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Close any open database.
				<P>
				<DD><B>2. </B>Click the Open Database button on the toolbar. Choose the location
				and name of the older version file and choose <U>O</U>pen.
				<P>
				<DD><B>3. </B>In the Convert/Open Database <I>dialog box</I>, choose <U>C</U>onvert
				Database.
				<P>
				<DD><B>4. </B>In the Convert Database Into dialog box, type the name of the file
				in the File Name <I>text box</I>; choose <U>S</U>ave.
			</DL>
			<P>The database opens and is converted into your new version of Access.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If you open the old version database
			(rather than convert it), Access might not bring up the Convert dialog box again.
			In this case, close any database and choose <U>T</U>ools, <U>D</U>atabase Utilities,
			Conver<U>t</U> Database; then, choose the database and give it a new name. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Database: Default Folder Set</B></FONT></H2>
			<P>If you store most of your database files in one folder, you can have Access automatically
			go to that folder when you first start Access and choose to open a database.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Choose <U>T</U>ools, <U>O</U>ptions, and click the General tab.
				<P>
				<DD><B>2.</B> In the <U>D</U>efault Database Folder text box, type the name of the
				folder where you store your databases; choose OK.
			</DL>
			<H2><FONT COLOR="#000077"><B>Database: Repair Closed Database</B></FONT></H2>
			<P>You can repair a <I>database</I> that isn't currently in view. The process is
			only slightly different than repairing an open database. (See &quot;Database: Repair
			Open Database.&quot;) You might try to open a database and Access informs you that
			the database needs to be repaired before it is opened. Your database might be corrupted
			because the power went off while the database was open.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Close your current database.
				<P>
				<DD><B>2. </B>Choose <U>T</U>ools, <U>D</U>atabase Utilities, <U>R</U>epair Database.
				<P>
				<DD><B>3. </B>Select the database in the Repair Database <I>dialog box</I>.
				<P>
				<DD><B>4. </B>Choose the <U>R</U>epair button.
			</DL>
			<P>Access will perform data <I>validation</I> and other procedures, and repair the
			database, if possible.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT><B> </B>Access isn't always capable of
			repairing badly damaged database files. You should always maintain an active system
			for backing up your database so that you can revert to your last backup should your
			current file be unusable. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Database: Repair Open Database</B></FONT></H2>
			<P>When you open, compact, encrypt, or decrypt a database that is damaged, Access
			will inform you of the damage and post a <I>dialog box</I> that offers to repair
			it. In instances when you find erratic behavior (maybe tables don't sort correctly
			or a report takes an unusually long time to run), you might want to initiate the
			procedure of repairing a database manually before you get an error message.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click the Open button and check the Exclusi<U>v</U>e box, select the
				database file name and choose <U>O</U>pen.
				<P>
				<DD><B>2.</B> Select <U>T</U>ools, <U>D</U>atabase Utilities, <U>R</U>epair Database.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The VBA statement for repairing
			a database is <TT>DBEngine.RepairDatabase </TT>databasename. If you are designing
			an application for other users, you may want to include a command button for repairs
			or do automatic repairs somewhere in your code. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Excel: Convert Access Object to Excel Worksheet</B></FONT></H2>
			<P>You can convert Access <I>tables</I>, <I>queries</I>, <I>forms</I>, and <I>reports</I>
			to Excel worksheets by using the Office Links feature. Excel's strength in this instance
			is its capability to analyze the data.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Select the <I>object</I> (to be converted) in the <I>Database window</I>.
				<P>
				<DD><B>2. </B>On the Office Links button, choose <U>A</U>nalyze It with MS Excel.
			</DL>
			<P>This process Opens Excel, converts the selected object, and saves the name of
			the object with an XLS extension.</P>
			<P>If the object was a <I>form</I>, Excel creates one row for each <I>record</I>
			with the <I>field</I> names as the first row. Excel ignores any information on a
			<I>subform</I>. If the object was a grouped <I>report</I>, the Excel worksheet is
			in outline format, enabling you to show or hide detail.
			<H2><FONT COLOR="#000077"><B>Export and Import: Installing Additional Drivers Through
			Setup</B></FONT></H2>
			<P>If the file format does not appear on your Files As <U>T</U>ype when you are exporting
			or importing data (see &quot;Export Data: Access to Another File Type&quot;), you
			might need to install additional data drivers. Access has two sources for these drivers
			(some of which overlap): the setup program and the ValuPack folder. (See &quot;Export
			and Import: Installing Additional Drivers Through ValuPack.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> First, close any open applications and insert the Office or Access
				CD-ROM.
				<P>
				<DD><B>2. </B>On the Windows Desktop, double-click My Computer and double-click the
				CD-ROM drive. Double-click the Setup icon.
				<P>
				<DD><B>3. </B>On the setup <I>dialog box</I>, choose <U>A</U>dd/Remove, select the
				Data Access choice in the <U>O</U>ptions list, and click the Change <I>option button</I>.
				Choose Database Drivers in the Options list and click the Change Option button.
				<P>
				<DD><B>4. </B>Pick from the list of Options and then choose the OK, Continue, and
				Yes buttons until you finish the setup.
			</DL>
			<H2><FONT COLOR="#000077"><B>Export and Import: Installing Additional Drivers Through
			ValuPack</B></FONT></H2>
			<P>If you cannot get drivers from the setup program (see &quot;Export and Import:
			Installing Additional Drivers Through Setup&quot;) you can also look at the ValuPack.
			This is especially necessary for Lotus 1-2-3 and Paradox files.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>First, close any open applications and insert the Office or Access
				CD-ROM.
				<P>
				<DD><B>2.</B> On the Windows Desktop, double-click My Computer and double-click the
				CD-ROM drive. Double-click the ValuPack folder and then the Dataacc folder.
				<P>
				<DD><B>3.</B> Inside the Dataacc folder is the Dataacc program. Double-click the
				program icon and choose <U>Y</U>es to install the Microsoft Data Access Pack.
				<P>
				<DD><B>4. </B>After the Data Access Pack is installed, click the <U>A</U>dd/Remove
				button, choose Data Access Drivers and the Chang<U> </U>e option button.
				<P>
				<DD><B>5.</B> Pick from the list of <U>O</U>ptions and then choose the OK, <U>C</U>ontinue,
				and <U>Y</U>es buttons until you finish the setup.
			</DL>
			<H2><FONT COLOR="#000077"><B>Export Data: Access to Access</B></FONT></H2>
			<P>If you need to export any objects from one Access database to another Access database,
			you can use the <U>F</U>ile Save <U>A</U>s/Export menu choice. Another option is
			to import from an Access database. (See &quot;Import Data: Access.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click the <I>object</I> you want to export in any tab of the <I>Database
				window</I>.
				<P>
				<DD><B>2. </B>Choose <U>F</U>ile, Save <U>A</U>s/Export.
				<P>
				<DD><B>3. </B>Click the To an <U>E</U>xternal File or Database <I>option button</I>;
				then choose OK.
				<P>
				<DD><B>4. </B>Select the Access database you want to export to in the file <I>dialog
				box</I>; then choose Export.
				<P>
				<DD><B>5. </B>If you chose a <I>table</I> in Step 1, indicate in the Export dialog
				box whether you want to export just the table De<U>f</U>inition Only, or the Definition
				and <U>D</U>ata by selecting the appropriate option button; then choose OK.
			</DL>
			<P>Access copies the object (or table definition) to the database you indicated.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Exporting tables in this manner works
			for all versions of Access. If you are exporting to Access 95 or 97, you can use
			the same procedure to copy queries and macros from one Access database to another.
			
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Export Data: Access to Another File Type</B></FONT></H2>
			<P>Access can export to different <I>database file</I> types, or to other data file
			formats. Single records, multiple records, tables, queries, forms, and reports can
			all be exported depending on the file type.</P>
			<P>Access creates a file in the format you specify. If a file doesn't support long
			table names, as is the case for FoxPro 2.5 for example, Access truncates the <I>field</I>
			names appropriately in the conversion. In some cases, you may need to install the
			file driver for the external program. (See &quot;Export and Import: Installing Additional
			Drivers Through Setup&quot; and &quot;Export and Import: Installing Additional Drivers
			Through ValuPack.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click the table or <I>query</I> you want to export in the <I>Database
				window</I>.
				<P>
				<DD><B>2. </B>Choose <U>F</U>ile, Save <U>A</U>s/Export.
				<P>