DAO OpenRecordset in Word 2000 VBA returns nothing

[keithius]

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]

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]

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.

 

[Cindy M -WordMVP-]

<[email protected]>
<[email protected]>
Newsgroups: microsoft.public.word.vba.general
NNTP-Posting-Host: 144.166.78.83.cust.bluewin.ch 83.78.166.144
Lines: 1
Path: number1.nntp.dca.giganews.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newshub.sdsu.edu!msrtrans!TK2MSFTFEEDS01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
Xref: number1.nntp.dca.giganews.com microsoft.public.word.vba.general:87930

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.

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