`
`
`
`
`
`Merrill Communications LLC
`d/b/a Merrill Corporation
`Exhibit 1005 pt 5
`
`

`

`Linking, Importing,
`and Exporting Data
`Y ou'll want to read this d1apter if you
`
`already hav data on y m computer
`that you want to pull into Access, or if
`you have created an Access database and want to export its data to another program. Most
`procedures described in this chapter for linking, importing, and exporting data are quick
`and easy to use. However, a few fine points can be tricky, especially if you're working with
`open database connectivity (ODBC), Paradox, or dBASE database files. Luckily, lots of
`online Help is available. Choose one of these topics from the Access Help Index to get
`going quickly:
`
`• Importing Data
`• Exporting Data
`
`See Chapter 4 for other ways to share data among Microsoft Office programs.
`
`

`

`21 0
`
`CHAPTER 7 • LINKING, IMPORTING, AND EXPORTING DATA
`
`Link. Import. Export: What's the Difference?
`
`Access gives you several ways to share data with other programs:
`
`Link Lets you directly access data in another computer database. Any changes
`you make via Access will affect the original database as well as the Access data(cid:173)
`base. That is, you'll be working with live data.
`
`In some earlier versions of Access, linking was called attaching.
`
`Import Lets you make a separate copy of data from other programs and file formats
`into an Access table. Changes you make via Access will not affect the original data.
`Export Lets you copy data from an Access table to some other program or file
`format, such as Microsoft Word or Excel, or even to a separate Access database.
`The exported data is an entirely separate copy that isn't linked to your original
`Access data in any way.
`
`After importing or linking data from another program into Access, you can usually use
`that data as though you had created it in Access originally. Files linked from Lotus 1-2-3
`files are read-only. For example, if you import or link a Paradox table into your Access
`database, you can open that table and use it as you would any Access table.
`Similarly, you can use exported Access tables in other programs, just as though you
`had created the exported data in the other program originally. Thus you can export an
`Access table to Paradox format, fire up Paradox, and then open and use the table as you
`would any other "native" Paradox table.
`
`OLE is a technology that offers another way to combine information from separate
`programs. However, OLE lets you share objects, rather than data. We'll talk about
`OLE in Chapter 8.
`
`Interacting with Other Databases
`
`You can import or link data from any of these database formats:
`
`• dBASE III, III+, IV, and 5.
`• Paradox versions 3.x, 4.x, and 5.0.
`• Microsoft FoxPro versions 2.0, 2.5, 2.6, and 3.0.
`
`

`

`INTERACTING WITH OTHER DATABASES
`
`1211
`
`Automating Import and
`Export Procedures
`You can use a macro or Visual Basic proce(cid:173)
`dure to automate importing, linking, and
`exporting certain types of data. Here are
`the macro and Visual Basic actions you'll
`need to use:
`
`TransferDatabase Lets you import or
`export data between the current Access
`database and another database. See the
`TransferDatabase Action and Transfer(cid:173)
`Database Method topics in the Access
`Help Index.
`
`TransferSpreadSheet Lets you import
`or export data between the current Access
`database and a spreadsheet file. See the
`TransferSpreadsheet Action and Transfer(cid:173)
`Spreadsheet Method topics· in the Access
`Help Index.
`
`TransferText Lets you import or export
`data between the current Access database
`and a text file. See the TransferText Action
`and Transfer Text Method topics in the
`Access Help Index.
`
`SendObject Lets you create Microsoft
`Excel (*.xis), rich text format (*.rtf), MS(cid:173)
`DOS text (*.txt), HTML (*.htm), or HTX/
`IDC (*.htx, *.ide) output from a table,
`query, form, report, or module and then
`send that output in an electronic mail
`message. See the SendObject Action and
`SendObject Method topics in the Access
`Help Index.
`
`Parts Four and Five of this book introduce
`macros and Visual Basic.
`
`• ODBC databases, including Microsoft SQL Server. You'll need a properly installed
`and configured ODBC driver.
`• Databases created with the Microsoft Jet database engine, including Microsoft
`Access (versions l.x, 2.0, 7 .0) and Microsoft Visual Basic.
`• Databases kept in Microsoft Excel worksheets, versions 5.0 and 7.0.
`• HTML and HTX tables and lists.
`• Lotus 1-2-3 files (read-only when linked).
`
`Before you bring data from one of these other formats into an Access database, you
`need to decide whether to link to that data or import your own copy of it. Table 7.1 lists
`some points that can help you decide.
`
`

`

