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

Dreamweaver

Well-known member
Local time
Today, 11:15
Joined
Nov 28, 2005
Messages
2,466
I learnt from a lot of access books but they never told me this


Set Invo = Db.OpenRecordset("OtblInvoicesLineItems'", dbOpenDynaset)


But thinking about it all these years later after it being pointed out i can see it should have been


Set Invo = Db.OpenRecordset("SELECT * FROM OtblInvoicesLineItems WHERE [InvoiceID]='INV'", dbOpenDynaset) 'UPDATED 10/11/2018



yes InvoiceID is a text value
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 11:15
Joined
Sep 21, 2011
Messages
14,038
Wouldn't that really depend on what that query does?

For instance that criteria could already be in that query, or be using a form control on the form you are executing that code from.?

Is this in relation to that thread that just needs to get the recordset structure?
 

Dreamweaver

Well-known member
Local time
Today, 11:15
Joined
Nov 28, 2005
Messages
2,466
Wouldn't that really depend on what that query does?

For instance that criteria could already be in that query, or be using a form control on the form you are executing that code from.?

Is this in relation to that thread that just needs to get the recordset structure?


You pointed out i was allowing the full records to be drawn from a table without a limiting where as those queries are only used to add new records
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:15
Joined
Sep 21, 2011
Messages
14,038
You pointed out i was allowing the full records to be drawn from a table without a limiting where as those queries are only used to add new records

Yes, but at that time I was *thinking* that one needed the records for a particular ID?, the normal situation.

Then I realised that you were just doing it to get the structure of the recordset, and so for that you could have an empty recordset (at least in that instance) and that is why I suggested the WHERE 1=2 criteria.?

I've not tested it as a recordset, but as a straight query it returns no records, but shows the structure?
 

Dreamweaver

Well-known member
Local time
Today, 11:15
Joined
Nov 28, 2005
Messages
2,466
you are correct I have run it and agree with you as I have projects with tables of over a million entries and more out there it would speed it up I hope lol


I would test it but not my thing as using table.recordcount would force it to get records at least i think it would?


To be honest im not sure if db.recordset("table") would return the complete records or just the def.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 11:15
Joined
Sep 21, 2011
Messages
14,038
you are correct I have run it and agree with you as I have projects with tables of over a million entries and more out there it would speed it up I hope lol


I would test it but not my thing as using table.recordcount would force it to get records at least i think it would?


To be honest im not sure if db.recordset("table") would return the complete records or just the def.

Complete Records
Code:
Sub TestRecordset()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("transactions")
rst.MoveLast
Debug.Print rst.RecordCount
Debug.Print rst!ID

Set rst = Nothing
Set db = Nothing
End Sub

Debug.print showed 281 for both. That is the number of records in the table.

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

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT transactions.* from transactions WHERE 1 = 2", dbOpenDynaset)
If Not rst.EOF Then
    rst.MoveLast
End If
Debug.Print rst.RecordCount
Debug.Print rst.Fields.Count
rst.AddNew
rst!Date = Date
rst.Update
rst.Close

Set rst = Nothing
Set db = Nothing
End Sub

adds the record to an empty recordset.

So that is a quick way to do what you want, though I do not know if it has to read all the records to discover there are none to return, or Access gets clever. I do not have any large datasets, so i will leave that to you? :D
 

Dreamweaver

Well-known member
Local time
Today, 11:15
Joined
Nov 28, 2005
Messages
2,466
Access Clever never seen that in one sentance befor lol


I've attached a pic of my largest table you'll see why it maters when you look at the record number lol
 

Attachments

  • 2018-11-10 (1).png
    2018-11-10 (1).png
    57.5 KB · Views: 35
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:15
Joined
Feb 28, 2001
Messages
26,996
"Access Clever." Definitely an oxymoron.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:15
Joined
Sep 21, 2011
Messages
14,038
Doc_Man,

You are up on these things though.
If Access cannot recognise that the WHERE condition will always be false, then it will look through the whole recordset anyway? The only time saving then, will be that it will not bring any data into the recordset?, which leads me me to think a WHERE of autonumber = 0 might be a better option, as that will use an available index.?
 

Cronk

Registered User.
Local time
Today, 22:15
Joined
Jul 4, 2013
Messages
2,770
Fastest way of opening a recordset to add a record is to use a condition which equates to false.
Rather than having to put a condition that has to be evaluated (1=2), just use
Code:
select * from tableName where false;
Using
Code:
set rst= currentdb.openrecordset("<tableName>")
creates a recordset with all table records and positions the pointer to the last record
Using

Code:
set rst= currentdb.openrecordset("select * from <tableName>")
creates a recordset with all table records and positions the pointer to the first record, and you need to move the recordset position to the last record to get the recordcount.
Using

Code:
set rst= currentdb.openrecordset("select ID from <tableName>")
rst.movelast
where ID is the primary key will open the recordset faster because only the index is returned, not the data for other fields (but insignificant if only a relatively limited number of records).
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:15
Joined
Jan 20, 2009
Messages
12,849
Fastest way of opening a recordset to add a record is to use a condition which equates to false.

Why not use the OpenRecordset's Option parameter dbAppendOnly ?
 

Cronk

Registered User.
Local time
Today, 22:15
Joined
Jul 4, 2013
Messages
2,770
Code:
set rst= db.openrecordset("select * from tablename",,dbappendOnly)
Does that not open all records in the table with the pointer on the first record?
 

Dreamweaver

Well-known member
Local time
Today, 11:15
Joined
Nov 28, 2005
Messages
2,466
As with most things Access they are more than one way of skinning the beast I like the false as it seems correct to me but also the dbappendonly implys that the recordset would be empty but I'm just guessing there
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:15
Joined
Jan 20, 2009
Messages
12,849
Code:
set rst= db.openrecordset("select * from tablename",,dbappendOnly)
Does that not open all records in the table with the pointer on the first record?

Don't know but it would seem kind of dumb if it returned records.
 

Dreamweaver

Well-known member
Local time
Today, 11:15
Joined
Nov 28, 2005
Messages
2,466
The dbAppendOnly Gives error this operation is not valid for this object on the line .Addnew
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:15
Joined
Sep 21, 2011
Messages
14,038
Using
Code:
set rst= currentdb.openrecordset("<tableName>")
creates a recordset with all table records and positions the pointer to the last record
Cronk,

I have just used
Code:
Set rst = db.OpenRecordset("transactions")
and whilst it returned the number of records in the table without a movelast, when I debug printed the ID, it returned 1, the first record in the table.?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:15
Joined
Jul 9, 2003
Messages
16,244
I learnt from a lot of access books but they never told me this


So True! Hence what I try and do in my website which is to show you what I have never seen in the books..

Off the top of my head I think the strap line to my website is "What they don't teach you in the books"


Website here:-

www.niftyaccess.com


Sent from my SM-G925F using Tapatalk
 

Cronk

Registered User.
Local time
Today, 22:15
Joined
Jul 4, 2013
Messages
2,770
@Gasman
...returned the number of records in the table without a movelast, when I debug printed the ID, it returned 1, the first record in the table.?
My mistake. Positioned at first record, recordcount is 1
 

Users who are viewing this thread

Top Bottom