ch08.htm

Microsoft_Access_97_Quick_Reference.zip

				<P>
				<LI><I>Assigning more memory.</I> You can assign more memory to Access, or close
				other programs or unnecessary system utilities like wallpaper, to free up additional
				RAM. (You can add additional RAM to your computer, but do not set up a RAM disk because
				Access creates its own.)
				<P>
				<LI><I>Defragmenting the hard drive. </I>You can defragment your hard drive using
				the Disk Defragmentor. Choose the Windows 95 Start button, <U>P</U>rograms, Accessories,
				System Tools, Disk Defragmentor.
				<P>
				<LI><I>Upgrading.</I> You could improve your processor by upgrading your computer.
			</UL>
			<H2><FONT COLOR="#000077"><B>Optimization: Performance Analyzer</B></FONT></H2>
			<P>The performance of your <I>database</I> might be limited by the design of the
			database itself. If you have tables with redundant data, you can have poorly constructed
			indexes, inappropriate <I>data types</I>, or the wrong <I>join</I> definition for
			relationships. Run the Performance Analyzer to see suggestions on optimizing database
			objects, and then perform the suggestions as desired.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT> The Performance Analyzer can take a
			significant amount of time to run. Experiment with it first on a small database.
			Also, make sure you back up your database before you run the Performance Analyzer.
			(See &quot;Backup Data&quot; in the File Management part of this book) 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> Choose <U>T</U>ools, Anal_yze, <U>P</U>erformance.
				<P><B>2.</B> Click an <I>object</I> tab or the All tab in the <I>Database window</I>
				and select the objects you want to analyze (or click the Select All button). Choose
				OK.</P>
				<P><B>3.</B> The results appear in the Analysis Results <I>list box</I>. Icons appear
				for recommendations, suggestions, ideas, and whether something was fixed. Move to
				each item on the list and look at the lower half of the screen (Analysis Notes) for
				a description of the item and additional tips for fixing the problem.</P>
				<P><B>4.</B> For recommendations and suggestions, you can click the <U>O</U>ptimize
				button to make the change. Choose Close when finished.
			</OL>
			<H2><FONT COLOR="#000077"><B>Optimization: Split Database</B></FONT></H2>
			<P>You can split a <I>database</I> into two files: one that contains the tables,
			and another that contains all of the other <I>objects</I> in the database that act
			on the base tables (<I>queries</I>, <I>forms</I>, <I>reports</I>, <I>macros</I>,
			and <I>modules</I>). This is useful when you want to put the <I>table</I> object(s)
			on a network server and the other object(s) on the user's machine. This is also useful
			when you want to provide a means for users to access data while maintaining their
			own forms and reports or database interface.</P>
			<P>You can use the Database Splitter Wizard to perform the function of splitting
			an existing database into its tables and other component objects.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> Choose <U>T</U>ools, Add-Ins, <U>D</U>atabase Splitter.
				<P><B>2.</B> Choose the Split Database command button.</P>
				<P><B>3.</B> Enter the name for the tables database in the File <U>N</U>ame <I>text
				box</I> on the Create Backend Database <I>dialog box</I>, then click the Split command
				button.
			</OL>
			<P>Access creates a new database based on your current data and structure. In the
			original database, Access attaches the tables to the backend database created.
			<H2><FONT COLOR="#000077"><B>Optimization: Table Analyzer</B></FONT></H2>
			<P>You can analyze your <I>database</I> <I>table</I> structure using the Table Analyzer
			Wizard. This wizard checks for redundant data, and can create smaller related tables
			and a <I>query</I> with relationships between the former tables to improve performance.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT> Because Access makes significant changes
			throughout your database when you use the Table Analyzer, you should back up your
			database first. (See &quot;Backup Data&quot; in the File Management part of this
			book.) 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> To run the Table Analyzer Wizard, choose <U>T</U>ools, <U>A</U>nalyze,
				<U>T</U>able. On the first two steps of the wizard, Access can show you some examples
				of duplicating information and how to deal with updating information. Choose <U>N</U>ext
				twice if these screens appear.
				<P><B>2.</B> Choose the table in the list you want to analyze. Also, uncheck <U>S</U>how
				Introductory Pages if you don't want to see the pages mentioned in Step 1. Choose
				<U>N</U>ext.</P>
				<P><B>3.</B> Tell the wizard to suggest fields to go into the tables and choose <U>N</U>ext.</P>
				<P><B>4.</B> On the next screen of the wizard, you can confirm the suggestions that
				the wizard made for you, create a new table by dragging fields into a blank area,
				and move fields to a table by dragging between <I>field</I> lists. To name a table,
				click the table and then click the Rename Table button and enter the new name. Choose
				<U>N</U>ext.</P>
				<P><B>5.</B> Select any <I>primary keys</I> you want by clicking the field and then
				clicking the Set Unique Key button. You can also create an <I>AutoNumber</I> primary
				<I>key field</I> by clicking the Add Generated Unique Key button. Choose <U>N</U>ext.</P>
				<P><B>6.</B> The wizard might find close duplications in the values for tables. In
				the Correction column, choose Leave As Is to ignore the corrections. Choose <U>N</U>ext;
				on the last step of the wizard, tell Access to create a query that will be used in
				place of your original table in forms and reports. Choose <U>F</U>inish.
			</OL>
			<P>Access creates the tables and query in your <I>Database window</I> and renames
			your original table with an _OLD extension.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT> If you are finding and sorting records,
			creating an <I>index</I> specifically composed for this purpose can improve performance
			considerably. (See the tasks relating to Index in the Table and Database Design part
			of this book.) 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Programming: Create a Procedure</B></FONT></H2>
			<P>Procedures are the basic programming units of <I>VBA</I>. They are alternatives
			to creating macros and offer much greater flexibility, including the ability to trap
			and handle errors. (See also &quot;Macros: Create with Database Window<I>.</I>&quot;)</P>
			<P>There are two types of procedures: sub procedures and function procedures. The
			major difference between the two is that a function procedure can return a value
			where a sub procedure cannot. To create a function procedure, see also &quot;Programming:
			Create Function.&quot; Many of the other following tasks in this part help you create
			procedures.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT> This book covers only some of the fundamentals
			of Visual Basic. For more details, you can choose from several Que titles. These
			titles include <I>Special Edition Using Microsoft Access 97, </I>which is the companion
			piece to this Quick Reference; <I>Access 97 Expert Solutions; </I>and <I>Access 97
			Power Programming.</I> 