`212
`
`CHAPTER 7 • LINKING, IMPORTING, AND EXPORTING DATA
`
`Access 97 does not automatically include the drivers for Paradox and Lotus 1-2-3
`.....,......, .... files when you install it. You can get these drivers by installing the Office 97 Valu(cid:173)
`Pak. This option is included on the CD-ROM of the Microsoft Office Professional
`Edition 97. The ValuPak can also be downloaded from Microsoft's Web site:
`choose Help ~ Microsoft on the Web ~ Free Stuff from the Access menus. See
`Access 97 Help for more information on what the ValuPak provides.
`
`I
`
`TABLE 7.1: DIFFERENCES BElWEEN IMPORTED AND LINKED TABLES
`
`IMPORTED TABLE
`
`LINKED TABLE
`
`Copied from the external table to your
`open database. (Requires extra disk
`storage.)
`Use when you no longer need to have the
`original application update the table.
`Converted to Access format and works
`exactly like a table you created from
`scratch.
`You can change any properties, including
`the structure, of an imported table.
`Access can work faster with
`imported tables.
`Deleting the table deletes the copy from
`your open database.
`Access translates certain data types of the
`original table to Access data types.
`
`Linked to your open database. No copies
`are made. (Conserves disk storage.)
`
`Use when you still need to have the origi(cid:173)
`nal application update the table.
`Retains its original database format, but
`"acts like" an Access table.
`
`You can change some properties, but you
`can't change the structure of a linked table.
`Access may work more slowly with
`linked tables.
`Deleting the table deletes the link only,
`not the external table.
`No data type translations are necessary.
`
`Where You'll See Imported/Linked Tables
`When you import or link data from another database, Access treats the data as it would
`one of its own tables. For linked tables, however, special icons in the database window
`indicate that the data is not originally from Access.
`In Figure 7.1 we linked Access tables named Exp ' n S
`ateg des, Expense Detail , and
`Expense Reports (from another Access database); a Paradox tabl.e named Biolife; and a
`dBASE table named Grades to an open
`rder Entryl database. We also imported a table
`named Sales from a Microsoft Excel worksheet. Notic that the icon next to lhe ti.nked
`tables include an arrow symbol to indicate a link to the external tables; however, the icon
`next to the Sales table looks like any other Access table icon.
`
`

`

`anaMu•
`
`Special/ink
`icons indicate
`linked tables.
`
`IMPORTING OR LINKING A TABLE
`
`1213
`
`•
`
`!II Reports I C% Macros I
`
`Linked Paradox table
`
`Linked Access tables
`
`I .. ·- I ..,_ I
`L +Px BIOLIFE
`
`llliJ Customers
`llliJ Employees
`+llliJ Expense Categories
`+llliJ Expense Details
`+llliJ Expense Reports
`+dB Grades
`llliJ My Company Information
`llliJ Order Details
`llliJ Orders
`llliJ Payment Methods
`llliJ Payments
`llliJ Products
`llliJ Sales
`llliJ Shipping Methods
`
`Linked dBASE table
`Imported table
`
`Importing or Linking a Table
`
`Here are the general steps for importing or linking tables. The exact procedures will
`depend on the type of data you're importing or linking. Refer to the appropriate section
`below for more details.
`
`1. Open (or create) the database you want to import or link the table to and switch to
`the database window if it isn't visible.
`2. Do one of the following, depending on whether you are importing or linking:
`
`• To import, choose File )oo Get External Data )oo Import, or right-click on the data(cid:173)
`base window (the shaded part, not a table name) and choose Import. Or click
`on the Tables tab on the database window, click on the database window's New
`button, and then double-click on Import Table in the New Table dialog box.
`• To link, choose File )oo Get External Data )oo Link Tables, or right-click on the data(cid:173)
`base window and choose Link Tables. Or click on the Tables tab on the database
`window, click on the database window's New button, and then double-click on
`Link Table in the New Table dialog box.
`
`

