Conversion Failure?

[Dave Bolt]

I have a legacy application written using Access 2.0 which is to be updated
to Access 2003 at least in the near future.
I experimented converting to Access 2000 and found this problem.

Public Function Test()
Dim recset As Recordset
Dim db As Database
Dim r As Integer
Set db = DBEngine(0)(0)
Set recset = db.OpenRecordset("testtable")
r = recset.ROW ' This line fails
End Function


Even though ROW, or any other valid field name exists in the table, when
Access tries to compile the VBA function it cannot find a data member in the
RecordSet. Works in Access 97 by the way.
The version below seems to fix the problem, but is it the correct
conversion?

Public Function Test()
Dim recset As Recordset
Dim db As Database
Dim r As Integer
Set db = DBEngine(0)(0)
Set recset = db.OpenRecordset("testtable")
r = recset.Fields("ROW") ' Both these lines work
r = recset("ROW")
End Function

Thanks
Dave

 

[Douglas J. Steele]

Try:

r = recset!ROW

 

[Dave Bolt]

Yes, that works just great. I thought I had tried that but obviously I
didn't.
Thanks

Now, why does it change from being a full stop in a previous version to
being a ! in 2000 and why does the conversion process fail to convert.
I don't mind if nobody has an answer to these two questions.;-)
Regards
Dave

 

[Douglas J. Steele]

I believe that why you now need a bang instead of a dot is that,
realistically, it should have been a bang in Access 97 as well, but Access
97 was a little more forgiving.

The Help file in Access 97 says the following:

The ! operator indicates that what follows is a user-defined item (an
element of a collection). For example, use the ! operator to refer to an
open form, an open report, or a control on an open form or report.

Identifier Refers to
Forms![Orders] The open Orders form
Reports![Invoice] The open Invoice report
Forms![Orders]![OrderID] The OrderID control on the open Orders form

The . (dot) operator usually indicates that what follows is an item defined
by Microsoft Access. For example, use the . (dot) operator to refer to a
property of a form, report, or control.

Based on that, r = recset.ROW is wrong, since Row is not an item defined by
Access. Row is a specific element of the Fields collection, so, to use .,
you should really be using r = recset.Fields("ROW").

Why it's not caught in conversion? I'd only be guessing, but it is possible
to define new properties for objects. While you can't define properties for
recordsets, you could theoretically define a property named Row for a form,
say. It was probably simpler for them to not have to check whether the
correct syntax was being used. (Or no one thought of it!)

 

[Dave Bolt]

Nice clear answer, thanks.
Of course, I just needed to know that I needed to look up the ! operator
Amazing how much simpler it is asking someone for a hint.
Off to hand convert some code now.
Bye
Dave

I believe that why you now need a bang instead of a dot is that,
realistically, it should have been a bang in Access 97 as well, but Access
97 was a little more forgiving.

The Help file in Access 97 says the following:

The ! operator indicates that what follows is a user-defined item (an
element of a collection). For example, use the ! operator to refer to an
open form, an open report, or a control on an open form or report.

Identifier Refers to
Forms![Orders] The open Orders form
Reports![Invoice] The open Invoice report
Forms![Orders]![OrderID] The OrderID control on the open Orders form

The . (dot) operator usually indicates that what follows is an item
defined by Microsoft Access. For example, use the . (dot) operator to
refer to a property of a form, report, or control.

Based on that, r = recset.ROW is wrong, since Row is not an item defined
by Access. Row is a specific element of the Fields collection, so, to use
., you should really be using r = recset.Fields("ROW").

Why it's not caught in conversion? I'd only be guessing, but it is
possible to define new properties for objects. While you can't define
properties for recordsets, you could theoretically define a property named
Row for a form, say. It was probably simpler for them to not have to check
whether the correct syntax was being used. (Or no one thought of it!)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Yes, that works just great. I thought I had tried that but obviously I
didn't.
Thanks

Now, why does it change from being a full stop in a previous version to
being a ! in 2000 and why does the conversion process fail to convert.
I don't mind if nobody has an answer to these two questions.;-)
Regards
Dave

 

[David W. Fenton]

I believe that why you now need a bang instead of a dot is that,
realistically, it should have been a bang in Access 97 as well,
but Access 97 was a little more forgiving.

It DOES NOT work in Access 97. The conversion was from Access 2,
where it *did* work, directly to A2K3.

I never encountered this problem, but I don't think I ever converted
any apps with DAO recordsets in them from Access 2 to A97 -- by the
time I got into using DAO, I had left Access 2 long behind. I doubt
it's the kind of thing thing that the conversion process would have
been able to catch, since it's not a compile error but a runtime
error.

But for most of us, this adaptation came about 10 years ago!

 

[Dave Bolt]

But for most of us, this adaptation came about 10 years ago!

(amongst other things).

Yes, but in the real world there are lots of small companies who only update
when the equipment stops working. I support a database application written
during Win3.1 days which actually runs under DOS and is still in use. Funny
thing though, it keeps working. <snigger>
Regards
Dave

 

[Brendan Reynolds]

If it worked for you in an Access 97 MDB converted from Access 2, Dave,
that's because when you convert from Access 2, Access 97 adds a reference to
the DAO 2.x/3.x Compatibility Library instead of a reference to the DAO 3.51
Object Library. It would not work in a new Access 97 MDB, because new Access
97 MDBs use the DAO 3.51 Object Library.

 

[Yenia]

la musica es todo en la vida