ch03.htm

Microsoft_Access_97_Quick_Reference.zip

				<DD><B>3. </B>Choose the To an <U>E</U>xternal File or Database <I>option button</I>;
				choose OK.
				<P>
				<DD><B>4. </B>Select the location of the file in the Save <U>I</U>n <I>list box</I>.
				In the Save As <U>T</U>ype list box, select the format you want to save the table
				or query in.
				<P>
				<DD><B>5. </B>Enter a name in the File <U>N</U>ame <I>text box</I>, then choose Export.
			</DL>
			<P>When you export a <I>table</I> or query, Access offers you the following file
			formats as types: Access files; text files (.TXT) in either delimited or fixed width
			format; Microsoft Excel 3, 4, 5-7, 97; <I>HTML</I> files; dBASE III, IV and V; Microsoft
			FoxPro 2.<I>x </I>and 3.0; Microsoft Word Merge; Rich Text Format (.RTF); Microsoft
			IIS 1 and 2 O (Internet Information Server); Microsoft ActiveX Server; and ODBC Databases.
			When you purchase the Office 97 ValuPack, included are drivers for conversion to
			Paradox databases versions 3.<I>x</I>, 4.<I>x</I>, and 5.0; and Lotus 1-2-3 versions
			2 and 3.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>When you select text files as your
			export type, Access opens the Export Text Wizard, which enables you to set the format
			of your text to Windows (ANSI), DOS, OS/2 (PC-8); date, time, and number format;
			and which fields get exported. This wizard also lets you select whether you create
			a delimited or fixed width text file. (See &quot;Export Data: Text Files.&quot;)
			
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Export Data: Access Object to HTML</B></FONT></H2>
			<P>Access 97 ships with HTML templates that you can use to create Web pages of a
			particular style. They are stored in the &lt;path&gt;\Program Files\Microsoft Office\Templates\Access
			folder by default.</P>
			<P>Publishing dynamic <I>HTML</I> files to a Web server requires that you determine
			the format that your particular server requires. For Microsoft IIS, that format is
			IDC/HTX files; for ActiveX servers, that format is ASP files.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Choose File, Save As HTML. The Publish to the Web Wizard opens; choose
				the <U>N</U>ext button.
				<P>
				<DD><B>2. </B>Choose which objects (on the Tables, Queries, Forms, Reports, or All
				Objects tabs); choose the <U>N</U>ext button.
				<P>
				<DD><B>3.</B> If you have a default template that contains background patterns or
				other styles for the Web pages, enter it on the third step of the Publish to the
				Web Wizard; choose the <U>N</U>ext button.
				<P>
				<DD><B>4. </B>Choose whether you want to create a Static or Dynamic HTML file; choose
				the <U>N</U>ext button. If you created a dynamic Web page, Access creates the HTML
				file and links it to the data source you specified. If you created a static Web page,
				you can copy to file to your intranet/Internet site.
				<P>
				<DD><B>5.</B> Specify the computer or <I>data source</I> used by the Web server,
				and a username or password if that data source requires it. If you created an ASP
				file, enter the server URL of the location of the ASP file.
				<P>
				<DD>If you chose Static HTML, identify where you want to store the file.
				<P>
				<DD><B>6. </B>On the last two pages of the Wizard, indicate if you want to create
				a home page for the objects and if you want to save the settings.
			</DL>
			<P>You can create view <I>forms</I> that display <I>records</I>; data entry forms
			that add, modify, or delete records; or switchboard forms that navigate to other
			Web pages. The forms appear similar to the way they look in your <I>database</I>.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>When you create a form, you can
			only output it as an ActiveX Server (.ASP) file. When a form is exported to HTML,
			most <I>controls</I> become <I>ActiveX controls</I> and all Visual Basic <I>code</I>
			associated with the controls is ignored. All <I>data types</I> are output as unformatted
			text, and the Format and InputMask properties of the controls are also ignored. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Export Data: Text Files</B></FONT></H2>
			<P>Exporting to text files is common because most <I>database</I>, spreadsheet, and
			word processing programs will accept this format when another format is not available.
			There are actually two formats for text files: Fixed Width and Delimited. Fixed Width
			is the less common of the two. Each <I>field</I> in a <I>record</I> is a set width
			(first name goes from positions 1-8, last name is 9-15, and so forth). When you use
			this option, the Export Wizard enables you to manually change the width between columns.
			Delimited is the more common of the text file formats and is described in the following
			steps. Delimited means that there is some character (usually a comma) separating
			the fields. Text also is usually indicated by quotes.</P>
			<P>To begin this procedure, first follow the steps in &quot;Export Data: Access to
			Another File Type&quot; in the previous task.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Choose <U>D</U>elimited on the Export Text Wizard.
				<P>
				<DD><B>2.</B> Choose which delimiter (Tab, Semicolon, Space, Other) you want between
				fields, check if you want to <U>I</U>nclude Field Names on First Row, and define
				the Text _Qualifier (the default is quotes).
				<P>
				<DD><B>3. </B>The last step allows you to change the file name and <I>path</I>; choose
				<U>F</U>inish.
			</DL>
			<H2><FONT COLOR="#000077"><B>Export Data: Word</B></FONT></H2>
			<P>You can convert Access <I>tables</I>, <I>queries</I>, <I>forms</I>, and <I>reports</I>
			to Word documents by using the Office Links feature. You might want to use Word for
			its formatting capabilities and to add additional text describing the data. Access
			also enables you to create a Word <I>mail merge.</I> (See &quot;Mail Merge to Word&quot;
			in the Outputting part of this book.)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Select the <I>object</I> in the <I>Database window</I>.
				<P>
				<DD><B>2. </B>On the Office Links button on the toolbar, choose <U>P</U>ublish It
				with MS Word.
			</DL>
			<P>This process Opens Word, converts the object, and saves the name of object with
			a RTF (rich text format) extension.</P>
			<P>If the object is a table, query, or form, Word creates a table. Word ignores any
			information on a <I>subform</I>. If the object is a report, Word creates tabbed entries.
			<H2><FONT COLOR="#000077"><B>Import Data: 1st Steps</B> <A NAME="st"></A></FONT></H2>
			<P>When you import data, you store the data in an Access <I>table</I>. When you import
			a text file or spreadsheet, you can append the data directly to a table. When you
			import from a <I>database</I> table the data goes into a new table. Then you can
			use an Append <I>query</I> to add the data to another table in your database. (See
			&quot;Action Query: Append Query&quot; in the Queries and Filters part of this book.)</P>
			<P>You begin importing the same way, regardless of the <I>data type</I>. If the data
			type is not in your Files of Type list, you may need to install a driver. (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>Choose <U>F</U>ile, Get External <U>D</U>ata, <U>I</U>mport.
				<P>
				<DD><B>2. </B>In the Import <I>dialog box</I>, choose folder from Look <U>I</U>n
				drop-down box, and the data type from the Files of <U>T</U>ype drop down.
				<P>
				<DD><B>3. </B>Select file name from list and choose the Import command button.
			</DL>
			<P>If the file is a database type file from another application (dBASE, FoxPro, or
			Paradox), these steps are sufficient to import the database to create an Access table.</P>
			<P>If the file is text, see &quot;Import Data: Text.&quot; If the file is a spreadsheet
			(Excel or Lotus 1-2-3), see &quot;Import Data: Spreadsheet.&quot; If the file is
			an Access database, see &quot;Import Data: Access.&quot;


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>After you import the data, Access
			creates an <I>object</I> in the <I>Database window</I>. If the import procedure does
			not prompt you for a new <I>table</I> name, Access makes the table name the same
			name as the file. Access does not overwrite existing objects, but instead avoids
			duplicate names by adding numbers to each imported table sequentially (such as <TT>Employee1</TT>,
			<TT>Employee2</TT>). After importing, you might consider renaming the object. (See
			&quot;Database Object: Rename.&quot;) 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Import Data: Access</B></FONT></H2>
			<P>If you want to copy <I>objects</I> from another Access <I>database</I>, you can
			use this import procedure or export. (See &quot;Export Data: Access to Access.&quot;)
			You first need to follow the steps in &quot;Import Data:1st Steps&quot; in the previous
			task and choose an Access database file.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>The Import Objects <I>dialog box</I> has tabs for <I>tables</I>, <I>queries</I>,
				<I>forms</I>, <I>reports</I>, <I>macros</I>, and <I>modules</I>. Click the objects
				you wish to import from each tab.<BR>
				<BR>
				<B>2. </B>If desired, choose the Options button and choose whether to import <I>relationships</I>,
				design features (Definition), the data in tables, and whether to import queries as
				queries or as tables; choose OK.
			</DL>
			<P>Access adds each of the objects into their appropriate places in the <I>Database
			window</I>.
			<H2><FONT COLOR="#000077"><B>Import Data: Checking Data Integrity</B></FONT></H2>
			<P>When you import data between different sources, you need to make sure the import
			has a reasonable chance of success. If the data in your original source is not all
			of the same type, Access will convert the <I>data type</I> to text or another data
			type. If the <I>field</I> names are invalid for Access, the import might not work
			at all.</P>
			<P>In some cases, it might be easier to update the data in the original source. In
			other cases (especially when you no longer have the original program), you have to
			update the data in Access. It would be unusual if you didn't have to clean up some
			data. This is notably true when you are importing data into an existing Access <I>table</I>
			versus importing the data into a new table.</P>
			<P>Before you even launch Access, check the following in your old program.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Check that field names conform to Access naming rules. For example,
				Informix allows periods in field names. You will have to remove the periods before
				importing to Access. Field names also cannot have an exclamation point (!), accent
				grave (`), or square brackets ([]).
				<P>
				<DD><B>2. </B>Make all data in a column the same data type. If you have comments
				in a number field (such as unknown), you need to take them out. Alternatively, import
				the field as text. If you need to make calculations on the mixed data type field,
				separate it into two fields. An update <I>query</I> might help.
				<P>
				<DD><B>3. </B>If the import does not work, you will get an error message and an additional
				table, <TT>&lt;file name&gt;Import Errors</TT>. You can use this table to troubleshoot
				what you need to do to fix your original <I>data source</I>.
				<P>
				<DD><B>4. </B>After you import and change data properties, you might receive error
				messages as well. You might need to use some select queries, or check for duplicates
				or unmatched records.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>For information concerning update
			queries from Step 2,<B> </B>see &quot;Action Query: Update Query.&quot; For material
			supplementing Step 4, see &quot;Queries: Create,&quot; &quot;Queries: Duplicates:
			Remove,&quot; and &quot;Unmatched Queries&quot; in the Queries and Filters part of
			this book. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Import Data: HTML</B></FONT></H2>
			<P>When you import data, you create a copy of the data in a <I>table</I> in your
			Access database, but you leave the original <I>data source</I> intact. You can import
			an <I>HTML</I> table or list data source.</P>
			<P>Imported data is copied into your <I>database</I> and can be altered. Imported
			data is an independent copy of the original data.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> To import data, choose <U>F</U>ile, Get External <U>D</U>ata, <U>I</U>mport.
				<P>
				<DD><B>2. </B>In the Import <I>dialog box</I>, select HTML Documents (*html;*.htm)
				from the Files of <U>T</U>ype <I>list box</I>.
				<P>
				<DD><B>3.</B> Double-click the file of interest using the Look <U>I</U>n list box.
				The Import HTML Wizard runs.
				<P>
				<DD><B>4. </B>On the first steps of the wizard, choose whether the first row has
				headings, whether you want to import into a new table or existing table, what the
				field names and data types should be, and whether you want Access to add a <I>primary</I>
				key. Choose <U>N</U>ext after you make the choices on each screen.
				<P>
				<DD><B>5. </B>On the last step of the wizard, type a name for the table and choose
				<U>F</U>inish.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The Advanced button on every step