Submitted By: Kent Finkle

Performs an inner join on tables from two Access databases.

Visual Basic
Edit|Remove
'* Script name: ExternalJoin.vbs
'* Created on: Monday, April 16, 2007
'* Author: Kent Finkle
'* Purpose: Do an inner join on Access tables in different files.

Dim objConnection
Dim objRecordset
Dim Sql

Sql = "SELECT City.City, City.St, Sales.Dt, Sales.Sales "
Sql = Sql & "FROM City "
Sql = Sql & "INNER JOIN [c:\scripts\db2.mdb].Sales  "
Sql = Sql & "ON City.City = Sales.City;"

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Scripts\db1.mdb"

Set objRecordset = CreateObject("ADODB.Recordset")

objRecordset.Open Sql, objConnection

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields("City").Value
    Wscript.Echo objRecordset.Fields("St").Value
    Wscript.Echo objRecordset.Fields("Sales").Value
    objRecordset.MoveNext
Loop

objRecordset.Close
objConnection.Close