ch03.htm

Microsoft_Access_97_Quick_Reference.zip

			of the Import HTML Wizard enables you to see or change the choices you made for the
			preceding Step 4, as well as identify delimiters and symbols for dates, times, and
			numbers, and save this specification or retrieve another. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Import Data: Spreadsheet</B></FONT></H2>
			<P>When you import a spreadsheet (Excel or Lotus 1-2-3 data files), you have the
			option of importing a range or an entire sheet. You first must follow the steps earlier
			in this section (&quot;Import Data: 1<SUP>st</SUP> Steps&quot;) to choose an Excel
			or Lotus 1-2-3 spreadsheet file. The spreadsheet must be in the standard database
			format, with each row being a separate <I>record</I> and each column a different
			<I>field</I>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>After choosing the spreadsheet file, Access brings up the Import Spreadsheet
				Wizard. Choose to list worksheets or ranges and then select the appropriate part
				of the file; choose <U>N</U>ext.
				<P>
				<DD><B>2. </B>Identify whether the first row of the <I>data source</I> contains <I>field</I>
				names; choose <U>N</U>ext.
				<P>
				<DD><B>3. </B>Choose to store data in a Ne<U>w</U> <I>Table</I> or choose drop-down
				arrow for In an E<U>x</U>isting Table; choose <U>N</U>ext.
				<P>
				<DD><B>4. </B>Click in each field, type the Field Na<U>m</U>e, the Data <U>T</U>ype,
				whether the field should be <U>I</U>ndexed, or <U>S</U>kip the field; choose <U>N</U>ext.
				<P>
				<DD><B>5. </B>Decide whether you want Access to add a <I>primary key</I> or if you
				want to choose your own; choose <U>N</U>ext.
				<P>
				<DD><B>6. </B>On the final step of the Import Spreadsheet Wizard, give the table
				a name and choose <U>F</U>inish.
			</DL>
			<H2><FONT COLOR="#000077"><B>Import Data: Text</B></FONT></H2>
			<P>Text data can be from many sources, including mainframes. If your data is not
			in one of the other sources, this might be the only option for you to move your data
			from your program to Access. Text is often identified with quotes and the fields
			separated by commas. This is called a delimited text file. Another option is for
			every field to be a set number of characters. This text file is called fixed width.
			You first need to follow the steps earlier in this section (&quot;Import Data: 1<SUP>st</SUP>
			Steps&quot;) to choose a Text file.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>After choosing the text file, Access brings up the Import Text Wizard.
				Choose whether your file is delimited or fixed width; choose <U>N</U>ext.
				<P>
				<DD><B>2. </B>Show where columns break, whether first row contains field names, choose
				characters that delimit fields, and identify text; choose <U>N</U>ext.
				<P>
				<DD><B>3. </B>Choose to store data in a Ne<U>w</U> Table or choose drop-down arrow
				for In an E<U>x</U>isting Table; choose <U>N</U>ext.
				<P>
				<DD><B>4. </B>Click in each field, type the Field Na<U>m</U>e, the Date <U>T</U>ype,
				whether the field should be <U>I</U>ndexed, or <U>S</U>kip the field; choose <U>N</U>ext.
				<P>
				<DD><B>5. </B>Decide whether you want Access to add a <I>primary key</I> or if you
				want to choose your own; choose <U>N</U>ext.
				<P>
				<DD><B>6. </B>On the final step of the Import Text Wizard, give the <I>table</I>
				a name 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
			of the Import Text Wizard enables you to see or change the choices you made for the
			preceding Steps 2 and 4, as well as identify delimiters and symbols for dates, times,
			and numbers, and save this specification or retrieve another. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Import Data: Word</B></FONT></H2>
			<P>Although it is more rare than exporting to Word and importing from other file
			formats, there might be an occasion when you need to import text from Word into an
			Access <I>database</I>. The text needs to be in tab or <I>table</I> format. Each
			column is a <I>field</I> and each row is a <I>record</I>. You might need to design
			a table first in Access to accept Word's data. (See &quot;Database: Create Blank&quot;
			in the Table and Database Design part of this book.)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> In Access, design the table structure first by creating fields in the
				same order as the columns in the Word document. Be sure the <I>data types</I> match
				the data in the Word columns.
				<P>
				<DD><B>2. </B>Launch Word and open the file with the table or tabbed information.
				Select the data. Do not include any column headers in the selection.
				<P>
				<DD><B>3. </B>Choose the Copy button on the toolbar.
				<P>
				<DD><B>4. </B>Return to Access, go to the <I>Database window</I> and double-click
				the table you designed in Step 1.
				<P>
				<DD><B>5. </B>Choose <U>E</U>dit, Paste Appe<U>n</U>d to bring in the data from Word.
			</DL>
			<H2><FONT COLOR="#000077"><B>Links to External Data: 1st Steps <A NAME="st"></A></B></FONT></H2>
			<P>The process of linking to a <I>table</I> in Access is similar in to importing
			a table. (See &quot;Import Data: 1st Steps.&quot;) With linking, the original table
			serves as the <I>data source</I> and only a reference to that table is contained
			in your Access database. When you link to a table, you can view and often also modify
			the data in that table. Access takes care of the details of opening the table and
			saving it in the appropriate data format. Linking can be contrasted with importing.
			When you import a new table, a copy of the table is created in the Microsoft Access
			format. The source table is left intact.
			<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>L</U>ink Table.
				<P>
				<DD><B>2. </B>In the Link <I>dialog box</I>, select the appropriate file format in
				the Files of <U>T</U>ype drop-down list; then locate the file of interest using the
				Look <U>I</U>n <I>list box</I>.
				<P>
				<DD><B>3.</B> Select the table or spreadsheet, then choose the Link button.
			</DL>
			<P>What happens next depends on the data source you selected. See the following <I>sections</I>
			for notes on the various <I>data types</I>.</P>
			<P>For Access, unencrypted Paradox tables, or spreadsheets, Access tables are imported
			directly. For an encrypted Paradox table, you will be prompted for a password. A
			linked Access table has an arrow with the icon in the Tables tab of the <I>Database
			window</I>. Paradox shows a Px icon.
			<H2><FONT COLOR="#000077"><B>Links to External Data: dBASE and FoxPro</B></FONT></H2>
			<P>When you link to dBASE and FoxPro, an <I>index</I> is requested. Normally, that
			index is the primary index, but it can also be candidate indexes for these two database
			programs. To start, first follow the steps in the earlier task, &quot;Links to External
			Data: 1<SUP>st</SUP> Steps.&quot; After these initial procedures, complete the following
			steps.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>After you select the dBase or FoxPro file, Access opens the Select
				Index Files <I>dialog box</I>. Choose the FoxPro index (.CDX or .IDX) file or dBASE
				(.MDX or .NDX) file. If no index exists, choose the Cancel button.
				<P>
				<DD><B>2. </B>Enter the index that identifies each <I>record</I> in the Select Unique
				Record Identifier dialog box; choose OK.
			</DL>
			<P>When you link to a dBASE <I>table</I>, Access' <I>Database window</I> shows the
			dB symbol, FoxPro shows a fox icon.
			<H2><FONT COLOR="#000077"><B>Links to External Data: Excel</B></FONT></H2>
			<P>Before you link to an Excel file, the data must be in the appropriate format for
			a <I>database</I>. Columns will identify fields (with the <I>field</I> name usually
			at the top row) and rows indicate records. To start, first follow the steps in the
			earlier task, &quot;Links to External Data: 1<SUP>st</SUP> Steps.&quot; After these
			initial procedures, complete the following steps.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>After you select the Excel file, Access opens the Link Spreadsheet
				Wizard. Choose the worksheet or range that you want to link; choose <U>N</U>ext.
				<P>
				<DD><B>2.</B> Identify if the first row contains names for the fields; choose <U>N</U>ext.
				<P>
				<DD><B>3. </B>On the final step of the wizard, give the <I>table</I> a name and choose
				<U>F</U>inish.
			</DL>
			<P>The Tables tab of the <I>Database window</I> shows a linked Excel spreadsheet
			indicated by an Excel X icon.
			<H2><FONT COLOR="#000077"><B>Links to External Data: HTML File</B></FONT></H2>
			<P>When you link data, you store a reference to that data <I>object</I> in its original
			location, and generally you can modify or update the data from within Access.<I>
			</I>HTML linked data is read-only. You cannot change the data from within Access.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD>1. To link the data, choose <U>F</U>ile, Get External <U>D</U>ata, <U>L</U>ink.
				<P>
				<DD>2. In the Link <I>dialog box</I>, select <I>HTML </I>Documents (*html;*.htm)
				from the Files of <U>T</U>ype <I>list box</I>.
				<P>
				<DD>3. Double-click the file of interest using the Look <U>I</U>n list box. The Link
				HTML Wizard runs.
				<P>
				<DD>4. Choose whether the first row of the HTML file contains headers; choose <U>N</U>ext.
				<P>
				<DD>5. If desired, click each column and type new <I>field</I> names in the Field
				Na<U>m</U>e text box, change Data <U>T</U>ype in with the drop-down list, or choose
				if you want to <U>S</U>kip the column and not import it.
				<P>
				<DD>6. On the last step of the wizard, give the table name and choose <U>F</U>inish;
				choose <U>N</U>ext.
			</DL>
			<P>Access imports or links to each <I>table</I> or list in an HTML file as if it
			were an individual table. You will need to repeat this procedure if your HTML file
			contains two or more tables or lists in it.


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The Advanced button on every step
			of the Link HTML Wizard enables you to see or change the choices you made for the
			preceding Step 5, as well as identify delimiters and symbols for dates, times, and
			numbers, and save this specification or retrieve another. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Links to External Data: Text</B></FONT></H2>
			<P>Linking to a text file involves the same steps as importing to a text file. First,
			follow the steps in the earlier task, &quot;Links to External Data: 1<SUP>st</SUP>
			Steps.&quot; After these initial procedures, complete the following steps. You might
			need to use this procedure rather than import the data if you have other programs
			that use this text file.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>After choosing the text file, Access brings up the Link Text Wizard.
				Choose whether your file is delimited or fixed width; choose <U>N</U>ext.
				<P>
				<DD><B>2. </B>Show where columns break, whether first row contains field names, choose
				characters that delimit fields, and identify text; choose <U>N</U>ext.
				<P>
				<DD><B>3. </B>Click in each <I>field</I>, type the Field Na<U>m</U>e, the Date <U>T</U>ype,
				whether the field should be <U>I</U>ndexed, or <U>S</U>kip the field; choose <U>N</U>ext.
				<P>
				<DD><B>4. </B>On the final step of the Link Text Wizard, give the <I>table</I> a
				name and choose <U>F</U>inish.
			</DL>
			<P>The Tables tab of the <I>Database window</I> shows a linked text file indicated
			by a small notebook icon.</P>
			<P>For details on the Import Text Wizard, see also &quot;Import Data: Text.&quot;


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>The Advanced button on every step
			of the Link Text Wizard enables you to see or change the choices you made for the
			preceding Steps 2-3, as well as identify delimiters and symbols for dates, times,
			and numbers, and save this specification or retrieve another. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Links to External Data: Removing</B></FONT></H2>
			<P>If you no longer need to view the data from a linked file, you can delete the
			link. Deleting the link does not delete the data--it only deletes the access to the
			file.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Select the <I>linked table</I> in the <I>Database window</I>.<BR>
				<BR>
				<B>2.</B> Press the Delete key on your keyboard.<BR>
				<BR>
				<B>3. </B>When prompted, choose <U>Y</U>es to confirm removing the link.
			</DL>
			<H2><FONT COLOR="#000077"><B>Links to External Data: Updating</B></FONT></H2>
			<P>If the source files for the links move, you will not be able to view or edit the
			information in the files unless you update the location for the links.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Choose <U>T</U>ools, Add-<U>I</U>ns, <U>L</U>inked Table Manager.
				<P>
				<DD><B>2. </B>In the Linked Table Manager <I>dialog box</I>, choose which files you
				want to update. Click the <U>S</U>elect All button to choose all files.