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. You might also want to import external data 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 concern for corrupting, or in any other way 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.

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, in most cases, 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 (indexed sequential access method) 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.

Importing Database Data Using Code

You use the TransferDatabase method of the DoCmd object to import data from a database such as FoxPro, dBASE, Paradox, or another Access database. Listing 19.1, included in basImport, shows an example that uses the TransferDatabase method.

Example 19.1. Using the TransferDatabase Method

Sub ImportDatabase()
  DoCmd.TransferDatabase _
    TransferType:=acImport, _
    DatabaseType:="dBASE III", _
    DatabaseName:= CurrentProject.Path, _
    ObjectType:=acTable, _
    Source:="Customer", _
    Destination:="tblCustomers", _
    StructureOnly:=False
End Sub

Note

This code and all the code in this chapter are located in the CHAP19EX.MDB file on the sample code CD-ROM.

Table 19.1 lists the arguments for the TransferDatabase method.

Table 19.1. TransferDatabase Arguments

Argument	Specifies
TransferType	Type of transfer being performed.
DatabaseType	Type of database being imported.
DatabaseName	Name of the database. If the table is a separate file (as is the case with dBASE, Paradox, and earlier versions of FoxPro), the database name is the name of the directory that contains the table file. Do not include a backslash after the name of the directory.
ObjectType	Type of object you want to import. This argument is ignored for all but Access objects.
Source	Name of the object you are importing. Do not include the file extension.
Destination	Name of the imported object.
StructureOnly	Whether you want the structure of the table only or the structure and data.
StoreLogin	Whether you want to save the login ID and password for an ODBC database in the connection string for linked tables.

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

Sub ImportSpreadsheet()
  DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:="tblCustomerSpread", _
    FileName:=CurrentProject.Path & "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 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 anywhere within the Database window.

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

3. In 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 varies, depending on the type of table to which you want to link. With a dBASE 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 within 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 (see Note below if the dialog doesn’t appear). 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.

Note

You must install the Jet 4.0 Service Pack 5 or have the BDE (Borland Database Engine) for the Select Unique Record Identifier dialog box to appear. If you do not have one of these tools, the data in the database cannot be edited. For more information, see the Microsoft knowledge base article Q283294.

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 dBASE database file.

Note

Earlier versions of Access supported links to FoxPro files using the FoxPro ISAM driver. With Jet 4.0, you must link to FoxPro tables using the Visual FoxPro ODBC Driver.

Creating a link to an external table using code is a six-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 cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    'Instantiate a Catalog Object
    Set cat = New ADOX.Catalog

    'Set the connection of the Catalog object
    'to the connection associated with the current
    'project
    cat.ActiveConnection = CurrentProject.Connection

    'Instantiate a table object
    Set tbl = New ADOX.Table

    'Establish the name of the new table object
    tbl.Name = "tblLinkedTable"

    'Point the catalog of the new table at the
    'catalog object established above
    Set tbl.ParentCatalog = cat

    'Set necessary properties of the new table object
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = _
        CurrentProject.Path & "Chap19Data.mdb"
    tbl.Properties("Jet OLEDB:Link Provider String") = ";pwd=password"
    tbl.Properties("Jet OLEDB:Remote Table Name") = "tblClients"

    'Append the new table object to the tables collection
    'of the catalog object
    cat.Tables.Append tbl
End Sub

In Listing 19.4, a Catalog object is created. The ActiveConnection property of the Catalog object is pointed at the connection associated with the current database. Next, a Table object is created. The Name property of the Table object is set equal to tblLinkedTable. The ParentCatalog property of the Table object is set to point at the Catalog object. Four properties in the properties collection of the Table object are set to the appropriate values, and the Table object is appended to the Catalog object. This process is discussed in further detail in the following sections.

tbl.Properties("Jet OLEDB:Link Provider String") = ";pwd=password"
tbl.Properties("Jet OLEDB:Remote Table Name") = "tblClients"
tbl.Properties("Jet OLEDB:Link Datasource") = CurrentProject.Path &
"Chap19Data.mdb"

