It is important that you understand when to import external data, when to link to external data, and when to open an external table directly. You should import external data in either of these circumstances:

When you are migrating an existing system to Access and you are ready to permanently migrate test or production data into your application, you import the tables into Access. Another good reason to import external data is if the data is downloaded from a mainframe into ASCII format on a regular basis, and you want to use the data for reports. Instead of attempting to link to the data and suffering the performance hits associated with such a link, you can import the data each time it is downloaded from the mainframe.

You should link to external data in any of the following circumstances:

Often, you won't have the time or resources to rewrite an application written in FoxPro, Paradox, or some other language. You might be developing additional applications that will share data with the legacy application, or you might want to use the strong querying and reporting capabilities of Access instead of developing queries and reports in the native environment.

By linking to the external data, users of existing applications can continue to work with the applications and their data. Your Access applications can retrieve and modify data without corrupting or harming the data.

If the data resides in an ODBC database such as Microsoft SQL Server, you want to reap the data-retrieval benefits provided by a database server. By linking to the ODBC data source, you can take advantage of Access's ease of use as a front-end tool, while taking advantage of client/server technology at the same time.

Finally, if you intend to access data on a regular basis, linking to the external table instead of temporarily opening the table directly provides you with ease of use and performance benefits. After you create the link, Access treats the table just like any other Access table.

You should open an external table directly in either of these circumstances:

If you rarely need to access the external data, it might be appropriate to open it directly. Links increase the size of your MDB file. This size increase is not necessary if you rarely will access the data. Furthermore, in certain situations, when accessing ISAM data, you might find that opening the table directly provides better performance than linking to it.

Although this chapter covers the process of importing external data, this is essentially a one-time process and doesn't require a lot of discussion. It is important to note, however, that after data is imported into an Access table, it no longer is accessed by the application in its native format. The majority of this chapter focuses on linking to or directly opening external data tables.

Microsoft Access enables you to import, link to, and open files in these formats:

To import an external data file using the user interface, follow these steps:

The DoCmd object has three methods that assist you with importing external data. They are TransferDatabase, TransferText, and TransferSpreadsheet, each of which is covered in this section.

Sub ImportDatabase()
  DoCmd.TransferDatabase _
    TransferType:=acImport, _
    DatabaseType:="FoxPro 2.5", _
    DatabaseName:=APPPATH,
    ObjectType:=acTable, _
    Source:="Customers", _
    Destination:="tblCustomers", _
    StructureOnly:=False
End Sub

Sub ImportText()
  DoCmd.TransferText _
    TransferType:=acImportDelim, _
    TableName:="tblCustomerText", _
    FileName:=APPPATH & "Customer.Txt"
End Sub

Sub ImportSpreadsheet()
  DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=5, _
    TableName:="tblCustomerSpread", _
    FileName:=APPPATH & "Customer.Xls",
    HasFieldNames:=True
End Sub

It is very common to create a link using the user interface. If you know what links you want to establish at design time, this is probably the easiest way to establish links to external data. You can establish links by using the Database Splitter or by establishing them manually.

Creating Links to Other Types of Tables

The process of creating links to other types of database files is a little different. It works like this:

1. Right-click on any tab of the Database window.

2. Choose Link Tables. The Link dialog box appears.

3. From the Files of Type drop-down list, select the type of table to which you are linking.

4. Select the external file containing the data to which you will be linking.

5. Click the Link button. The next dialog box that appears varies, depending on the type of table to which you want to link. With a FoxPro file, for example, the Select Index Files dialog box appears, as shown in Figure 19.6. It is important that you select any index files associated with the data file. These indexes are updated automatically by Access as you add, change, and delete table data from Access.

   

   Figure 19.6. The Select Index Files dialog box.

6. You receive a message indicating that the index was added successfully and that you can add other indexes if you choose. Click OK.

7. Add any additional indexes and click Close.

8. The Select Unique Record Identifier dialog box appears, as shown in Figure 19.7. This dialog box enables you to select a unique identifier for each record in the table. Select a unique field and click OK.

   

   Figure 19.7. The Select Unique Record Identifier dialog box.

