How To Access Multiple Databases in an SQL Query in VB 3.0
Retired KB Content Disclaimer
Attaching tables has powerful performance and administrative advantages --especially if you are executing queries repeatedly. However, sometimes youmight want to take the slower route described in this article when you needto use ad hoc queries that encompass two or more databases. This articleexplains how to construct these slower cross-database queries.
Method OneMicrosoft Access SQL provides an IN clause that allows you to connect to anexternal database (a database other than the current database). This methoddoes, however, limit you to only one external database at a time.
The IN clause has two parts, database name and connect string. The databasename is a fully-qualified path to the file or directory containing thedatabase file and the connect string contains the database type and otherparameters as needed.
To specify an external database, append a semicolon (;) to the connectpart, and enclose it with single or double quotation marks. The followingexample uses the IN clause to specify a table (Customers) in a dBASE IVdatabase (SALES):
SELECT Customers.CustomerIDFROM CustomersIN "C:\DBASE\DATA\SALES" "dBASE IV;"WHERE Customers.CustomerID Like "A*"
Dim db As DatabaseDim ds As DynasetDim 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.MoveNextWend
Method TwoThe Microsoft Access engine incorporated into Visual Basic version 3.0 canparse SQL queries to include the connect string used to open a databaseobject. The From clause of the SQL statement accepts a fully qualifiedtable name, which allows the placement of the connect string in squarebrackets before the table name. The connect string is separated from thetable name by a period. This method allows you to connect to multipleexternal databases at the same time.
You can access any table in either of two databases inside a single selectstatement by using this syntax:
SELECT tbl1.fld1,tbl2.fld1FROM [odbc;dsn=datasource;database=pubs;Username= <username>;PWD=<strong password>].tbl1,[dbase iii;database=C:\DBASE3].tbl2WHERE tbl1.fld1 = tbl2.fld1
In general, the connect string used here in square brackets is identical tothe Connect property of a TableDef when attaching or the fourth parameterof the OpenDatabase statement. It will be in one of three forms dependingon the database (ODBC, ISAM, or Microsoft Access).
For ODBC 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]
Code Example of a Multiple Database QueryThis example creates a dynaset joining two tables from two data sources,one an SQL Server and the other a Microsoft Access database. The TestTabtable is on the SQL Server and the T1 table is in the Microsoft Accessdatabase.
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 databaseDim ds As dynasetDim 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.MoveNextWend
Special Note Concerning Secured Microsoft Access DatabasesIf the Microsoft Access database is secured, the Visual Basic applicationmust execute the SetDataAccessOption and SetDefaultWorkspace commandsbefore executing any data access related code. This is required for asuccessful 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>"
However, if more than two secured Microsoft Access databases need to beaccessed for a query, the best approach is to move the actual tables fromsecured databases into one secured database. To do this, you need to changethe password for the admin account to "" temporarily during the transferoperations. Then you could use Visual Basic code, such as that in the dataaccess sample Visdata, to copy the tables.
Article ID: 113701 - Last Review: 06/17/2014 21:47:00 - Revision: 4.0
- kbhowto kbsqlprog KB113701