`

`214
`
`CHAPTER 7 • LINKING, IMPORTING, AND EXPORTING DATA
`
`You cannot delete rows from linked Excel tables or text files.
`
`3. Choose the type of data you want to import or link in the Files Of Type drop-down
`list. For example, click on the drop-down list button next to Files Of Type and then
`click on Paradox to import or link a Paradox table.
`4. Locate the file name you want to import or link and then click on it in the list below
`Look in. Figure 7.2 shows a typical Link dialog box; the Import dialog box is similar.
`
`The techniques for locating drives, folders, and files in the Link and Import dialog
`boxes are the same ones that work in the Open and File New Database dialog boxes
`(see Chapters 1 and 5, respectively).
`
`5. Click on the Import or Link button or double-click on the file name you want to
`import or link.
`
`M#@ljjt##W
`A Link dialog
`box for linking
`an Excel table.
`
`3.Choose a file name. 2.Choose a folder.
`
`4.Ciick on Link (or Import).
`
`•
`
`::::J T eJil or properly: I
`
`find Now
`
`1.Choose a file format.
`
`

`

`IMPORTING OR LINKING A TABLE
`
`6. Respond to any additional prompts and dialog boxes that appear.
`
`• If you're linking a dBASE or FoxPro table, you'll be prompted to choose one or
`more existing indexes to associate with the table. Choosing indexes will speed
`up access to the linked table.
`• If you're importing or linking a table from Paradox or a SQL database, you may
`be prompted for a password. This password is set in the external program and
`is different from an Access ~ser password.
`
`Passwords of linked tables are stored in your database so that you can open the
`~oo;;;o-.:~. table later by double-clicking on it in the Access database window. Be aware that
`the database password is stored in an unencrypted form. If you need to protect
`sensitive data in the linked table, you should implement user-level security, rather
`than use a database password. (See Chapter 18 and the Help Index topic Linking
`Tables, Password-Protected Databases.)
`
`7. Click on OK if you see a message saying the operation is complete.
`8. If the Import or Link dialog box remains on your screen, you can repeat steps 3
`through 7 until you've imported or linked all the tables you want. When you're
`done, click on the Close button as needed.
`
`For more help on importing and linking tables, go to the Access Help Contents,
`open the Creating, Importing, And Linking Tables book, open the Importing Or Linking
`Tables book, and then double-click on an appropriate topic. You also can look up topics
`and subtopics under Importing Data and Linking Data in the Access Help Index.
`
`Importing or linking Paradox Tables
`'
`Access can import or link tables (.db files) from Paradox version 3, 4, and 5. Follow
`these steps:
`
`1. Start from the Access database window and choose the Import or Link command.
`(Two easy starting points are to choose the File> Get External Data commands on
`the menu bar, or right-click on the database window and choose Import or Link
`Tables from the shortcut menu.)
`2. Choose Paradox from the Files Of Type drop-down list.
`3. Locate the file you want to import or link and then double-click on it in the list
`below Look In. You may be prompted for a password.
`4. Type the password that was assigned to the table in Paradox and then click on OK.
`
`

`

`216
`
`CHAPTER 7 • LINKING, IMPORTING, AND EXPORTING DATA
`
`5. Click on OK when you see the message saying the operation is complete.
`6. Repeat steps 3 through 5 as needed. When you're done, click on Close.
`
`Having trouble? Perhaps the following tips will help:
`
`• A linked Paradox table must have a primary key (.px) file if you intend to update
`it through Access. If that file is not available, Access won't be able to link the
`table. Without a primary key, you'll only be able to view the linked table, not to
`change it.
`• In general, any ancillary file that Paradox maintains for a table (such as a· .mb file
`for memo fields) should be available to Access.
`• Because Access can't open OLE objects stored in a linked or imported Paradox
`table, no OLE fields will appear when you open the table in Access.
`• When linking a shared Paradox table that resides on a server, you must set the
`ParadoxNetPath key in the Windows Registry to the path of the Paradox. net or
`Pdoxus rs. net file. Furthermore, if you're using Paradox version 4 or 5 to share data
`in your workgroup, you also must set the ParadoxNetStyle key to 4.x. For Paradox
`version 3 tables, set this key to 3.x (the default is 4.x).
`
`For more details about importing and linking Paradox tables, ask the Office Assistant
`to search for Import Or Link A Paradox Table. You can also search for Registry or Registry
`Editor with the Office Assistant for details about using the Registry Editor.
`
`Making changes incorrectly in the Registry Editor can produce unexpected results
`in Access. Be sure to l;>ack up all or part of the Registry before making changes in
`the Registry Editor. See the topic Backing Up The Registry in the Registry Editor's
`Help Index for details.
`
`Importing or Linking dBASE and FoxPro Files
`
`Access can import or link tables (.dbf files) from dBASE III, IV, and dBASE 5 and from
`FoxPro 2.0, 2.5, 2.6, and 3.0. Here are the basic steps for importing or linking a dBASE
`or FoxPro table:
`
`1. Start from the Access database window and choose the Import or Link command.
`2. Choose dBASE III, dBASE IV, dBASE 5, Microsoft FoxPro, or Microsoft FoxPro 3.0
`from the Files Of Type drop-down list.
`3. Locate the file you want to import or link and then double-click on it in the list
`below Look In.
`
`

