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.