Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-11-2018, 03:47 AM   #16
sonic8
Newly Registered User
 
Join Date: Oct 2015
Posts: 122
Thanks: 23
Thanked 37 Times in 36 Posts
sonic8 is on a distinguished road
Re: Books don't alway tell you everything!

Quote:
Originally Posted by Galaxiom View Post
Don't know but it would seem kind of dumb if it returned records.
OpenRecordset does not return any records with the dbAppendOnly argument set.

__________________
New Video:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
, recorded at the AEK conference, Nuremberg, Oct. 2018.
sonic8 is offline   Reply With Quote
The Following User Says Thank You to sonic8 For This Useful Post:
Gasman (11-11-2018)
Old 11-11-2018, 06:44 AM   #17
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: Books don't alway tell you everything!

The dbAppendOnly Gives error this operation is not valid for this object on the line .Addnew
__________________
After 20 years working with access i have no more hair to give.
MickJav is offline   Reply With Quote
Old 11-11-2018, 07:17 AM   #18
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Books don't alway tell you everything!

Quote:
Originally Posted by Cronk View Post
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.?

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 11-11-2018, 08:44 AM   #19
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 9,517
Thanks: 363
Thanked 782 Times in 747 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Books don't alway tell you everything!

Quote:
Originally Posted by MickJav View Post
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
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 11-11-2018, 09:05 AM   #20
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,908
Thanks: 2
Thanked 411 Times in 404 Posts
Cronk will become famous soon enough
Re: Books don't alway tell you everything!

@Gasman
Quote:
...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
Cronk is offline   Reply With Quote
Old 11-11-2018, 01:32 PM   #21
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Books don't alway tell you everything!

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
MickJav (11-11-2018)
Old 11-11-2018, 01:42 PM   #22
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: Books don't alway tell you everything!

Looks like WHERE wins nice job gasman

__________________
After 20 years working with access i have no more hair to give.
MickJav is offline   Reply With Quote
Old 11-11-2018, 01:49 PM   #23
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Books don't alway tell you everything!

Quote:
Originally Posted by MickJav View Post
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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 11-11-2018, 04:47 PM   #24
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,291
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Books don't alway tell you everything!

Quote:
Originally Posted by MickJav View Post
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 is offline   Reply With Quote
Old 11-11-2018, 05:03 PM   #25
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,291
Thanks: 78
Thanked 1,409 Times in 1,329 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Books don't alway tell you everything!

Quote:
Originally Posted by MickJav View Post
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)
Galaxiom is offline   Reply With Quote
The Following 2 Users Say Thank You to Galaxiom For This Useful Post:
Gasman (11-12-2018), MickJav (11-11-2018)
Old 11-11-2018, 10:27 PM   #26
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 554
Thanks: 24
Thanked 5 Times in 5 Posts
MickJav is on a distinguished road
Re: Books don't alway tell you everything!

Quote:
Originally Posted by Galaxiom View Post
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

__________________
After 20 years working with access i have no more hair to give.

Last edited by MickJav; 11-11-2018 at 10:40 PM.
MickJav is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Books! Leo_Coroneos General 23 12-03-2017 01:15 PM
Books on SQL omgjtt Queries 1 06-06-2011 12:01 PM
VBA Books DavidRS Modules & VBA 2 02-07-2005 02:25 AM
Im alway 1 step to late alaric Forms 8 09-20-2004 04:41 PM
Books. Crilen007 General 2 08-02-2004 11:44 AM




All times are GMT -8. The time now is 08:36 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World