How to Access Multiple Databases in an SQL Query in VB 3.0

- Professional Edition of Microsoft Visual Basic for Windows,

  version 3.0

SUMMARY

When accessing data from more than one data source simultaneously in a single query, you will probably want to use the fastest and most general method -- attaching the tables from the different data sources to a single Microsoft Access database. At that point, queries that span two different databases can be constructed as if all the tables, attached or local, were local to the Microsoft Access database. For more information on attaching tables, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q108423

TITLE     : How to Attach an External Database Table to a VB 3.0 Database

MORE INFORMATION

There are two methods you can use to specify a database outside the one that's currently open.

Method One

Microsoft Access SQL provides an IN clause that allows you to connect to an external database (a database other than the current database). This method does, however, limit you to only one external database at a time.

The IN clause has two parts, database name and connect string. The database name is a fully-qualified path to the file or directory containing the database file and the connect string contains the database type and other parameters as needed.

To specify an external database, append a semicolon (;) to the connect part, and enclose it with single or double quotation marks. The following example uses the IN clause to specify a table (Customers) in a dBASE IV database (SALES):

SELECT Customers.CustomerID FROM Customers IN "C:\DBASE\DATA\SALES" "dBASE IV;" WHERE Customers.CustomerID Like "A*"

In Visual Basic, you can create a dynaset from the above example by using the following Visual Basic code:

Dim db As Database Dim ds As Dynaset Dim sql As String

' Open a database:

' Build the select statement, referencing the external dBASE IV file:

' Create the recordset:

' Loop through and display the records:

   For i = 0 To ds.Fields.Count - 1
      Print ds(i); "  ";
   Next i
   Print
   ds.MoveNext

Method Two

The Microsoft Access engine incorporated into Visual Basic version 3.0 can parse SQL queries to include the connect string used to open a database object. The From clause of the SQL statement accepts a fully qualified table name, which allows the placement of the connect string in square brackets before the table name. The connect string is separated from the table name by a period. This method allows you to connect to multiple external databases at the same time.

You can access any table in either of two databases inside a single select statement by using this syntax:

SELECT tbl1.fld1,tbl2.fld1 FROM [odbc;dsn=datasource;database=pubs;uid=sa;pwd=].tbl1, [dbase iii;database=C:\DBASE3].tbl2 WHERE tbl1.fld1 = tbl2.fld1

This example joins two tables from two different databases, one an ODBC data source and the other a dBASE III table in the directory C:\DBASE3.

In general, the connect string used here in square brackets is identical to the Connect property of a TableDef when attaching or the fourth parameter of the OpenDatabase statement. It will be in one of three forms depending on the database (ODBC, ISAM, or Microsoft Access).

For ODBC databases:

   [odbc;dsn=datasource;database=thedatabasename;uid=sa;pwd=<password>]

   [paradox 3.X;database=C:\DATABASE\PARADOX3]
   [foxpro 2.5;database=C:\DATABASE\FOX25]
   [dbase iv;database=C:\DATABASE\DBASEIV]
   [btrieve;database=C:\DATABASE\BTRIEVE\FILE.DDF]

   [;database=C:\VB\BIBLIO.MDB]

Code Example of a Multiple Database Query

This example creates a dynaset joining two tables from two data sources, one an SQL Server and the other a Microsoft Access database. The TestTab table is on the SQL Server and the T1 table is in the Microsoft Access database.

Dim db As database Dim ds As dynaset Dim sql As String, Uid$, Pwd$

Set db = OpenDatabase("C:\VB\BIBLIO.MDB")

' This obtains a valid database object. It does not have to be a Microsoft
' Access database; the following works equally as well:
' Set db = OpenDatabase("C:\FOXPRO25\", 0, 0, "foxpro 2.5")

' The values here are hard-coded, but you could prompt the user for their
' user id and password.

' Build the select statement, concatenating the user's id & password:

' Execute the select query:

' Loop through and display the records:

   For i = 0 To ds.Fields.Count - 1
      Print ds(i); "  ";
   Next i
   Print
   ds.MoveNext

Special Note Concerning Secured Microsoft Access Databases

If the Microsoft Access database is secured, the Visual Basic application must execute the SetDataAccessOption and SetDefaultWorkspace commands before executing any data access related code. This is required for a successful logon because Microsoft Access does not use the "uid=" and "pwd=" sections of the connect string. For example:

   ' Establish the location of the SYSTEM.MDA files if in another
   ' directory other than the \WINDOWS directory:
   SetDataAccessOption 1, "C:\MYDIR\MYAPP.INI"
   ' Log on to a valid account:
   SetDefaultWorkspace "admin", "<password>"

However, if more than two secured Microsoft Access databases need to be accessed for a query, the best approach is to move the actual tables from secured databases into one secured database. To do this, you need to change the password for the admin account to "" temporarily during the transfer operations. Then you could use Visual Basic code, such as that in the data access sample Visdata, to copy the tables.

For additional information on Microsoft Access security, please see the following article in the Microsoft Knowledge Base:

ARTICLE-ID: Q105990

TITLE     : How Visual Basic Handles Security Set by Microsoft Access