DAO reference

[Guest]

Hello all,

I am trying to use the outstanding info of this link.
http://msdn.microsoft.com/newsgroup...cedev-access&lang=en&cr=US&sloc=en-us&m=1&p=1

The post says to set references to Excel, so I added Microsoft Excel 11.0
Object Library

I am using Access 2003, Win XP. With references
Visual Basic for Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Calendar Control 11.0
Microsoft Excel 11.0 Object Library

I am erroring out at the Set rs line in the code below. Is this a reference
problem or word wrap problem?

sSQL = "SELECT DataSeries1, DataSeries2 " _
& "FROM Table1 " _
& "ORDER BY ID;"
'Debug.Print sSQL
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) With rs
...MoveLast 'force error 3021 if no records
...MoveFirst

I am somewhat DAO challenged, so any assistance you can provide, I would
appreciate it.

 

[Guest]

David,

This appears to be a word wrap problem. If you have copied the code from
some other source this can happen.

The "Set" portion should be:
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
the "With" statement would be like:
with rs
.MoveFirst
.MoveLast
end with

 

[Guest]

Hi Mr B.

try using rs.recordcount > 0
instead of forcing an error.
if you are like me you will encounter enuf errors. You dont need to make
them.

example:
if rs.recordcount > 0 then
rs = nothing
db = nothing
end function
"or what ever"
endif

with rs
.MoveFirst
.MoveLast
end with

Scott Burke

 

[Tim Ferguson]

Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
With rs
.MoveLast 'force error 3021 if no records

No. If you want to know whether the recordset is empty, you use the .EOF
and .BOF properties:

Set rs= etc
If rs.BOF Then
' There are no records

Else
' there are

End If


If you are going to iterate the whole lot, then it's simpler just to use
the looping criterion at the top:

Set rs = etc
Do While Not rs.EOF
' Do Something here
rs.MoveNext

Loop

Finally, if it's an already-opened recordset and you don't know if the
record pointer has been moved away from the start, you can do this

If rs.BOF And rs.EOF Then
' it must be an empty recordset

End If

It's actually much easier and faster than it is made out to be...

HTH


Tim F

 

[Guest]

Hi Tim,

If rs.BOF And rs.EOF Then
' it must be an empty recordset

End If

I never thought of that one.
Scott Burke