X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Recordset Has Records But Eof And Bof Are True, Access 2016    
post Feb 22 2018, 01:35 PM

Posts: 73
Joined: 29-January 03

I am creating an ADO recordset that has records when you use the SQL in a query but when evaluating the recordset the BOF and EOF are true. Code is below.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim str_sql As String
Dim str_document_number As String
Dim str_version As String

str_document_number = "DOC-1"
str_version = "1"

Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset

str_sql = "select statement here"

rst.Open str_sql, cnn, adOpenDynamic, adLockOptimistic

If (rst.EOF And rst.BOF) Then

str_sql = "INSERT INTO tbl_temp_document_change(document_number, arc_version, field_name, old_value, new_value) " & _
"VALUES(" & get_quote(str_arc_document_number) & ", " & get_quote(str_arc_version) & ", " & _
get_quote("Relation Name") & " , " & get_quote("TS to Doc New or Revise") & ", " & get_quote(vbNullString) & ")"

DoCmd.SetWarnings False
DoCmd.RunSQL str_sql
DoCmd.SetWarnings True
End If

Set rst = Nothing

Go to the top of the page
post Feb 22 2018, 02:01 PM

UA Admin
Posts: 34,110
Joined: 20-June 02
From: Newcastle, WA

Are you sure that that is the source of your problem (I assume there's a problem?)

Have you stepped through the code to see what is happening on each line?
Go to the top of the page
post Feb 22 2018, 02:04 PM

UtterAccess VIP
Posts: 10,629
Joined: 6-December 03
From: Telegraph Hill

Not much use if you just tell us:

str_sql = "select statement here"

Go to the top of the page
post Feb 22 2018, 03:00 PM

UA Admin
Posts: 34,110
Joined: 20-June 02
From: Newcastle, WA

I think that's there to initialize the string. It's set to valid SQL later.

Oh wait. It's being opened first, then another RST is set up.

Yeah. That's not logical without knowing what the actual SQL is.
This post has been edited by GroverParkGeorge: Feb 22 2018, 06:36 PM
Go to the top of the page
post Feb 23 2018, 11:47 AM

UtterAccess Editor
Posts: 16,296
Joined: 27-June 06
From: England (North East / South Yorks)

I certainly agree and speculate that perhaps the title of this thread should be:
Recordset Should Have Records But Eof And Bof Are True

i.e. I've run this SQL in a query window and got results, but in code EOF and BOF are true.

And so the SQL is vital, as you're very likely just non ACE OLEDB compatible. (We're all probably suspecting a Like clause needing to switch to ALike and a wildcard change ;-)

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th December 2018 - 10:42 AM