`

`IMPORTING OR LINKING A TABLE 1217
`
`4. Double-click on the index file you want to use if the Select Index Files dialog box
`appears. Repeat this step until you've specified all the index files associated with
`the .dbf file you chose in step 3, and respond to any other prompts that appear.
`When you're done, click on the Close or Cancel button.
`5. Click on OK when you see the message that the operation is complete.
`6. Repeat steps 3 through 5 as needed. When you're done, click on Close.
`
`When linking dBASE or FoxPro files, keep these points in mind:
`
`• To improve performance later, you can have Access use one or more existing dBASE
`index files (.ndx or .mdx) or FoxPro index files (.idx or .cdx). The index files are
`tracked in a special information file (.inf) and are maintained automatically when
`you update the table through Access.
`• If you use dBASE or Fox Pro to update data in a linked .dbf file, you must manually
`update the associated indexes; otherwise, Access won't be able to use the linked table.
`• If you move or remove any .ndx, .mdx, or .inf files that Access i.s using, then
`Access won't be able to open the linked table.
`• To link tables on a read-only drive or CD-ROM drive, Access must store the .inf
`file on a writable directory. To specify this directory, you must specify the path for
`the .inf file in the Windows Registry.
`
`For more about updating, look up Import Or Link A dBase Or FoxPro Table with the
`Office Assistant. Search for Registry or Registry Editor with the Office Assistant for details
`about using the Registry Editor.
`
`Importing or linking SQL Tables
`
`If everything is properly set up, you can use Access and ODBC drivers to open tables on
`Microsoft SQL Servers and other SQL database servers on a network. Here are the basic
`steps for linking or importing SQL database tables:
`
`I. Start from the Access database window and choose the Import or Link command.
`2. Choose ODBC Databases from the Files Of Type drop-down list.
`3. Click the Machine Data Source tab in the Select Data Source dialog box if you want
`to work with data from another user or system. Double-click on the SQL data source
`you want to use. (If necessary, you can define a new data source for any installed
`ODBC driver by clicking on the New button and following the instructions that
`appear. After creating the new data source, you can double-click on it to continue.
`Alternatively, you can manage your ODBC data sources by double-clicking on the
`32-bit ODBC icon in Control Panel.)
`
`

`

