DAO doesn't work anymore?

S

Steve W.

It's been several months since I programmed in VBA (and
then in Access 2000), but it seems to me that this used to
work. Has DAO changed somehow with Access 2002?

Private Sub btnConvert_Click()

Dim Rst As Recordset
Dim Qdf As QueryDef
Dim Parm As Parameter
Dim ORst As Recordset
Dim Db As Database

Set Db = CurrentDb()
Set Qdf = Db.QueryDefs("qry_Delete_OrdersNormal_by_Year")
Set Parm = Qdf.Parameters![Year?]
Parm = Me.tbxYear
Qdf.Execute

Set Qdf = Db.QueryDefs("qry_Orders_for_Conversion")
Qdf.Parameters(0).Value = Me.tbxYear
'Set Parm = Qdf.Parameters![Year?]
'Parm = Me.tbxYear
Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)

1) Setting the value of a Query parameter doesn't work
the way I remember. What's wrong with the syntax in the
first attempt to set the Parm variable to the query parm...

Set Parm = Qdf.Parameters![Year?]

The query has a parm [Year?] set up for one column in the
base table. I get a "type mismatch" error on this line if
I use this syntax.

2) OK, no big deal, I can change to more awkward syntax
if I have to in setting the parm. In the second instance
of setting the Parm...

Qdf.Parameters(0) = Me.tbxYear ' (a form text box)

This works. But now on the immediately following line

Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)

I get a "type mismatch" error on this line. Why!!!?

Thanks for your help.

Steve

PS. the column that the [Year?] query parm is in is an
integer value (but this never seemed to matter before and
I get the last crash even if I try to convert the value of
tbxYear to an Int before I set the parm).
 
P

Paul Overway

You probably have not set a reference to DAO. See Tools|References while in
VBA Editor. Also, if you are not using ADO, you should probably remove that
reference. Otherwise, you need to preface your variables with DAO, i.e.,
DAO.Recordset.
 
L

Larry Linson

The reference to Microsoft DAO 3.6 Library is not set by default (as it was
also not in Access 2000), so you have to open any module, on the menu, Tools
| References, find that line, and check it. You may want to move it above
ADO in the reference list just in case you miss qualifying an object.

Because ADO and DAO share object names, you'll need to qualify each DAO
object with, no surprise, DAO... as in

Dim rs as DAO.RecordSet.

VBA will use the definition from the library highest in the reference list
if you forget to qualify one of the objects you have Dim'd.

Both these "issues" were the same in the Access 2000 environment, so I guess
they must have slipped from your memory while you were away from Access.

Larry Linson
Microsoft Access MVP
 
S

Steve W.

I did have a reference set to DAO. But... taking the
reference to ActiveX Data Objects 2.1 from the reference
list worked. So I guess the syntax for the two data
access methods conflicts.

I ported my "old" database that I was working on before
from Access 97, so maybe that's why I hadn't encountered
this before.

Thanks for your help.
-----Original Message-----
You probably have not set a reference to DAO. See Tools|References while in
VBA Editor. Also, if you are not using ADO, you should probably remove that
reference. Otherwise, you need to preface your variables with DAO, i.e.,
DAO.Recordset.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com


Steve W. said:
It's been several months since I programmed in VBA (and
then in Access 2000), but it seems to me that this used to
work. Has DAO changed somehow with Access 2002?

Private Sub btnConvert_Click()

Dim Rst As Recordset
Dim Qdf As QueryDef
Dim Parm As Parameter
Dim ORst As Recordset
Dim Db As Database

Set Db = CurrentDb()
Set Qdf = Db.QueryDefs ("qry_Delete_OrdersNormal_by_Year")
Set Parm = Qdf.Parameters![Year?]
Parm = Me.tbxYear
Qdf.Execute

Set Qdf = Db.QueryDefs("qry_Orders_for_Conversion")
Qdf.Parameters(0).Value = Me.tbxYear
'Set Parm = Qdf.Parameters![Year?]
'Parm = Me.tbxYear
Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)

1) Setting the value of a Query parameter doesn't work
the way I remember. What's wrong with the syntax in the
first attempt to set the Parm variable to the query parm...

Set Parm = Qdf.Parameters![Year?]

The query has a parm [Year?] set up for one column in the
base table. I get a "type mismatch" error on this line if
I use this syntax.

