asked on

Opening a database file and running a query

I have a database file with several tables. I need to open the database from my vb 6 program and run a simple query using two tables from the database and store the result in vb.

DataBase = "H:\Access\NT\NT_Cat_dbs.mdb"
Table 1 = "Test Information"
Fields needed to get: TestNo & Phase

Table 2 = "Test Animals"
Fields needed to get: ProductID

I need to query the database using the fields:
AnimalId, NT

If datatypes need declared please include code.

Ask for More info if needed or not clear.....

thanks

sorinuc

What's the relation between Table 1 and Table 2?

ataripirate

ASKER

The two tables are linked with common Test Numbers.

TestNo

thanks

TimCottee

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Access\NT\NT_Cat_dbs.mdb;"
cnn.Open
rst.Open "Select TestNo,Phase From [Test Information]",cnn,adOpenStatic,adLockReadOnly
With rst
.MoveFirst
Do
'Do Something with each record e.g.,
MsgBox .Fields("TestNo").Value & " : " & .Fields("Phase").Value
.MoveNext
Loop Until .Eof
End With
rst.Close
rst.Open "Select ProductID From [Test Animals]",cnn,adOpenStatic,adLockReadOnly
With rst
.MoveFirst
Do
'Do Something with each record e.g.,
MsgBox .Fields("ProductID").Value
.MoveNext
Loop Until .Eof
End With
rst.Close
Set rst = Nothing
cnn.Close
Set cnn = Nothing

This code requires a reference to Microsoft ActiveX Data Objects 2.x Library added to the project.

You can extend the sql query by using a Where clause which specifies the values of AnimalID and NT to restrict the number of records returned.

Hope this helps.

ataripirate

ASKER

I will check it out, and respond asap.

thanks

rkot2000

You need to use a join to get all related data.

lvsql =Select TestNo , Phase, ProductID From Table1, Table2 Where Table 1. Test Numbers = Table2. Test Numbers?

so you can use tim's example and this sql

ASKER CERTIFIED SOLUTION

hongjun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ataripirate

ASKER

Common Record is TestNo

thanks

ataripirate

ASKER

Common Record is TestNo

thanks

ataripirate

ASKER

Hongjun,

I ran your code and go the following error:

Runtime error:
[Microsoft] [ODBC Microsoft Access 97 Driver] too Few Parameters, Expected 4

?

Thanks

hongjun

Dim objconn as adodb.connection
dim objrs as adodb.recordset
dim strsql as string

set objconn = new adodb.connection
objconn.open "Driver={Microsoft Access Driver (*.mdb)};dbq=h:\Access\NT\NT_Cat_dbs.mdb;uid=sa;pwd=;"

strsql = "select t1.TestNo, t1.Phase, t2.ProductID " & _
"from [Test Information] t1, [Test Animals] t2 " & _
"where t1.TestNo = t2.TestNo"

set objrs = objconn.execute(strsql)

hongjun