tbl.Properties("Jet OLEDB:Link Datasource") = "c:DatabasesChap19Data"

tbl.Properties("Jet OLEDB:Link Datasource") = _
    "\FILESERVERNAMEDatabasesChap19Data"

Sub LinkToDBase(strDirName As String, strTableName As String, _
   strAccessTable)
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    'Instantiate a Catalog object
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection

    'Instantiate a Table object
    Set tbl = New ADOX.Table
    tbl.Name = strAccessTable
    Set tbl.ParentCatalog = cat

    'Set necessary properties of the new Table object
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = strDirName
    tbl.Properties("Jet OLEDB:Link Provider String") = & _
    "dBASE III;HDR=NO;IMEX=2;"
    tbl.Properties("Jet OLEDB:Remote Table Name") = strTableName

    'Append the new table object to the tables collection
    'of the catalog object
    cat.Tables.Append tbl
End Sub

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

Sub LinkToAccess(strDBName As String, strTableName As String, _
   strAccessTable)
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    'Instantiate a Catalog object
    Set cat = New ADOX.Catalog

    'Set the ActiveConnection property of the Catalog object
    'to the connection associated with the current project
    cat.ActiveConnection = CurrentProject.Connection

    'Instantiate a Table object
    Set tbl = New ADOX.Table

    'Set the Name property of the Table object to the name
    'you wish to give to the linked table
    tbl.Name = strAccessTable

    'Set the ParentCatalog property of the Table object
    'to the Catalog object
    Set tbl.ParentCatalog = cat

    'Set all necessary properties of the Table object
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Link Datasource") = strDBName
    tbl.Properties("Jet OLEDB:Link Provider String") = ";pwd=password"
    tbl.Properties("Jet OLEDB:Remote Table Name") = strTableName

    'Append the Table object to the Tables collection
    'associated with the CatalogObject
    cat.Tables.Append tbl
End Sub