2) OK, no big deal, I can change to more awkward syntax
if I have to in setting the parm. In the second instance
of setting the Parm...

Qdf.Parameters(0) = Me.tbxYear ' (a form text box)

This works. But now on the immediately following line

Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)

I get a "type mismatch" error on this line. Why!!!?

Thanks for your help.

Steve

PS. the column that the [Year?] query parm is in is an
integer value (but this never seemed to matter before and
I get the last crash even if I try to convert the value of
tbxYear to an Int before I set the parm).
Click to expand...


.
Click to expand...
 
S

Steve W.

I did have DAO in my reference list, but I didn't qualify
the calls. Instead of qualifying the calls I just removed
the defaulted ADO reference and everything started working.

Come to think of it, I did all my work in Access 2000 on
databases that I started in Access 97. Maybe that's why I
didn't have to contend with this before?

Whatever... It's working now.

Thanks for your help.

Steve
-----Original Message-----
The reference to Microsoft DAO 3.6 Library is not set by default (as it was
also not in Access 2000), so you have to open any module, on the menu, Tools
| References, find that line, and check it. You may want to move it above
ADO in the reference list just in case you miss qualifying an object.

Because ADO and DAO share object names, you'll need to qualify each DAO
object with, no surprise, DAO... as in

Dim rs as DAO.RecordSet.

VBA will use the definition from the library highest in the reference list
if you forget to qualify one of the objects you have Dim'd.

Both these "issues" were the same in the Access 2000 environment, so I guess
they must have slipped from your memory while you were away from Access.

Larry Linson
Microsoft Access MVP


Steve W. said:
It's been several months since I programmed in VBA (and
then in Access 2000), but it seems to me that this used to
work. Has DAO changed somehow with Access 2002?

Private Sub btnConvert_Click()

Dim Rst As Recordset
Dim Qdf As QueryDef
Dim Parm As Parameter
Dim ORst As Recordset
Dim Db As Database

Set Db = CurrentDb()
Set Qdf = Db.QueryDefs ("qry_Delete_OrdersNormal_by_Year")
Set Parm = Qdf.Parameters![Year?]
Parm = Me.tbxYear
Qdf.Execute

Set Qdf = Db.QueryDefs("qry_Orders_for_Conversion")
Qdf.Parameters(0).Value = Me.tbxYear
'Set Parm = Qdf.Parameters![Year?]
'Parm = Me.tbxYear
Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)

1) Setting the value of a Query parameter doesn't work
the way I remember. What's wrong with the syntax in the
first attempt to set the Parm variable to the query parm...

Set Parm = Qdf.Parameters![Year?]

The query has a parm [Year?] set up for one column in the
base table. I get a "type mismatch" error on this line if
I use this syntax.

2) OK, no big deal, I can change to more awkward syntax
if I have to in setting the parm. In the second instance
of setting the Parm...

Qdf.Parameters(0) = Me.tbxYear ' (a form text box)

This works. But now on the immediately following line

Set Rst = Qdf.OpenRecordset(dbOpenSnapshot)

I get a "type mismatch" error on this line. Why!!!?

Thanks for your help.

Steve

PS. the column that the [Year?] query parm is in is an
integer value (but this never seemed to matter before and
I get the last crash even if I try to convert the value of
tbxYear to an Int before I set the parm).
Click to expand...


.
Click to expand...
 
T

Tim Ferguson

I did have a reference set to DAO. But... taking the
reference to ActiveX Data Objects 2.1 from the reference
list worked. So I guess the syntax for the two data
access methods conflicts.
Click to expand...

For future reference, if you ever need to have references to both, VBA will
stop looking after the first valid reference in the order in the References
dialog. If you move DAO above ADO, then Dim As Recordset will get you a DAO
one, and you'd have to use an explicit Dim As ADODB.Recordset to get the
other one.

Or vice versa.

Hope that makes sense


Tim F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

RUN TIME Error on Code that has ALWAYS worked before 2
Strange behavior with query and rs.Recordcount 2
item not found in this collection 7
Proper use of Parameter 4
Referencing Field names in Parameters 14
How to optimize a query called in code VBA 6
Excel Export Efficiency 2
OpenRecordset - Error 3061 8

Top