Notice the icon indicating the type of file you linked to, as shown in Figure 19.8.

Figure 19.8. An icon indicating that the file database is linked to a FoxPro database file.

Creating a link to an external table using code is a five-step process. Here are the steps involved in establishing the link:

Listing 19.4 shows the code for linking to an external table, which in this case exists in another Microsoft Access database.

Sub LinkToAccessTableProps()
   Dim db As DATABASE
   Dim tdf As TableDef
   Set db = CurrentDb
   Set tdf = db.CreateTableDef("tblLinkedTable")
   tdf.Connect = ";Database=" & APPPATH & "Chap19Data.MDB"
   tdf.SourceTableName = "tblClients"
   db.TableDefs.Append tdf
End Sub

In Listing 19.4, the source database (Chap19Data.MDB) does not need to be opened, because you are adding a table definition to the current database. The CreateTableDef method is used to create the new table definition. The Connect property is set, and the SourceTableName is defined. Finally, the table definition is appended to the TableDefs collection of the database. This process is discussed in further detail in the following sections.

Set tdf = db.CreateTableDef("tblLinkedTable")
tdf.Connect = ";Database=" & APPPATH & "Chap19Data.MDB"
tdf.SourceTableName = "tblClients"

Set tdf = db.CreateTableDef("tblLinkedTable", _
    0, "tblClients", _
    ";Database=" & APPPATH & "Chap19Data")

Database=c:DatabasesChap19Data

\FILESERVERNAMEDatabasesChap19Data

tdf.Connect = "FoxPro 2.6;Database=c:Databases;PWD="

Sub LinkToFox25(strDirName As String, strTableName As String, strAccessTable)
   Dim db As DATABASE
   Dim tdf As TableDef
   Set db = CurrentDb
   Set tdf = db.CreateTableDef(strAccessTable)
   tdf.Connect = "FoxPro 2.5;Database=" & strDirName
   tdf.SourceTableName = strTableName
   db.TableDefs.Append tdf
End Sub

Call LinkToFox25("c:customerdata","customer","tblCustomers")

Sub LinkToFoxAlt(strDirName As String, strTableName As String, strAccessTable)
   Dim db As DATABASE
   Dim tdf As TableDef
   Set db = CurrentDb
   Set tdf = db.CreateTableDef(strAccessTable, 0, strTableName, _
      "FoxPro 2.5;Database=" & strDirName)
   db.TableDefs.Append tdf
End Sub

Sub LinkToAccess(strDBName As String, strTableName As String, strAccessTable)
   Dim db As DATABASE
   Dim tdf As TableDef
   Set db = CurrentDb
   Set tdf = db.CreateTableDef(strAccessTable)
   tdf.Connect = ";DATABASE=" & strDBName
   tdf.SourceTableName = strTableName
   db.TableDefs.Append tdf
End Sub