Call LinkToAccess("C:databases
orthwind.mdb","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 the ConnectionString argument of the Open method of the Connection object. Here’s an example:

cnn.Open "Providersqlodedb;" & _
        "Data Source=(local);" & _ _
        "Initial Catalog=Pubs;" & _
        "User ID=sa;Password=;"

Here, the connection string is to the SQL Server database called Pubs on the local machine.

The Recordset object is pointed at the result of executing a Select statement against the Connection object:

Set rst = cnn.EXEcute("Select * from Authors")

Listing 19.7 shows what the entire process looks like in code.

Sub OpenExternalSQL(strDBName As String, strTableName As String)
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset

    'Instantiate Connection and Recordset objects
    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset

    'Use the Open method of the Connection object to establish
    'a connection to the SQL Server database
    cnn.Open "Provider=sqloledb;" & _
        "Data Source=(local);" & _
        "Initial Catalog=" & strDBName & ";" & _
        "User Id=sa;Password=; "


    'Use the Execute method of the Connection object to execute
    'a Select statement and return the result as a Recordset
    Set rst = cnn.Execute("Select * from " & strTableName)

    'Loop through the resulting recordset,
    'printing the value of the first field
    Do Until rst.EOF
        Debug.Print rst.Fields(0).Value
        rst.MoveNext
    Loop

    'Close the Connection
    cnn.Close
End Sub

Listing 19.7 is called with this code:

Call OpenExternalSQL("Pubs","authors")

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, simply redefine the Jet OLEDB:Link Datasource. Listing 19.10 shows the code to refresh a link.

Sub RefreshLink()
    Dim cat As ADOX.Catalog
    Dim tdf As ADOX.Table

    Set cat = New ADOX.Catalog
    Set cat.ActiveConnection = CurrentProject.Connection
    tdf.Properties("Jet OLEDB:Link Datasource") = _
        strNewLocation
End Sub

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

Sub RefreshLink()
    'Initiate error handling
    On Error GoTo RefreshLink_Err
    Dim cat As ADOX.Catalog
    Dim tdf As ADOX.Table
    Dim strNewLocation As String
    Dim strTemp As String

    'Instantiate a Catalog object
    Set cat = New ADOX.Catalog

    'Set the ActiveConnection property of the Catalog
    'object to the connection associated with the
    'current project
    Set cat.ActiveConnection = CurrentProject.Connection
    'Point the TableeDef object at the tblClients table
    Set tdf = cat.Tables("tblClients")

    'Attempt to retrieve the Name property of the table
    strTemp = tdf.Columns(0).Name

    'Exit the routine if all goes well
    Exit Sub

RefreshLink_Err:

    'If an error occurs, prompt the user for the new name
    'and location
    strNewLocation = InputBox("Please Enter Database Path and Name")

    'Set the properties of the TableDef object to the
    'information provided by the user
    tdf.Properties("Jet OLEDB:Link Datasource") = _
        strNewLocation
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tdf = cat.Tables("tblClients")

    'Try to grab the name property again
    Resume
End Sub

This routine points a Table object to the tblClients table. It then attempts to access the name of the first column in the table. If an error occurs, an input box prompts the user for the new location of the database. The Jet OLEDB:Link Datasource property for the database is modified to incorporate the new location. The code then resumes on the offending line of code. 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 (see Listing 19.13) is presented later in the “Practical Examples” section of this chapter.

To remove a link using VBA code, simply execute a Delete method of the Tables collection of a Catalog object connected to the database, as shown in Listing 19.12.

Sub RemoveLink()
    Dim cat As Catalog

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection

    cat.Tables.Delete ("tblClients")

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. You must Pack the database (a process in a dBASE table that removes deleted rows) 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 within the Access application, the Deleted value in the HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesXbase section of the registry must be set to 01 (True).

Access can use the dBASE indexes to improve performance (Jet 4.0 Service Pack 5 the Borland Database Engine must be installed). 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, make sure 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.

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. The application behaves unpredictably if not enough disk space is available to run the query. It therefore is necessary to make sure that all users have enough disk space to meet the requirements of the queries that are run.

Begin by using the Database Splitter (covered in Chapter 20, “Developing Multiuser and Enterprise Applications”) to separate the tables from the rest of the database objects. The CHAP19EX.MDB and CHAP19DATA.MDB files are included on the sample code CD-ROM. The CHAP19DATA.MDB file contains all the tables, and CHAP19EX.MDB contains the rest of the database objects.

If you distribute your application and all users did not have the same path to the CHAP19DATA.MDB file, the application would not load successfully. The LinkTables routine, located in the Switchboard startup form, ensures that the tables are successfully linked, as Listing 19.13 shows.

Sub LinkTables()
    Dim objFileDialog As FileDialog

    On Error GoTo LinkTables_Err:

    DoCmd.Hourglass True

    'Determine if links are ok
    If Not VerifyLink Then

        'If not ok, attempt to relink with expected file name
        If Not ReLink(CurrentProject.FullName, True) Then

            'If still not ok, ask user to locate file
            Set objFileDialog = FileDialog(msoFileDialogOpen)

            With objFileDialog
                .AllowMultiSelect = False
                .Show
            End With

            'Attempt to link to file that user selected
            If Not ReLink(objFileDialog.SelectedItems(1), False) Then

                'If not successful, display a message a quit app
                MsgBox "You Cannot Run This App Without " & _
                   "Locating Data Tables"
                DoCmd.Close acForm, "frmSplash"
                DoCmd.Quit
            End If
        End If
    End If

    DoCmd.Hourglass False
    Exit Sub

LinkTables_Err:
    DoCmd.Hourglass False
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Exit Sub
End Sub

Notice that the VerifyLink routine is called from the LinkTables routine. The VerifyLink routine appears in Listing 19.14.

Function VerifyLink() As Boolean
    'Verify connection information in linked tables.

    'Declare Required Variables
    Dim cat As ADOX.Catalog
    Dim tdf As ADOX.Table
    Dim strTemp As String

    'Point Database object variable at the current database
    Set cat = New ADOX.Catalog

    With cat
        Set .ActiveConnection = CurrentProject.Connection

        'Continue if links are broken.
        On Error Resume Next

        'Open one linked table to see if connection
        'information is correct.
        For Each tdf In .Tables
            If tdf.Type = "LINK" Then
                strTemp = tdf.Columns(0).Name
                If Err.Number Then
                    Exit For
                End If
            End If

        Next tdf
    End With

    VerifyLink = (Err.Number = 0)

End Function

The routine begins by pointing the ActiveConnection property of the Catalog object to the connection associated with the current database. It then loops through each table in the Tables collection of the Catalog object. If the table is a linked table, it attempts to access the name of the first column in the table. If any of the links are broken, an error occurs, and the For...Each loop is exited. If no error occurs, the function returns True; otherwise, the function returns False.

If the VerifyLink routine returns False, the ReLink routine is called. Listing 19.15 shows the ReLink routine.

Function ReLink(strDir As String, DefaultData As Boolean) _
    As Boolean

    ' Relink a broken linked Access table.

    Dim cat As ADOX.Catalog
    Dim tdfRelink As ADOX.Table
    Dim oDBInfo As DBInfo
    Dim strPath As String
    Dim strName As String
    Dim intCounter As Integer
    Dim vntStatus As Variant

    'Prepare status bar
    vntStatus = SysCmd(acSysCmdSetStatus, "Updating Links")

    Set cat = New ADOX.Catalog

    'Instantiate database information class
    Set oDBInfo = New DBInfo
    With cat
        .ActiveConnection = CurrentProject.Connection

        'Extract the name and path from the passed database
        oDBInfo.FullName = strDir
        strPath = oDBInfo.FilePathOnly
        strName = Left(oDBInfo.FileName, InStr(oDBInfo.FileName, ".") - 1)

        On Error Resume Next
        'Update progress meter
        Call SysCmd(acSysCmdInitMeter, "Linking Data Tables", .Tables.Count)

        'Loop through each table, attempting to update the link
        For Each tdfRelink In .Tables
            intCounter = intCounter + 1
            Call SysCmd(acSysCmdUpdateMeter, intCounter)
            If .Tables(tdfRelink.Name).Type = "LINK" And _
                Left(tdfRelink.Name, 3) = "tbl" Then
                tdfRelink.Properties("Jet OLEDB:Link Datasource") = _
                strPath & strName & IIf(DefaultData, "Data.Mdb", ".mdb")
           End If
           If Err.Number Then
                Exit For
            End If
        Next tdfRelink
    End With

    'Reset the progress meter
    Call SysCmd(acSysCmdRemoveMeter)

    vntStatus = SysCmd(acSysCmdClearStatus)

    ReLink = (Err = 0)
End Function

The ReLink function receives two parameters. The first parameter is the name of the database the function will attempt to link to. The second parameter is a Boolean variable that designates whether the database is considered the default database.

The function begins by modifying the status bar. It then creates a Catalog object and an instance of a custom class called DBInfo. This custom class is covered in further detail in Chapter 32, “Distributing Your Application.” Class modules are covered in Chapter 13, “Exploiting the Power of Class Modules.” The ActiveConnection property of the catalog object is set equal to the Connection property of the current project. Next, the FullName property of the DBInfo class is set equal to the name of the file that is passed as a parameter to the function. The DBInfo class extracts the path and the filename from the full filename. Just as with the VerifyLink function, the ReLink function uses a For...Next loop to loop through all the tables in the database. As it loops through each table, it attempts to establish a link to a database with the name passed as parameter to the ReLink function.

This ReLink function is called twice from the LinkTables routine, shown in Listing 19.13. The first time it is passed, the FullName property of the CurrentProject object and the boolean is True, indicating that it will try to locate the table in a database with the same location as the application database. If that attempt is not successful, the LinkTables routine uses the FileDialog object to display the File Open dialog, allowing the user to attempt to locate the database. The ReLink function is called again, searching for the table in the database selected by the user. If it is still unsuccessful, the routine quits the application.