`CHAPTER 7 • liNKING, IMPORTING, AND EXPORTING DATA
`
`4. Complete the remaining dialog boxes that appear, clicking on OK as needed to pro(cid:173)
`ceed. (These dialog boxes depend on the data source you chose.) For example, you
`may be prompted ·for the following:
`
`• Your login ID and password
`• Whether you want to save the login ID and password (if you're linking)
`• The tables you want to import or link
`• The fields that uniquely identify each record
`
`5. Click on Close when you're finished importing or linking.
`
`If you're having trouble with the importing or linking procedure, consider these
`points:
`
`• Before connecting to a SQL database, you must install the proper ODBC driver for
`your network and SQL database. You can find information on this procedure by
`looking up the Installing Drivers topic in the Access Help Index.
`• Access includes the ODBC drivers for Microsoft SQL Server, Access, Paradox,
`dBASE, FoxPro, Excel, Oracle 7, and Text. Before using any other OBDC driver,
`check with its vendor to verify that it will work properly.
`• If you'll want to edit a linked SQL table, that table usually must contain a unique
`index. If no unique index exists, you can create one by executing a data-definition
`query within Access. For information on data-definition queries, open the Microsoft
`Jet SQL Reference book in the Access Help Contents, open the Data Definition Lan(cid:173)
`guage book, and then double-click on the CREATE INDEX Statement topic.
`• If the SQL table's structure changes after you link it, you'll need to use the Linked
`Table Manager to refresh your link to the table (see "Using the Linked Table Man(cid:173)
`ager" later in the chapter) or delete and recreate the link.
`• If an error occurs while you're importing, linking, or using a SQL table, the prob(cid:173)
`lem may be with your account on the SQL database server or the database itself.
`Please contact your SQL database administrator for assistance.
`
`\
`
`For more about the subtleties of importing and linking SQL tables, look up Import Or
`Link An SQL Table with the Office Assistant.
`
`Importing or Linking Other Access Databases
`Normally you'll want to store all the tables, forms, reports, and other objects that make
`up a given application in a single Access database. But in a large company, different
`departments might create their own separate Access databases. Fortunately, it's easy to
`import objects (tables, queries, forms, reports, macros, and modules) r link tables from
`other unopened Access databases into your open database. Then you can use the
`objects as if they were part of your currently open Access database. This method is
`
`

`

`IMPORTING OR LINKING A TABLE
`
`much easier than using copy and paste to copy several related objects at once. Also,
`linked tables allow many users to share a single copy of their data over a network.
`
`You can import just the structure or both the structure and data of Access tables.
`loiiiiiiiliiiilill If you intend to import objects (forms, reports, queries, and so forth) for use with
`tables in the open database, the name and structure of the tables in the open
`database must match the name and structure of the tables originally used to cre(cid:173)
`ate the objects you're importing.
`
`To import objects or link tables from an unopened Access database:
`
`1. Choose the Import or Link command from the Access database window.
`2. Choose Microsoft Access from the Files Of Type list.
`3. Locate the Access database you want to import or link and then double-click on it
`in the list below Look In.
`4. Do one of the following:
`
`• To link: You'll see the Link Tables dialog box shown in Figure 7.3; skip to step 6.
`• To import objects: You'll see the Import Objects dialog box shown in Figure 7.4;
`continue with step 5.
`
`5. Click on the tab for the type of object you want to import if you're importing
`objects. As Figure 7.4 shows, your options are Tables, Queries, Forms, Reports, Mac(cid:173)
`ros, and Modules. For additional import options, click on the Options button (see
`Figure 7 .4) and then choose any of the options shown. (For help with the options,
`click on the ? button at the upper-right corner of the Import Objects dialog box,
`and then click on the place you need help with.)
`
`M#ftQ;IUM
`The Link Tables
`dialog box.
`
`Lmk T abies
`
`~Jlkl~_ .. ,_ .. _ , _
`E ~epen~e C..teg01le~
`Expense Detailo
`Expense Reports
`Switchboard Items
`
`•
`
`H r.l l
`
`_ .. ,_ .. , __ ,_ ,_ ,_ .. _, ......
`
`r
`1
`c.-d
`u--s~~· - 1
`I
`!!
`Dfi~AI
`
`

`

`220
`
`CHAPTER 7 • LINKING, IMPORTING, AND EXPORTING DATA
`
`M#dQ;lUM
`The Import
`Objects dialog
`box after click(cid:173)
`ing on the
`Options button
`and the
`Tables tab.
`
`Import Objects
`
`fllil T abies I rn:P Queries I §I Forms I l1 Reports I ~ Macros I 4
`
`Employees
`Expense Categories
`~pense Details
`ExJJen:;e Reports
`Sl'lltchbo<!!td Items
`
`613
`Modules I
`J
`
`Import T •bles ~._.,~
`r. Definition and Q.ata
`r De{i1ition only
`
`6. Use any of the following techniques to select the object(s) you want to import or
`link and then click on OK to continue.
`
`• To select all the objects of a particular type in one fell swoop, click on the
`Select All button.
`• To deselect all the selected objects, click on the Deselect All button.
`• To select one object that isn't selected yet, or to deselect an object that is selected,
`click on its name.
`
`Access will import or link all the objects you selected and return to the database
`window.
`
`The opposite of importing or linking a database is splitting it-a trick that can make
`your database run faster over networks. You can use the Database Splitter Wizard
`(Tools~ Add-Ins~ Database Splitter) to split a database into one file that contains
`the tables and another that contains the queries, forms, reports, macros, and mod(cid:173)
`ules. See Chapter 1 8 for details.
`
`Using Linked T~bles
`
`After linking a table from another database, you can use it almost like any other Access
`table. You can enter and update data, use existing queries, forms, and reports, or develop
`
`

