| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
Microsoft Access is very capable of interfacing with data from other sources. It can utilize data from any ODBC data source, as well as data from FoxPro, dBASE, Paradox, Lotus, Excel, and many other sources. In this chapter, you will learn how to interface with external data sources both with the user interface, and using code.
External data is data stored outside the current database. It can refer to data stored in another Microsoft Access database, as well as data stored in a multitude of other file formats including ODBC, ISAM, spreadsheet, ASCII, and more. This chapter focuses on accessing data sources other than ODBC data sources. ODBC data sources are covered extensively in Chapter 20, "Client/Server Techniques."
Access is an excellent "front-end" product, which means that it provides a powerful and effective means of presenting data—even data from external sources. Data is stored in places besides Access for many reasons. For example, large databases can be more effectively managed on a back-end database server such as Microsoft SQL Server. Data is often stored in a FoxPro, dBASE, or Paradox file format because the data is being used by a legacy application written in one of those environments. Text data has often been downloaded from a mainframe. Regardless of the reason why data is stored in another format, it is necessary that you understand how to manipulate this external data within your VBA modules. When you are able to access data from other sources, you can create queries, forms, and reports, utilizing the data.
In accessing external data, you have three choices. You can import the data into an Access database, access the data by linking to it from within your Access database, or open a data source directly. As you will learn in the section titled "Importing, Linking, and Opening: When and Why," importing the data is optimal (except with ODBC data sources) but not always possible. Short of importing external data, you should link to external files because Microsoft Access maintains a lot of information about these linked files. This optimizes performance when manipulating the external files. Sometimes a particular situation warrants accessing the data directly. It is therefore necessary to know how to work with linked files, as well as how to open and manipulate files directly.
Many of the examples in this chapter use data stored in the ISAM file format, which includes files created in FoxPro, dBASE, and Paradox. If you performed a standard installation of Access 95, you probably did not install the drivers necessary to communicate with an ISAM file. You need to rerun setup and select the ISAMs check box if you want to perform many of the exercises covered in this chapter.
The process of importing data into an Access table makes a copy of the data, placing the copy within an Access table. After data is imported, it is treated like any other native Access table. In fact, neither you nor Access has any way of knowing where the data came from. As a result, imported data offers the same performance and flexibility as any other Access table.
The process of linking to external data is quite different from the process of importing data. Linked data remains in its native format. By establishing a link to the external data, you are able to build queries, forms, and reports that present the data. After you have created a link to external data, the link remains permanently established unless you explicitly remove it. The linked table appears in the database window just like any other Access table. The only difference is that you cannot modify its structure from within Access. In fact, if the data source permits multiuser access, the users of your application can be modifying the data along with users of the applications written in its native database format (such as FoxPro, dBASE, or Paradox).
The process of opening an external table is similar to linking to the table, except that a permanent relationship is not created. When you link to an external table, connection information is maintained from session to session. When you open the table, you create a recordset from the table and no permanent link is established.
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 under the following 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 utilize the data for reports. Rather than attempting to link to the data and suffer 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 under 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 utilize the strong querying and reporting capabilities of Access rather than 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. (See the warning that follows.) 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 rather than temporarily opening the table directly provides you with ease of use and performance benefits. When you have created the link, Access treats the table just like any other Access table.
Access can corrupt linked data when it rewrites data to the linked file.
You should open an external table directly under the following 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 will rarely 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, it focuses on linking to or directly opening external data tables rather than importing them. The chapter focuses on linking to external data because the import process is a one-time process. When data is imported into an Access table, it is no longer accessed by the application in its native format.
Microsoft Access enables you to import, link to, and open files in the following formats:
The process of importing external data is quite simple. You can import external data using the user interface or by using VBA code. If you are planning to import the data only once or twice, you should utilize the user interface. If you are importing data on a regular basis—for example, from a downloaded mainframe file—you should write code that accomplishes the task transparently to the user.
To import an external data file using the user interface, follow these steps:
Figure 19.1. The Import dialog.
Figure 19.2. The Import Spreadsheet Wizard.
Although this procedure is correct, if you find that you can't bring a text file directly into a large (4M to 5M) Access database, change the text file into an Excel spreadsheet first and then import that file.
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.
The TransferDatabase method of the DoCmd object is used to import data from a database such as FoxPro, dBASE, Paradox, or another Access database. It looks like this:
Sub ImportDatabase()
DoCmd.TransferDatabase _
TransferType:=acImport, _
DatabaseType:="FoxPro 2.5", _
DatabaseName:="c:\Clients\SAMS\Mastering Access 95 Development\Databases",
ObjectType:=acTable, _
Source:="Customers", _
Destination:="tblCustomers", _
StructureOnly:=False
End Sub
This code and all of the code in this chapter can be found in the CHAP19EX.MDB file on the sample code CD-ROM.
The following are the arguments to the TransferDatabase method:
TransferType: The type of transfer being performed.
DatabaseType: The type of database being imported.
DataBaseName: The 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 containing the table file. Do not include a backslash after the name of the directory.
ObjectType: The type of object that you want to import. This argument is ignored for all but Access objects.
Source: The name of the object you are importing. Do not include the file extension.
Destination: The name of the imported object.
StructureOnly: Specifies whether you want the structure of the table only or the structure and data.
The TransferText method of the DoCmd object is used to import text from a text file. Here's an example:
Sub ImportText()
DoCmd.TransferText _
TransferType:=acImportDelim, _
TableName:="tblCustomerText", _
FileName:="c:\Clients\SAMS\Mastering Access 95 Development_
\Databases\Customer.Txt"
End Sub
The following are the arguments to the TransferText method:
TransferType: Type of transfer you want to make.
SpecificationName: Specification name for the set of options that determines how the file is imported.
TableName: Name of the Access table that will receive the imported data.
FileName: Name of the Text file to import from.
HasFieldHeadings: Specifies whether the first row of the text file contains field headings.
The TransferSpreadsheet method of the DoCmd object is used to import data from a spreadsheet file. Here's an example:
Sub ImportSpreadsheet()
DoCmd.TransferSpreadsheet _
TransferType:=acImport, _
SpreadsheetType:=5, _
TableName:="tblCustomerSpread", _
FileName:="c:\Clients\SAMS\Mastering Access 95 Development_
\Databases\Customer.Xls",
HasFieldNames:=True
End Sub
The following are the arguments to the TransferSpreadsheet method:
TransferType: Type of transfer you want to make.
SpreadsheetType: Type of spreadsheet to import from. The default is Excel 3.0.
TableName: Name of the Access table that will receive the imported data.
FileName: Name of the Spreadsheet file to import from.
HasFieldNames: Specifies whether the first row of the spreadsheet contains field headings.
Range: The range of cells to import.
If you need to keep the data in its original format but want to treat the data just like any other Access table, linking is the best solution. All of the information required to establish and maintain the connection to the remote data source is stored within the linked table definition. Links can be created through the user interface and by using code. Both alternatives are covered in this section.
Probably one of the most common types of links is a link to another Access table. This type of link is created so that the application objects (queries, forms, reports, macros, and modules) can be placed in a local database and the tables can be stored in another database on a file server. Numerous benefits are associated with such a configuration. Chapter 18, "Developing for a Multiuser Environment," discusses these benefits in detail.
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 manually.
The Database Splitter was designed to split databases that have already been built with the tables and other database objects all in one physical .MDB database file. It automates the process of moving the data tables to another database. The Database Splitter is covered in detail in Chapter 18.
To create a link to an Access table, follow these steps:
Figure 19.4. The Link Tables dialog.
Figure 19.5. Linked tables in the Database window.
The process of creating links to other types of database files is a little different. It works like this:
Figure 19.6. The Select Index Files dialog.
Figure 19.7. The Select Unique Record Identifier dialog.
Notice the icon indicating the type of file that you linked to (Figure 19.8).
Figure 19.8. This icon indicates 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:
The code looks like this:
Sub LinkToAccessTableProps()
Dim db As DATABASE
Dim tdf As TableDef
Set db = CurrentDb
Set tdf = db.CreateTableDef("tblLinkedTable")
tdf.Connect = ";Database=c:\Databases\Chap19Data.MDB"
tdf.SourceTableName = "tblClients"
db.TableDefs.Append tdf
End Sub
Following the preceding steps, the database 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. All of this is discussed in further detail in the following sections.
When you link to an external table, you must provide information about the type, name, and location of the external database. This can be accomplished in one of two ways:
The process of setting the SourceTableName and Connect properties is illustrated by the following three lines of code:
Set tdf = db.CreateTableDef("tblLinkedTable")
tdf.Connect = ";Database=c:\Databases\Chap19Data.MDB"
tdf.SourceTableName = "tblClients"
Including the Source and Connect values as arguments to the CreateTableDef method looks like this:
Set tdf = db.CreateTableDef("tblLinkedTable", _
0, "tblClients", _
";Database=c:\Databases\Chap19Data")
As you can see from the example, both the Source (tblClients) and the Connect value are included as arguments to the CreateTableDef method.
The connect string is actually composed of several pieces. These include the Source Database Type, Database Name, Password, and Data Source Name. The Database Name is used for tables that are not ODBC-compliant, and the Data Source Name is used for ODBC tables.
The Source Database Type is the ISAM format that will be used for the link. Each Source Database Type is a different key in the Windows Registry. Valid Source Database Types include the following:
The Source Database Type must be entered exactly as specified in the preceding list. Spaces and punctuation must be exact.
The Database Name must include a fully qualified path to the file. The path can be specified with a drive letter and directory path or by using Universal Naming Conventions (UNC). For a local database, the path must be specified in this fashion:
Database=c:\ Databases\Chap19Data
For a file server either the UNC path or the drive letter path can be specified. The UNC path looks like this:
\\FILESERVERNAME\Databases\Chap19Data
In this case, the database called Chap19Data is stored on the Databases share of a particular File Server.
The Password is used to supply a password to a database (Access or other) that has been secured. It is best to fill in this part of the connection string from a variable at runtime rather than hard-coding it into the VBA code. Sending a password is covered in further detail in the "Working with Passwords" section of this chapter.
The completed connection string looks like this:
tdf.Connect = "FoxPro 2.6;Database=c:\\Databases;PWD="
In this example, the connect string is set up to link to a FoxPro 2.6 database in the c:\Clients\Databases directory. No password is specified.
Here is how you put it all together to establish a link to an external table:
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
Here is an example of how this subroutine would be called:
Call LinkToFox25("c:\customer\data","customer","tblCustomers")
The LinkToFox25 subroutine receives three parameters. The first parameter is the name of the directory in which the FoxPro file is stored. The second parameter is the name of the file (name of the table, without the .DBF extension) to which you want to connect. The third parameter is the name of the Access table that you are creating. The subroutine creates two object variables: a database object variable and a table definition object variable. It points the database object variable at the current database. Next it creates a table definition called tblCustomers. It establishes a connection string for that table definition. The connection string specified in the subroutine indicates that you will link to a FoxPro 2.5 table. The directory name acts as the database to which you are linked. After you have set the Connect property of the table definition, you are ready to indicate the name of table with which you are establishing the link. This is the name of the FoxPro file. Finally, you are ready to append the table definition to the database.
An alternative to the previous code would look like this:
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
Notice that here you are specifying the Access table name, the source table name, and the source database type and name—all as parameters to the CreateTableDef method.
You have seen how you can link to a FoxPro table. Putting everything that you have learned together, let's review how you can create a link to an Access table stored in another database.
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
Notice that the connection string no longer specifies the type of database to which you are connecting. Everything else in this routine is the same as the routine that connected to FoxPro. Also, looking at the parameters passed to the routine (listed next), the database passed to the routine is an actual Access database (as opposed to a directory), and the table name is the name of the Access table in the other database (rather than the .DBF filename).
Call LinkToAccess("C:\databases\northwind","Customers","tblCustomers")
As mentioned earlier in the chapter, it is generally preferable to link to, rather than open, an external table because of the additional performance that linking provides and the ease of use in dealing with a linked table. After you link to a table, it is treated just like any other Access table. But there are occasions when it is necessary to open an external table without creating a link to it. Opening an external table is a two-step process.
The connection information that you provide when you open an external table is similar to the information that 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:\customer\data", False, False, "FoxPro 2.5")
Here, the connection string is to the c:\customer\data database using the FoxPro 2.5 ISAM.
The OpenDatabase method receives the following arguments:
OpenDatabase(DBname, Exlusive, Read-Only, Source)
The DBname is the name of the database you are opening. The Exclusive and Read-Only parameters are used to specify whether you are opening the database exclusively or as read-only. The Source argument is used to specify the database type and connection string. Here's what it 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
This code is called as follows:
Call OpenExternalFox("c:\customer\data","Customer")
Notice that, here, you are not appending a table definition. 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 out the value of the first field. Of course, after the recordset is opened, you can manipulate it in any way you like. The table will not show up as a linked table in the database window. In fact, when the routine has completed and the local variable has gone 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 of these processes. This involves learning the Windows Registry settings that affect the linking process, learning more about the parameters that are available to you in 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.
Each ISAM driver has a separate key in the Windows Registry. These keys are used to configure the driver upon initialization. As you can see in Figure 19.9, the setup program has created keys for several data sources. Looking at a specific data source (in this case FoxPro 2.6), you can see all of the settings that exist for the FoxPro 2.6 driver. For example, the IndexFilter is set to FoxPro Index(*.idx;*.cdx). At times, you will need to modify one of the registry settings to customize the behavior of the ISAM driver. This is covered in the section titled "Special Considerations."
Figure 19.9. The Windows Registry with keys for ISAM drivers.
The connection string comprises the source database type, database name, user ID, password, and dataset name (DSN). Each part of the connection string must be separated by a semicolon.
Each source database type has a valid name. This is the name that must be used when accessing that type of data. These database types are listed in help under Connect Property. They can also be found in the Windows Registry under
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\3.0\ISAM Formats
The source database type must be specified accurately, or you will not be able to access the external data.
The source database name is the name of the database to which you are linking. In the case of ISAM files, this is the name of the directory in which the file is contained. The Database keyword is used to specify the database name.
The user ID is used whenever a user name must be specified in order to successfully log onto the data source. This is most common when dealing with back-end databases such as Oracle, Sybase, or Microsoft SQL Server. This part of the parameter string can be required to successfully log the user onto the system where the source data resides. The UID keyword is used to refer to the user ID.
As with the user ID, the password is most often included in dealing with back-end data. It can also be used on other database types that support passwords, such as Paradox, or when linking to an external Access table. The PWD keyword is used when specifying the password.
Finally, the dataset name is used to refer to a defined ODBC data source. Communicating with an ODBC data source is covered in detail in Chapter 20, "Client/Server Techniques." The DSN keyword is used when referring to the dataset name in the connection string.
In working with passwords, you probably won't want to hard code the password into your application. The following would be an example:
Sub LinkToSecured()
Dim db As DATABASE
Dim tdf As TableDef
Set db = CurrentDb
Set tdf = db.CreateTableDef("tblSecuredTable")
tdf.Connect = ";Database=c:\Databases\secure.mdb;PWD=alison"
tdf.SourceTableName = "tblClients"
db.TableDefs.Append tdf
End Sub
Although an invalid password would result in a message such as the one pictured in Figure 19.10, it is best to require the user to supply the password at runtime, as in the following example:
Sub ReallySecure()
Dim db As DATABASE
Dim tdf As TableDef
Dim strPassword As String
Set db = CurrentDb
Set tdf = db.CreateTableDef("tblSecuredTable")
strPassword = InputBox("Please Enter Your Password", "Database Security!!")
tdf.Connect = ";Database=c:\Databases\secure.mdb;PWD=" _
& strPassword
tdf.SourceTableName = "tblClients"
db.TableDefs.Append tdf
End Sub
Figure 19.10. This message box appears when an invalid password is provided.
The password is retrieved from the user and stored in a variable called strPassword. This strPassword variable is included in the connection string at runtime.
Refreshing links refers to the updating of the link to an external table. It is done when the location of an external table has changed. Removing links refers to the process of permanently removing a link to an external table.
Access cannot find external tables if their location has been moved. You need to accommodate for this in your VBA code. Furthermore, there might be times when you want to remove a link to external data. This occurs when it is no longer necessary to use the data, or when the data has been permanently imported into Access.
To refresh a link using VBA code, follow these steps:
The code looks like this:
Sub RefreshLink()
Dim db As Database
Set db = CurrentDb
db.TableDefs!FoxCusts.Connect = "FoxPro 2.6;DATABASE=d:\newdir"
db.TableDefs!FoxCusts.RefreshLink
End Sub
This routine can be modified to prompt the user for the directory containing the data tables. The modified routine looks like this:
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). This routine should be modified to allow the user a way out. The Resume throws the user into an endless loop if the database is not available. The enhanced routine is covered later in the "Practical Examples" section of this chapter.
To remove a link using VBA code, simply execute a Delete method on the table definition collection of the database.
Sub RemoveLink()
Dim db As Database
Set db = CurrentDb
db.TableDefs.Delete "FOXCUSTS"
End Sub
Not only can you link to existing tables, you can even create new external tables. This means that you can actually design a FoxPro, Paradox, or other type of table using VBA code. The table will reside on disk as an independent entity and can be used by the application for which it was created.
It is sometimes necessary for your application to provide another application with a data file. That other application might not be capable of reading an Access table. Therefore it is necessary for you to create the file in a format native to the application that needs to read it.
Creating a "foreign" table is not as difficult as you might think. It's actually not very different from creating an Access table using VBA code. Here's how it works:
Sub CreateFoxTable()
Dim db As DATABASE
Dim rst As Recordset
Dim fld As Field
Dim dbFox As DATABASE
Dim tdfFox As TableDef
Set dbFox = DBEngine.Workspaces(0).OpenDatabase_
("c:\databases", False, False, "FoxPro 2.6")
Set tdfFox = dbFox.CreateTableDef("PayMeth")
Set db = CurrentDb
Set rst = db.OpenRecordset("tblPaymentMethods", dbOpenSnapshot)
For Each fld In rst.Fields
Set fld = tdfFox.CreateField(fld.Name, _
fld.Type, _
fld.Size)
tdfFox.Fields.Append fld
Next fld
dbFox.TableDefs.Append tdfFox
End Sub
This example reads an access table and writes its structure out to a FoxPro table. It utilizes two database object variables, one recordset object variable, a table definition object variable, and a field object variable. It opens up a table called tblPaymentMethods as a snapshot. This is the table whose structure you will send out to FoxPro. Looking at each field in the table, it grabs that field's name, type, and size properties. It uses those properties as parameters to the CreateField method of the FoxPro table definition, appends each FoxPro field as it loops through each field in the Access table definition, and appends the table definition to create the FoxPro table.
When dealing with different types of external files, different problems and issues arise. If you understand these stumbling blocks prior to the time that they affect you, you will get a great head start in dealing with these potential obstacles.
The major concerns you will have in 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 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 \Jet\3.0\Engines\Xbase section of the registry must be set to 01 (true).
The dBASE indexes can be utilized by Access to improve performance. When 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 of 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 UNIX1 entry is used to specify a unique index.
The data types available in dBASE files are different than those available in Access files. It is important to understand how the field types map. Table 19.1 shows how each dBASE data type is mapped to a Jet data type.
|
dBASE Data Type |
Jet Data Type |
|
Character |
Text |
|
Numeric, Float |
Double |
|
Logical |
Boolean |
|
date |
Date/Time |
|
Memo |
Memo |
|
OLE |
OLE Object |
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 in dealing with FoxPro files surround deleted records, indexes, data types, and memo fields. Deleted records are handled in the same way as with dBASE files. By setting the Deleted value in the \Jet\3.0\Engines\Xbase section of the registry to 01, you filter deleted records.
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. The Schema Information file resides in the same directory as the Text file. It is always named SCHEMA.INI and contains information about the format of the file, column names, and 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.
Unfortunately, the process of working with external data is not always a smooth one. Many things can go wrong, including connection problems and a lack of temporary disk space. Both of these are discussed in this section.
Difficulties with accessing external data can be due to any of the following:
Access requires a significant amount of disk space to run complex queries on large tables. This disk space is required whether the tables are linked tables stored remotely in another format or whether they reside on the local machine. If not enough disk space is available to run a query, the application will behave unpredictably. It is therefore necessary to ensure that all users have enough disk space to meet the requirements of the queries that are run.
Because your application has to go through an extra translation layer, the installable ISAM, performance is nowhere near as good with ISAM files as it is with native Jet data. It is always best to import ISAM data whenever possible. If it is not possible to import the data, you need to either accept the performance that linking offers or consider linking the best solution to an otherwise unsolvable problem. Opening the recordset using the OpenDatabase method might alleviate the problem, but remember that this option cannot be used with bound forms.
It's time to split the data tables from the remainder of the application objects. You can easily accomplish this using the Database Splitter. After you have split the tables from the rest of the database objects, you need to write code to refresh links. Both of these topics are covered in this section.
Begin by using the Database Splitter to separate the tables from the rest of the database objects. Included on the sample code CD are CHAP19.MDB and CHAP19DATA.MDB. The CHAP19DATA.MDB file contains all of the tables, and CHAP19.MDB contains the rest of the database objects.
If you were to distribute the Time and Billing application and all users did not have the same path to the CHAP19DATA.MDB file, the application would not successfully load. The AutoExec function has been revised to ensure that the tables are successfully linked. It looks like this:
Function AutoExec()
On Error GoTo AutoExec_Err:
Dim fAnswer As Boolean
DoCmd.OpenForm "frmSplash"
DoEvents
DoCmd.Hourglass True
fAnswer = AreTablesAttached()
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 IsLoaded("frmSplash") Then
DoCmd.Close acForm, "frmSplash"
End If
Exit Function
AutoExec_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Exit Function
End Function
Notice that the routine AreTablesAttached is called from the AutoExec routine. If the AreTablesAttached routine returns False, a Message is displayed and the application is exited. The AreTablesAttached routine looks like this:
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 strFilename 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
MsgBox "You Must Locate the Data Tables"
DoCmd.OpenForm "frmGetTables", WindowMode:=acHidden
DoEvents
Forms!frmGetTables!dlgCommon.ShowOpen
strFilename = Forms!frmGetTables!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 Time and Billing App " & 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
AreTablesAttached = False
GoTo Exit_Final
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 at the current database and setting its default return value to True. It issues On Error Resume Next, instructing Access to proceed with the next line of code if an error is encountered. Next it attempts to open up a recordset based on the tblClients table. If the tables are not properly attached, an error occurs. The If Err.Number = 0 statement tests to see whether an error occurred. If the Err.Number is equal to zero, no error occurred and the function can be exited. Otherwise, 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 OCX control which is included as part of the Access Developer's Toolkit. It is covered in detail in Chapter 25, "Using OLE Controls." In this case, the control is being used to invoke the File Open common dialog. The filename that the user selects in the File Open dialog is returned into 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 within 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.
The capability to link to external data is one of Access 95's strongest attributes. It is important that you understand how to link to external data via the user interface and using VBA code. This chapter taught you how to link to external tables, open external data sources directly, refresh and remove links, and create external tables using VBA code. Many of the techniques covered in this chapter are expounded upon in Chapter 20.