Books don't alway tell you everything! (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 12:58
Joined
Sep 21, 2011
Messages
14,041
Ok, to avoid confusion, I created the sub below and then ran it.
The last set does not allow access to the ID field as no current record, but in this thread, that did not matter as we were adding a new record.

I created
Code:
Sub TestRecordset()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb

Set rst = db.OpenRecordset("transactions")
Debug.Print "Table with recordcount " & rst.RecordCount
Debug.Print "Table record ID " & rst!ID
rst.Close

Set rst = db.OpenRecordset("select * from transactions")
Debug.Print "Select from table with recordcount " & rst.RecordCount
Debug.Print "Select from table record ID " & rst!ID
rst.Close

Set rst = db.OpenRecordset("select * from transactions where ID = 0")
Debug.Print "Select from table ID = 0 with recordcount " & rst.RecordCount
'Debug.Print "Select from table ID = 0 record ID " & Nz(rst!ID, 0)
rst.Close

Set rst = Nothing
Set db = Nothing
End Sub

The output was
Code:
Table with recordcount 282
Table record ID 1

Select from table with recordcount 1
Select from table record ID 1

Select from table ID = 0 with recordcount 0
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:58
Joined
Sep 21, 2011
Messages
14,041
Looks like WHERE wins nice job gasman

Especially if you can ensure it does not return any records? using the autonumber field and zero.?

I also tested my theory of EOF and BOF being checked together, where I have seen tests for both before processing something, and found that in these cases EOF is sufficient to see if any records returned.

Learning all the time. :D
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:58
Joined
Jan 20, 2009
Messages
12,849
Looks like WHERE wins nice job gasman

WHERE wins which race exactly?

When opening a recordset with Option dbAppendOnly the RecordCount is zero. The engine doesn't have to evaluate anything. There is no need to test EOF to be sure it is empty.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:58
Joined
Jan 20, 2009
Messages
12,849
The dbAppendOnly Gives error this operation is not valid for this object on the line .Addnew

That will happen if you use the dbAppendOnly as the second parameter (Recordset Type) when it should be the third (Recordset Option). As the second parameter it is interpreted as dbOpenForwardOnly.

Code:
Set rs = CurrentDb.OpenRecordset("sometable", dbOpenDynaset, dbAppendOnly)
 

Dreamweaver

Well-known member
Local time
Today, 12:58
Joined
Nov 28, 2005
Messages
2,466
That will happen if you use the dbAppendOnly as the second parameter (Recordset Type) when it should be the third (Recordset Option). As the second parameter it is interpreted as dbOpenForwardOnly.

Code:
Set rs = CurrentDb.OpenRecordset("sometable", dbOpenDynaset, dbAppendOnly)



Just run a quick test as had 5 mins lol it returns 0 records using both just the table name and also the select * both give error 3021 no current record when Debug.Print "Select from table record ID " & rst!InvoiceID


so , dbOpenDynaset, dbAppendOnly wins hands down back to updateing lol
 
Last edited:

Users who are viewing this thread

Top Bottom