`

`USING LINKED TABLES
`
`1221
`
`Another Reason
`to Use Wizards
`Objects created with Access Wizards are
`perfect candidates for linking or import(cid:173)
`ing into other Access databases that also
`contain objects created with those Wiz(cid:173)
`ards. For instance, if you use the Database
`Wizard or Table Wizard (with default set(cid:173)
`tings) to create Customers tables in two
`Access databases-Order Entry and
`
`Service Call Management, for example(cid:173)
`those tables will have the same structure
`in both databases. If you then import into
`the Service Call Management database a
`report that was designed to print the Cus(cid:173)
`tomers table in the Order Entry database,
`that report should work perfectly on the
`Customers table in the Service Order Pro(cid:173)
`cessing database.
`
`new ones. So even if the data resides in separate programs on separate computers, Access
`can use the external table almost as if you had created it from scratch in your open data(cid:173)
`base. The only real restrictions are
`
`• You can't change the structure of a linked table.
`• You can't delete rows from a linked Excel table or text file.
`• Lotus 1-2-3 files that you link to an Access database are read-only.
`
`After importing a table from another database, you can use it exactly like any other
`,.....,..,.. ... Access table. Access will never know that your imported table wasn't originally
`one of its own.
`
`Setting Properties of Linked Tables
`
`Although you can't add, delete, or rearrange fields of a linked table, you can set some
`table properties, including the Format, Input Mask, Decimal Places, and Caption. You
`change these properties in the table design view (see Chapter 6). In the database win(cid:173)
`dow you can right-click on the table name on the Tables tab, choose Properties, and
`change the Description property.
`
`Anytime you click in a General or Lookup properties box in the Field Properties area
`of the table design window, the Hint box in the right side of the window will tell
`you if the selected property cannot be changed.
`
`

`

