DAO OpenRecordset in Word 2000 VBA returns nothing

I'm having an unusual problem with DAO and VBA in Word 2000. Here's the
problem code:

set myRecord = myDatabase.OpenRecordset(someQuery)

"myDatabase" is an open database connection to an Access database file
(mdb file), "myRecord" is a Recordset object, and "someQuery" is an SQL
query string; e.g. "select * from thisTable where thisField=SomeValue"

The problem is, if "someQuery" returns 0 (zero) rows, then myRecord
isn't "set" - it remains uninitialized. Later on in the code, a test
like this is used:

do while myRecord.EOF = False
' do something
loop

This causes a problem in Word 2000 (but not later versions of Word)
because "myRecord" is not set, so you get a "object variable or with
block variable not set" error.

My question is this: is the behavior of "OpenRecordset" different under
Word 2000's VBA? Or have I been wrong all this time in assuming that if
the query passed to OpenRecordset returned 0 (zero) rows, it would at
least return an empty (i.e. EOF=True) recordset? Or is this maybe just
a strange problem with a particular installation of Word/VBA/JET/DAO?

Jezebel
Fri Jul 07 04:06:17 CDT 2006

The recordset object isn't part of Word at all. It's derived entirely from
whatever library you have added to your VBA project (the DAO library in this
case). There are some versions of DAO (can't remember which ones) for which
you need to populate the recordset -- eg, by attempting a MoveLast
instruction -- before the recordset's Rows property becomes valid.



<keithius@gmail.com> wrote in message
news:1152196584.588411.39890@m73g2000cwd.googlegroups.com...
> I'm having an unusual problem with DAO and VBA in Word 2000. Here's the
> problem code:
>
> set myRecord = myDatabase.OpenRecordset(someQuery)
>
> "myDatabase" is an open database connection to an Access database file
> (mdb file), "myRecord" is a Recordset object, and "someQuery" is an SQL
> query string; e.g. "select * from thisTable where thisField=SomeValue"
>
> The problem is, if "someQuery" returns 0 (zero) rows, then myRecord
> isn't "set" - it remains uninitialized. Later on in the code, a test
> like this is used:
>
> do while myRecord.EOF = False
> ' do something
> loop
>
> This causes a problem in Word 2000 (but not later versions of Word)
> because "myRecord" is not set, so you get a "object variable or with
> block variable not set" error.
>
> My question is this: is the behavior of "OpenRecordset" different under
> Word 2000's VBA? Or have I been wrong all this time in assuming that if
> the query passed to OpenRecordset returned 0 (zero) rows, it would at
> least return an empty (i.e. EOF=True) recordset? Or is this maybe just
> a strange problem with a particular installation of Word/VBA/JET/DAO?
>

Keithius
Fri Jul 07 13:38:03 CDT 2006

That much I know - but I'm using DAO 3.6. According to all the
documentation I can find on this topic, when an empty recordset is
returned in a case like this, it should set .EOF=TRUE. In this case,
however, it is not - it is not setting the recordset object at all -
which is what causes the "object variable or with block variable not
set" error message.

There's a program running on the same computer that was written in VB6
and uses the DAO library and the same sort of code (I wrote it, so I
know). It's not experiencing this problem - which is why I thought
maybe there might be a connection with Word.

Jezebel wrote:
> The recordset object isn't part of Word at all. It's derived entirely from
> whatever library you have added to your VBA project (the DAO library in this
> case). There are some versions of DAO (can't remember which ones) for which
> you need to populate the recordset -- eg, by attempting a MoveLast
> instruction -- before the recordset's Rows property becomes valid.
>

Cindy
Sun Jul 09 07:37:37 CDT 2006

Hi Keithius,

Like Jezebel, I seem to recall running into this before, but I can't recall any
details beyond what she mentions. You could test whether myRecord Is Nothing, I
suppose.

The other choice would be to head over to an Access group that deals in DAO and
ask if anyone there has any idea.

> That much I know - but I'm using DAO 3.6. According to all the
> documentation I can find on this topic, when an empty recordset is
> returned in a case like this, it should set .EOF=TRUE. In this case,
> however, it is not - it is not setting the recordset object at all -
> which is what causes the "object variable or with block variable not
> set" error message.
>
> There's a program running on the same computer that was written in VB6
> and uses the DAO library and the same sort of code (I wrote it, so I
> know). It's not experiencing this problem - which is why I thought
> maybe there might be a connection with Word.
>
> Jezebel wrote:
> > The recordset object isn't part of Word at all. It's derived entirely from
> > whatever library you have added to your VBA project (the DAO library in this
> > case). There are some versions of DAO (can't remember which ones) for which
> > you need to populate the recordset -- eg, by attempting a MoveLast
> > instruction -- before the recordset's Rows property becomes valid.
> >
>

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :-)