Querying external database in vb6 DAO


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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.