<HR>


			</BLOCKQUOTE>

			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> From the Database window, click the Modules tab and then click the <U>N</U>ew
				button to create a new module. Make sure that the <TT>Option Explicit</TT> statement
				appears at the top of the module.
				<P><B>2.</B> Type <B>Sub</B> followed by the procedure name.</P>
				<P><B>3.</B> If there are any <I>arguments</I> that are required to run the procedure,
				include those in parentheses as well as the keyword <TT>As</TT> and the <I>data type</I>
				for each argument. Press Enter. Access will automatically add <TT>End Sub</TT> at
				the end of the procedure.</P>
				<P><B>4.</B> Type any statements between the <TT>Sub</TT> and <TT>End Sub</TT>. As
				you type, Access' AutoComplete feature might show a drop-down list. You can click
				one of the entries in the drop-down list or press Ctrl+Enter to enter the selected
				item.</P>
				<P><B>5.</B> To add comments to your procedure, type an apostrophe (<B>'</B>) and
				then the <I>comment</I>. The apostrophe can begin a line or can be added after any
				statement.</P>
				<P><B>6.</B> When finished writing the procedure, choose the Save button and give
				the module a name.
			</OL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> This task shows how to create a new <I>module</I>
			and new procedure within the module. If you want to add a procedure to an existing
			module, select the module and click the <U>D</U>esign button. Move to the end of
			the module (press Ctrl+End) and type the Sub statement mentioned in Step 2. 
<HR>


			</BLOCKQUOTE>

			<P>The following is an example of a <TT>Sub</TT> procedure:</P>
			<P><TT>Sub ShowTableNames() `Names of tables in current database</TT></P>
			<P><TT>Dim x As Integer, tdef As TableDef</TT></P>
			<P><TT>`CurrentDb is the Current Database</TT></P>
			<P><TT>For Each tdef In CurrentDb.TableDefs</TT></P>
			<P><TT>Debug.Print tdef.name</TT></P>
			<P><TT>Next tdef</TT></P>
			<P><TT>End Sub</TT>
			<H2><FONT COLOR="#000077"><B>Programming: Create Function</B></FONT></H2>
			<P>You might need a <I>function</I> that Access does not supply as one of the built-in
			functions. You can try creating one from Excel or you can create one of your own.
			(See also &quot;Programming: Function Create from Excel.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<OL>
				<B>1.</B> Open an existing <I>module</I> in <I>Design View</I>, or click the <U>N</U>ew
				button on the Modules tab to create a new module.
				<P><B>2.</B> In a blank area outside of any procedures, type <B>Function Functionname(</B>,
				any <I>arguments</I> you need to run your function, and a closed parenthesis<B>)</B>,
				and press Enter. If you have arguments, include the keyword <B>Type</B> after the
				name and the <I>data type</I>. Replace <I>Functionname</I> with the name of your
				function. For example, type <B>Function FahrenheitToCelsius (Fdegree as Single)</B>.<B>
				</B>Access will automatically add <TT>End Function</TT> at the end of the procedure.</P>
				<P><B>3.</B> If necessary, type any lines for declaring variables additional to the
				arguments or any other statements required for processing the function. In the line
				that will return the value, type the function name again, an equal sign, and the
				formula that calculates the function. For example, type <B>FahrenheitToCelsius =
				5/9*(Fdegree - 32)</B>.</P>
				<P><B>4.</B> Click the Save button to save the module, and then test the procedure.
			</OL>
			<P>You can use this function within another procedure or as part of a calculated
			<I>field</I> in a <I>query</I>, <I>form</I>, or <I>report.</I> (See &quot;Calculated
			Fields: Queries--Create by Typing&quot; and &quot;Calculated Fields: Forms and Reports--Create
			by Typing&quot; in the Calculations part of this book.)


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> To see a list or use a built-in function
			from Access, click the Build button when you are creating a formula or <I>expression</I>
			in Design View of a query, form, or report. Choose Expression <I>Builder</I> and
			double-click Functions in the folder area. To see Access functions, click the Built-In
			Functions folder. To see functions you've created, click your <I>database</I> name
			folder. 
<HR>

<HR>
<BR>
			<FONT COLOR="#000077"><B>CAUTION:</B></FONT> Be aware that there are two similar
			but distinctive Build Buttons in Access. There is the toolbar Build button, denoted
			by a wand over an ellipses (...); and there is the Properties tab build button, denoted
			by simply an ellipses (...). 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Programming: Create Function from Excel</B></FONT></H2>
			<P>Access has a significant number of built-in functions, but Excel has even more.
			If you are familiar with an Excel <I>function</I> that you want to use in Access,
			follow these steps. Excel has to be loaded for this procedure to work. For steps
			on creating a function in Access, see also &quot;Programming: Create Function.&quot;