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
ataripirateAsked:
Who is Participating?
 
hongjunConnect With a Mentor Commented:
Try this. I am assuming you have included "Microsoft ActiveX Data Objects 2.1 or 2.5 library" in Project->References.

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.AnimalId = t2.AnimalId"

set objrs = objconn.execute(strsql)


One thing to ask about your where clause and the relationship of the 2 tables. I am using AnimalId to do the joining and am assuming this field is common to both tables. I could be wrong so tell us your where condition and the field in common.

Records retrieved will be stored in objrs object.

hongjun
0
 
sorinucCommented:
What's the relation between Table 1 and Table 2?
0
 
ataripirateAuthor Commented:
The two tables are linked with common Test Numbers.

TestNo




thanks
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TimCotteeHead of Software ServicesCommented:
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.
0
 
ataripirateAuthor Commented:
I will check it out, and respond asap.

thanks
0
 
rkot2000Commented:
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
0
 
ataripirateAuthor Commented:
Common Record is TestNo

thanks
0
 
ataripirateAuthor Commented:
Common Record is TestNo

thanks
0
 
ataripirateAuthor Commented:
Hongjun,

I ran your code and go the following error:

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

?

Thanks
0
 
hongjunCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.