Call LinkToAccess("C:databases
orthwind","Customers","tblCustomers")

The connection information you provide when you open an external table is similar to the information you provide when you link to the table. The connection information is provided as arguments of the OpenDatabase method. Here's an example:

OpenDatabase("c:customerdata", False, False, "FoxPro 2.5")

Here, the connection string is to the c:customerdata database using the FoxPro 2.5 ISAM.

The OpenDatabase method receives the following arguments:

OpenDatabase(DBname, Exclusive, Read-Only, Source)

DBname is the name of the database you are opening. The Exclusive and Read-Only parameters specify whether you are opening the database exclusively or as read-only. The Source argument specifies the database type and connection string. Listing 19.8 shows what the OpenDatabase method looks like in code.

Sub OpenExternalFox(strDBName As String, strTableName As String)
   Dim db As DATABASE
   Dim rst As Recordset
   Set db = DBEngine.Workspaces(0).OpenDatabase(strDBName, False, _
      False, "FoxPro 2.5")
   Set rst = db.OpenRecordset(strTableName)
   Do While Not rst.EOF
      Debug.Print rst.Fields(0).Value
      rst.MoveNext
   Loop
End Sub

Listing 19.8 is called with this code:

Call OpenExternalFox("c:customerdata","Customer")

Notice that you are not appending a table definition here. Instead, you are creating a temporary recordset that refers to the external data. After the external table is opened as a recordset, the code traverses through each record of the table, printing the value of the first field. Of course, after the recordset is opened, you can manipulate it in any way you want. The table does not show up as a linked table in the database window. In fact, when the routine completes and the local variable goes out of scope, the recordset no longer exists.

Now that you have seen how you can link to external tables as well as open them, you are ready to take a look at how you can refine both these processes. This involves learning the Windows Registry settings that affect the linking process, learning more about the parameters that are available to you when specifying connection information, learning how to specify passwords, learning how to refresh and remove links, and learning how to create an external table using VBA code.

To refresh a link using VBA code, follow these steps:

Listing 19.11 shows the code to refresh a link.

Sub RefreshLink()
   Dim db As Database
   Set db = CurrentDb
   db.TableDefs!FoxCusts.Connect = "FoxPro 2.6;DATABASE=d:
ewdir"
   db.TableDefs!FoxCusts.RefreshLink
End Sub

You can modify this routine to prompt the user for the directory containing the data tables, as Listing 19.12 shows.

Sub RefreshLink()
   On Error GoTo RefreshLink_Err
   Dim db As DATABASE
   Dim tdf As TableDef
   Dim strNewLocation As String
   Set db = CurrentDb
   Set tdf = db.TableDefs("tblClients")
   tdf.RefreshLink
   Exit Sub

RefreshLink_Err:
   strNewLocation = InputBox("Please Enter Database Path and Name")
   db.TableDefs!tblClients.Connect = ";DATABASE=" & strNewLocation
   Resume
End Sub

This routine points a TableDef object to the tblClients table. It then issues a RefreshLink method on the table definition object. The RefreshLink method attempts to refresh the link for the table. If an error occurs, an input box prompts the user for the new location of the database. The Connect property for the database is modified to incorporate the new location. The code then resumes on the offending line of code (the RefreshLink). You should modify this routine to give the user a way out. Resume throws the user into an endless loop if the database is not available. An enhanced routine (Listing 19.15) is presented later in the “Practical Examples: Working with External Data from within Your Application” section of this chapter.

To remove a link using VBA code, simply execute a Delete method on the Table Definition collection of the database, as shown in Listing 19.13.

Sub RemoveLink()
   Dim db As Database
   Set db = CurrentDb
   db.TableDefs.Delete "FOXCUSTS"
End Sub

The major concerns you will have when dealing with dBASE files surround deleted records, indexes, data types, and Memo fields. When you delete a record from a dBASE table, it is not actually removed from the table. Instead, it is just marked for deletion. A Pack process must be completed in order for the records to actually be removed from the table. If records are deleted from a dBASE table using an Access application, the records are not removed. Because you cannot pack a dBASE database from within an Access application, the records still remain in the table. In fact, they are not automatically filtered from the Access table. In order to filter deleted records so that they cannot be seen in the Access application, the deleted value in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet3.5EnginesXbase section of the Registry must be set to 01 (True).

Access can use the dBASE indexes to improve performance. After you link to a dBASE table and select an index, an INF file is created. This file has the same name as your dBASE database with an INF extension. It contains information about all the indexes being used. Here's an example of an INF:

[dBASE III]
NDX1=CUSTID.NDX
UNDX1=CUSTID.NDX

dBASE III is the database type identifier. NDX1 is an index number for the first index. The UNDX1 entry specifies a unique index.

The data types available in dBASE files are different from those available in Access files. It is important to understand how the field types are mapped. Table 19.4 shows how each dBASE data type is mapped to a Jet data type.

Finally, it is important to ensure that the dBASE memo files are stored in the same directory as the table. Otherwise, Access is unable to read the data in the memo file.

Like dBASE files, the major concerns you will have when dealing with FoxPro files surround deleted records, indexes, data types, and Memo fields. You handle deleted records in the same way you handle dBASE files. You filter deleted records by setting the deleted value in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet3.5EnginesXbase section of the Registry to 01.

As with dBASE indexes, the Access Jet Engine can take advantage of FoxPro indexes. The format of an INF file for a FoxPro file is identical to that of a dBASE file.

FoxPro field types are mapped to Jet field types in the same way that dBASE fields are mapped. The only difference is that FoxPro 3.0 supports Double, Currency, Integer, and DateTime field types. These map to the corresponding Jet field types. As with dBASE, make sure that the Memo files are stored in the same directory as the data tables.

When linking to an ASCII text file, Jet can determine the format of the file directly, or it can use a schema information file, which resides in the same directory as the text file. It always is named SCHEMA.INI, and it contains information about the format of the file, the column names, and the data types. The schema information file is optional for delimited files, but it is required for fixed-length files. It is important to understand that ASCII files can never be opened for shared use.

Difficulties with accessing external data can be caused by any of the following circumstances:

Access requires a significant amount of disk space in order to run complex queries on large tables. This disk space is required whether the tables are linked tables stored remotely in another format, or they reside on the local machine. If not enough disk space is available to run a query, the application behaves unpredictably. It therefore is necessary to ensure that all users have enough disk space to meet the requirements of the queries that are run.

Begin by using the Database Splitter to separate the tables from the rest of the database objects. The CHAP19.MDB and CHAP19DATA.MDB files are on the sample code CD-ROM. The CHAP19DATA.MDB file contains all the tables, and CHAP19.MDB contains the rest of the database objects.

If you distributed the Time and Billing application and all users did not have the same path to the CHAP19DATA.MDB file, the application would not load successfully. The AutoExec function ensures that the tables are successfully linked, as Listing 19.15 shows.

Function AutoExec()
  On Error GoTo AutoExec_Err:

  Dim fAnswer As Boolean
  Dim dblStartTime As Double
  Dim dblTimeElapsed As Double

  'Open splash screen form
  DoCmd.OpenForm "frmSplash"
  DoEvents
  'Invoke hourglass
  DoCmd.Hourglass True
  'Call routine that checks if tables are properly attached
  fAnswer = AreTablesAttached()
  'Test return value and proceed only if tables were
  'successfully attached
  If Not fAnswer Then
    MsgBox "You Cannot Run This App Without Locating Data Tables"
    DoCmd.Close acForm, "frmSplash"
    DoCmd.Close acForm, "frmGetTables"
  End If
  Call GetCompanyInfo
  DoCmd.Hourglass False
  DoCmd.OpenForm "frmClients"
  'If splash screen is still loaded, unload it
  If IsLoaded("frmSplash") Then
    DoCmd.Close acForm, "frmSplash"
  End If
  Exit Sub

AutoExec_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Exit Sub
End Function

Notice that the AreTablesAttached routine is called from the AutoExec routine. If the AreTablesAttached routine returns False, a message is displayed and the application is exited. Listing 19.16 shows the AreTablesAttached routine.

Function AreTablesAttached() As Boolean
   ' Update connection information in attached tables.
   '
   ' Number of attached tables for progress meter.
   Const MAXTABLES = 8
   Const NONEXISTENT_TABLE = 3011
   Const DB_NOT_FOUND = 3024
   Const ACCESS_DENIED = 3051
   Const READ_ONLY_DATABASE = 3027

   Dim intTableCount As Integer
   Dim intResponse As Integer
   Dim strFilename As String
   Dim strAppDir As String
   Dim vntReturnValue As Variant
   Dim tdf As TableDef
   Dim db As DATABASE
   Dim rst As Recordset

   Set db = CurrentDb

   AreTablesAttached = True
   ' Continue if attachments are broken.
   On Error Resume Next
   ' Open attached table to see if connection information is correct.
   Set rst = db.OpenRecordset("tblClients")
   ' Exit if connection information is correct.
   If Err.Number = 0 Then
    rst.Close
    Exit Function
   Else
    'Otherwise, determine location of current database
    strAppDir = Left(db.Name, LastOccurence(db.Name, ""))
    'Try to establish the connection searching for the linked
    'tables in the same folder as the application database
    If TryAgain(strAppDir) Then
       rst.Close
       Exit Function
    End If
    'If connection still cannot be established, continue
    'Warn the user
    MsgBox "You Must Locate the Data Tables"
    DoEvents
    Forms!frmGetTables!dlgCommon.DialogTitle = _
        "Please Locate the Database Containing the Data Tables"
    Forms!frmGetTables!dlgCommon.ShowOpen
    strFilename = Forms!frmLogon!dlgCommon.filename
   End If
   If strFilename = "" Then
      GoTo Exit_Failed ' User pressed Cancel.
   End If
   ' Initialize progress meter.
   vntReturnValue = SysCmd(SYSCMD_INITMETER, "Attaching tables", MAXTABLES)

   ' Loop through all tables, reattaching those with
   ' nonzero-length Connect strings.
   intTableCount = 1 ' Initialize TableCount for status meter.
   For Each tdf In db.TableDefs
      If tdf.Connect <> "" Then
         tdf.Connect = ";DATABASE=" & strFilename
         Err.Number = 0
         tdf.RefreshLink
         If Err.Number <> 0 Then
           If Err.Number = NONEXISTENT_TABLE Then
             MsgBox "File '" & strFilename & _
                "' does not contain required table '" & _
                tdf.SourceTableName & "'", 16, "Can't Run This App"
           ElseIf Err.Number = DB_NOT_FOUND Then
             MsgBox "You can't run FSG Main Application " & vbCrLf & _
                "Until you locate Data File", 16, "Can't Run Application"
           ElseIf Err.Number = ACCESS_DENIED Then
             MsgBox "Couldn't open " & strFilename & _
                " because it is read-only or it is located " & _
                "on a read-only share.", 16, "Can't Run This App"
           ElseIf Err.Number = READ_ONLY_DATABASE Then
             MsgBox "Can't reattach tables because Data File " & _
                "is read-only or is located on a read-only share.", _
                16, "Can't Run This App"
           Else
             MsgBox Error, 16, "Can't Run This App"
           End If
           intResponse = MsgBox(tdf.Name & " Not Found. " & _
             vbCrLf & "Would You Like to Locate it?", _
             vbQuestion + vbYesNo)
           If intResponse = vbYes Then
             Forms!frmLogon!dlgCommon.DialogTitle = "Please Locate " & _
                tdf.Name
             Forms!frmLogon!dlgCommon.ShowOpen
             strFilename = Forms!frmLogon!dlgCommon.filename
           Else
             AreTablesAttached = False
             GoTo Exit_Final
           End If
         End If
         intTableCount = intTableCount + 1
         vntReturnValue = SysCmd(SYSCMD_UPDATEMETER, intTableCount)
      End If
   Next tdf
   GoTo Exit_Final
Exit_Failed:
   MsgBox "You can't run this example until " & _
    "you locate Data File", 16, "Can't Run This Example"
   AreTablesAttached = False
Exit_Final:
   vntReturnValue = SysCmd(SYSCMD_REMOVEMETER)
End Function

The AreTablesAttached function begins by pointing the DB object variable to the current database and setting its default return value to True. It issues an On Error Resume Next statement, instructing Access to proceed with the next line of code if an error is encountered. Next, it attempts to open a recordset based on the tblClients table. If the tables are not properly linked, an error occurs. The If Err.Number = 0 statement tests to see whether an error occurred. If the Err.Number is equal to 0, no error occurred and the function can be exited. Otherwise, the application attempts to locate the data file in the directory that contains the application database. If this is not successful, the ShowOpen method is applied to a Common Dialog control, which is placed on a hidden form called frmGetTables.

The Common Dialog control is an ActiveX control that is included as part of the Office Developer Edition Tools for Windows 95. It is covered in detail in Chapter 26, “Using ActiveX Controls.” In this case, the control invokes the File Open common dialog box. The filename the user selects in the File Open dialog box is returned to the variable strFilename. After a potential new location for the tables is identified by the user, Access attempts to issue the RefreshLink method on each table found in the Table Definitions collection of the current database. If the link to any table is not refreshed properly, another error occurs and a return value of False is returned from the AreTablesAttached function.