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:
Set db = OpenDatabase("C:\VB\BIBLIO.MDB")
' Build the select statement, referencing the external dBASE IV file:
sql = "SELECT Customers.CustomerID, FROM Customers"
sql = sql & " IN 'C:\DBASE\DATA\SALES' 'dBASE IV;'"
sql = sql & " WHERE Customers.CustomerID Like 'A*'"
' Create the recordset:
Set ds = db.CreateDynaset(sql)
' Loop through and display the records:
While Not ds.EOF
For i = 0 To ds.Fields.Count - 1
Print ds(i); " ";
Next i
Print
ds.MoveNext
Wend
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;Username= <username>;PWD=<strong password>].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;Username=<username>;
PWD=<strong password>]
For ISAM databases:
[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]
For Microsoft Access databases:
[;database=C:\VB\BIBLIO.MDB]
Note the leading semicolon for Microsoft Access databases is important.
This is exactly the same string needed to fill the Connect property of a
TableDef object before attaching the table to a Microsoft Access format
database. The leading semicolon is a place holder for the unneeded database
format specification and allows the "database=" clause to follow.
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.
Note You must change <username> and <strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the 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.
Uid$ = <username>
Pwd$ = <strong password>
' Build the select statement, concatenating the user's id and password:
sql = "SELECT T1.F2, TestTab.F2, TestTab.F3"
sql = sql & " FROM [;database=C:\ACCESS\DB1.MDB].T1 , "
sql = sql & " [odbc;dsn=texas;database=playpen;Username=" & Uid$
sql = sql & ";PWD=" & Pwd$ & "].TestTab"
sql = sql & " WHERE T1.F1 = TestTab.F1"
' Execute the select query:
Set ds = db.CreateDynaset(sql)
' Loop through and display the records:
While Not ds.EOF
For i = 0 To ds.Fields.Count - 1
Print ds(i); " ";
Next i
Print
ds.MoveNext
Wend
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 "Username=" 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>"
When this is done, queries to the secured Microsoft Access database will
succeed. However, note that because of this process, there is a built-in
limitation for this ad hoc technique; only one secured Microsoft Access
database can be accessed with an ad hoc query. This is because once the
Microsoft Access engine is initialized in a session, with a particular user
name and password combination, those values are retained until the session
(Visual Basic executable program or session of the environment, VB.EXE)
ends.
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.