DAO is one of many ways to access database in VB6. This tutorial tells you how you can access a .mdb database which is not pointed by the DAO Database object.
I have separate Access 2003 .mdb database files, one is pointed by the Database object conn and the other I want to access using the same connection.
SELECT * FROM [File Path][DBNAME].[TABLE]
Connection
Have a look at the connection. [May be you already know, what its look like, this for those not familiar with DAO in VB6]
Dim externalEmp As Recordset
Dim conn As Database
Set conn = OpenDatabase("D:\105443T.mdb")
Querying Records
The conn is now capable of querying any table within the 105443T database.
Say,
Set tr = conn.OpenRecordset("select * from Transactions")
If tr.RecordCount > 0 Then
tr.MoveFirst
tr.MoveNext
tr.MoveFirst
For c = 0 To tr.RecordCount - 1
Debug.Print tr!date & "--" & tr!dr & "--" & tr!cr
tr.MoveNext
Next c
End If
Querying the external DB
Likewise, I can point external Database too with zero changes in conn object. Just specify the [path of file] [.] [data table Name] in place of the table name in the query.
Set externalEmp = conn.OpenRecordset("select * from C:\users\manoj\documents\office.emp")
If externalEmp.RecordCount > 0 Then
externalEmp.MoveFirst
externalEmp.MoveNext
externalEmp.MoveFirst
For c = 0 To externalEmp.RecordCount - 1
Debug.Print externalEmp!Ename & "--" & externalEmp!edepart & "--" & externalEmp!ebasic
externalEmp.MoveNext
Next c
End If
The same is also possible with ADO and other connections.
That’s it.