`222
`
`CHAPTER 7 • LIN KING, IMPORTING, AND EXPORTING DATA
`
`......
`
`Renaming linked or Imported Tables
`Most objects will have their original file names when you import or link them to Access.
`After you've imported or linked such a file, however, you can give it a more descriptive
`name in your Access database window. For instance, you could rename an imported or
`linked dBASE table from CredCard to Credit Cards (From dBASE).
`To rename an imported or linked table quickly, right-click on its name in the data(cid:173)
`base window, choose Rename from the shortcut menu, type a new name, and then
`press Enter.
`
`Speeding Up linked Tables
`Although linked tables behave a lot like Access tables, they're not actually stored in
`your Access database. Each time you view linked data, Access must retrieve records
`from another file that may be on another computer in the network or in a SQL data(cid:173)
`base. You might grow some gray hairs waiting for this to happen.
`These guidelines can help speed up performance in a linked table on a network or
`SQL database:
`
`• Avoid jumping to the last record of a large table unless you need to add new records.
`• View only the data you absolutely need and don't scroll up and down unnecessarily.
`• Create a form that has the Data Entry property on the Data tab set to Yes. That way,
`if you frequently add new records to a linked table, Access won't bother to display
`any existing records from the table when you enter data (see Chapter 13). This data
`entry mode (also called add mode) can be much faster than opening the form in edit
`mode and jumping to the last record. (If you need to see all the records again,
`choose Records )o- Remove Filter/Sort.)
`
`Here are three more ways to open a form or table in data entry mode: (1) Choose
`Records >- Data Entry after opening a table or form; (2) choose Open Form In
`Add Mode as the Command for a form you've added to a switchboard with the
`Switchboard Manager (see Chapters 3 and 21 ); or (3) in a macro design window
`use the OpenForm action to open a form and set the action's Data Mode action
`argument to Add (see Chapter 20).
`
`• Use queries and filters to limit the number of records that you view in a form or
`datasheet.
`
`

`

`USING LINKED TABLES
`
`1223
`
`• Avoid using functions, especially domain-aggregate functions such as DSum( ),
`anywhere in your queries. These functions require Access to process all the data in
`the linked table. See Chapter- 10 for more about these functions.
`• Avoid locking records longer than you need to when sharing external tables with
`other users on a network. Hogging the locks will slow response time for others
`and make you unpopular in a hurry. See Chapter 18 for more about using Access
`on a network.
`
`Using the Linked Table Manager
`Access stores information about links to tables in your database. If you move the file
`that contains the linked table to another folder, Access won't be able to open the linked
`table. Fortunately, the Linked Table Manager can find the moved tables and fix things
`up quickly. Here's how to use it:
`
`1. Open the database that you've linked the objects to.
`2. Choose Tools ~ Add-Ins ~ Linked Table Manager to open the Linked Table Man(cid:173)
`ager dialog box (see Figure 7.5). This dialog box shows the linked objects and the
`full path names of the associated source files.
`3. Click on the appropriate checkboxes to select the linked tables you want to update.
`If you select a table accidentally, click on its checkbox again to deselect (clear) the
`box. Or click on the Select All or Deselect All buttons to check or clear all the check(cid:173)
`boxes in one step.
`4. Select (check) Always Prompt For New Location to force the Linked Table Manager
`to request the location for each table you checked in step 3.
`
`W#MQ;IUM
`The Linked Table
`Manager dialog
`box for a data(cid:173)
`base with sev-
`eral/inked files.
`
`•
`
`EJ
`~
`I
`
`ttl lmkcd Table Manage•
`
`P +Iilli Asset Categories
`[ ·\My Documents\Asset Twcking1 mdbl
`(;;: +Px
`• t
`r +Iilli E<pense C•legories (C:\My DocumentsiE<penses1 .mdb)
`r +Iilli E<pense Details
`(C:\ My DocumenlsiE<penses1 mdb)
`II +lilll E<pense Reports (C:\My Documents\E<penses1 mdb)
`r +dB Grades (C:\OFFICE\PDOXWIN\CONNECT\Grades dbf)
`
`r ~prompt for new location
`
`

`

`224
`
`CHAPTER 7 • LINKING, IMPORTING, AND EXPORTING DATA
`
`5. Click on OK.
`6. Usc the Select New Location Of dialog box that appears next to find the folder that
`contains the moved source table, and then double-click on the file name that con(cid:173)
`tains the linked table. Repeat this step as needed.
`
`The Select New Location Of dialog box works the same way as the Open, File New
`Database, Import, and Link dialog boxes you already know and love. See Chapters 1
`and 5 for techniques you can use in these dialog boxes.
`
`7. Click on OK when Access tells you that the links were refreshed successfully; then
`click on Close in the Linked Table Manager dialog box.
`
`What the Linked Table
`Manager Cannot Do
`
`The Linked Table Manager is great for
`finding files that have wandered off to a
`different folder or disk drive or have been
`renamed. However, if anyone has renamed
`the linked table or changed its password,
`Access won't be able to find the table and
`the Linked Table Manager won't be able to
`fix the problem. In these cases, you must
`
`delete the link and then relink the table with
`the correct table name and/or password.
`
`Also, don't expect the Linked Table
`Manager to move the database or table
`files for you. For that job, you'll need to use
`Windows Explorer or My Computer. Or go
`to any Access Open, File New Database,
`Link, Import, or Select New Location Of
`dialog box; then right-click on an empty
`area below the Look In list, choose Explore
`from the shortcut menu, and explore as
`you would with Windows Explorer.
`
`When you no longer neecl to use a linked table (or Access can't find it because someone
`has renamed it or changed its password), you can delete the link. To do this, open the
`database window that contains the linked table and click on the Tables tab. Then click
`on the table whose link you want to delete and press the Delete key. When asked if
`you're sure you want to delete the link, click on Yes.
`nemernber that cldeting a link deletes the information used to access the linked table,
`but it has absolutely no effect on the table itself. You can rdink the table at any time.
`
`

`

`IMPORTING OR LINKING SPREADSHEETS AND TEXT FILES
`
`225
`
`Importing or Linking Spreadsheets and Text Files
`
`You can import or link any of these spreadsheet and text formats into Access tables:
`
`• Microsoft Excel versions 2, 3, 4, and S, Excel 7 (also known as Excel for Windows 95
`or Excel 95), and Excel 8 (Excel 97).
`• Lotus 1-2-3 or 1-2-3 for Windows (.wk1, .wk3, and .wk4 files). Lotus files that are
`linked are read-only.
`• Delimited text (values are separated by commas, tabs, or other characters).
`• Fixed-width text, including Microsoft Word Merge (each field value is a certain
`width).
`
`When importing from a text file, you can create a new table or append the data to
`an existing table. If your spreadsheet or text file contains field names in the first row,
`Access can use them as field names in the table.
`Access looks at the first row of data and does its best to assign the appropriate data type
`for each field you import. For example, importing cells A1 through ClO from the spread(cid:173)
`sheet at the top of Figure 7.6 creates the Access table at the bottom of the figure. Here we
`
`•
`
`..
`.I[ )(
`las!. Hamel Fhst Nama I SalaJY
`I '
`Burns
`Joe
`m.o~.oo
`f-
`I Sunny
`$2B ,OQ