While .mdb databases (native to the Access database system) are easy to access (no pun intended. None taken, I'm sure) there are times when programmers need to get data from other desktop sources. For example, we might need to read or write data from dBASE, Paradox, FoxPro, or other databases. We might also need to retrieve information from Excel or Lotus spreadsheets, or even text files. If you take a look at the intrinsic (built-in) data control in VB 6.0, you will notice that there are several additional data sources that the control can talk to in addition to it's native Access:
The real benefit of connecting to one of the external ISAM (Indexed Sequential Access Method) file types listed in the connect property of the data control is that we can work on the data - as is - without changing its structure. We can leave the data where it is and use VB6.0 to connect with the various data source types. So any applications that created these files can continue to operate unchanged. We just go in and read or write data to and from these sources.
For example, there might be cases where an ASCII file is downloaded from a legacy mainframe system that we need to retrieve. Or possibly, several departments create Excel spreadsheets that track customer orders. We can simply connect to them, extract the data, and consolidate the information in an Access table.
Another probable scenario is that we want to get all of the data in an older Paradox system into a newer Access table. In a single VB6.0 application we can read the Paradox data and write it to an Access table in one fell swoop. I have done that several times and am still amazed at how easy the data control makes this. In many ways, it is transparent to the programmer that VB6.0, in conjunction with the data control, is talking to both Access and Paradox at the same time.
Jet performs this seemingly magical task by using various .DLL files called ISAM drivers. Indexed Sequential Access Method drivers are .DLL files that contain the specific code to talk to the various data sources. Jet supports several data access connections:
| Data Source | Version supported by Jet |
| Access | All versions |
| Paradox | 3.x, 4.x, 5.x |
| DBASE | III, IV, 5.0 |
| Excel | 3,4,5, and 8 |
| FoxPro | 2, 2.5, 2.6 and 3.0 |
| Lotus 1-2-3 | wk1, wk3, and wk4 |
| Text | Any fixed or delimited text file |
All data access from the data control, including Access itself, is handled under the hood with installable ISAM drivers. We just program the data control, and it handles the translation to the other data sources. We didn't bother with setting the DataSource on the data control we have been using in our programs so far. If you don't specify a specific connection, the default connection is to Access.
We mentioned earlier in the book that there are not only different formats of data, but there are even variations in the way tables themselves are laid out. Access, for example, uses the notion of a container. There is a single .mdb database that contains all of the tables within it. Paradox, however, has a separate file for every table. So if a database in Access has 10 tables, there will be a single .mdb file that contains 10 tables. Paradox, on the other hand, will have 10 separate independent tables.
As we mentioned earlier, DAO is the interface to the Jet database engine. The DAO/Jet combination is primarily designed to access ISAM databases as it supports most of the common ISAM data access techniques.
DAO/Jet is still the solution to use when it comes to accessing native Jet (.mdb) or ISAM data sources such as Btrieve, FoxPro, Paradox, and dBase.
In addition to the ISAM .DLL files that contain the code to handle specific data sources, as listed above, there is another more generalized method available to you. Microsoft's standard for providing data access to various data sources is Open Database Connectivity (ODBC). Essentially, this is a SQL approach to retrieving data. ODBC is supported by all sorts of software applications from spreadsheets to word processors to databases. ODBC provides database interoperability, which really means that it gives us methods by which data can be exchanged among different databases. So if an ISAM driver is not available, it is possible to communicate with a data source if it understands ODBC. Starting with DAO 3.5, an important enhancement was added - ODBC Direct. Selecting this option completely bypasses Jet and can really speed things up.
Using ODBCDirect allows you to deploy client/server applications using Microsoft Access without using Microsoft Jet as the middle layer. ODBCDirect is an interface directly to ODBC. So in most cases, it is faster.
If your application is hitting a Microsoft Jet .mdb or any other file-share databases it supports, you should use the Microsoft Jet path. This is because ODBCDirect was created specifically to access remote data. You should also use Jet if you want to join data in tables stored in different back-ends such as Oracle and SQL Server. You would need Jet in this case because it provides heterogeneous joins. You can create tables using ODBCDirect by executing SQL statements, but it's more convenient to use the Jet TableDef object we saw in the last chapter. So by now you are probably wondering why would you ever use ODBCDirect anyway? Good question. Most client/server developers simply need to quickly read data in, change it, and write it back to the back end. And if they need to do this quickly, then ODBCDirect is the way to go. It will make your code faster because it gives you direct access to the ODBC data source. It does not require loading the Jet engine, so this uses far fewer resources on the client side. This approach makes the ODBC server responsible for all query processing.