DAO with multi-tables

[TC]

Answered at bottom.

Hi all,

How can I get the informations from multi-tables using
DAO.
I have three tables:

Customer:
CustomerID
CustomerName
customerAddress

OrderProduct
CustomerID
ProductID

Product
ProductID
ProductName

What I need to do is to get the the product the customer
order. With that in mind, I created a query:

SELECT CustomerName,CustomerAddress,ProductName FROM
Customer,OrderProduct,Product WHERE Customer.CustomerID =
OrderProduct.CustomerID AND OrderProduct.ProductID =
Product.ProductID

Somehow when I add the query to the code, I'm getting
errors.

The code is:

Dim rd as DAO.Database
Dim rst as DAO.Recordset
Query=(The statement I typed above)

set rd=Currentdb()
set rst=rd.Openrecordset(Query) //Here is what I'm having
trouble. I keep getting an error. I'm trying to get the
information from multi-table using DAO. Is this possible
to be done? How? What do I need to do to make the
code/query work?

Any help is highly appreciated,

J.B.

Your SQL looks ok.

Your code also looks ok , assuming that:
Query = (The statement I types above)

actually means:
dim Query as string
Query = "SELECT ... etc."

So, post your >exact< code - copied & pasted exactly.

Also, what is the error you're gettng? There are 50 gazillion possible
errors. >You< know what message you're getting; we do not.

HTH,
TC

 

[James B.]

Hi all,

How can I get the informations from multi-tables using
DAO.
I have three tables:

Customer:
CustomerID
CustomerName
customerAddress

OrderProduct
CustomerID
ProductID

Product
ProductID
ProductName

What I need to do is to get the the product the customer
order. With that in mind, I created a query:

SELECT CustomerName,CustomerAddress,ProductName FROM
Customer,OrderProduct,Product WHERE Customer.CustomerID =
OrderProduct.CustomerID AND OrderProduct.ProductID =
Product.ProductID

Somehow when I add the query to the code, I'm getting
errors.

The code is:

Dim rd as DAO.Database
Dim rst as DAO.Recordset
Query=(The statement I typed above)

set rd=Currentdb()
set rst=rd.Openrecordset(Query) //Here is what I'm having
trouble. I keep getting an error. I'm trying to get the
information from multi-table using DAO. Is this possible
to be done? How? What do I need to do to make the
code/query work?

Any help is highly appreciated,

J.B.

 

[Andy]

Without analyzing your query, you might try the following:

dim qdf as dao.querydef
dim rst as dao.recordset
dim dbs as dao.database

set dbs = currentdb
set qdf = dbs.querydefs("Your Query Name")
set rst=qdf.openrecordsdet


hth,
Andy

 

[James B]

The error i'm getting is Data type mis-match and I can't
even getpass that statement. Posting the rest on the code
won't be need yet, until I can pass the query error part
first.

J.B.

 

[James B]

Now, I'm getting an error where "Item not found in this
collection". help.............

J.B.

 

[John Spencer (MVP)]

Have you set a reference to DAO?

Quoting Doug Steele
Database is a DAO object. By default, Access 2000 uses ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

(Hopefully this explains to you why you can't just use "DIM rst as
Recordset")

 

[cafe]

(snip)

Posting the rest on the code won't be needed yet

It will if you want much more help.

TC