Open a recordest

[mswilson16]

I am using access2k and i need to know how to open a recordset. I have seen lots of posts about them but there is not one that will give me the EXACT code for opening a recordset.

I have seen that you can run a sql statement as the recordset, I can create that no trouble I just don't know how to open the recordset.

could some one please tell me the syntax for opening a recordset with the sql statement
"UPDATE [Deliver Note Item] SET [Deliver Note Item].[Stock Updated] = "YES" WHERE ((([Deliver Note Item].[Stock Updated])="no&quot)"

Thanks

Wilson

 

[rookery]

The normal procedure to open a Recordset (using DAO) is as follows:

Dim dbs as Database
Dim MyRs as Recordset

Set dbs = CurrentDB
Set MyRs = dbs.OpenRecordset("YourTableOrQueryName","Type&quot

where "Type" can be one of 5 options, but "dbOpenDynaset" always seems to work for my purposes.

Or alternatively you can usually just type the SQL into where "YourTableOrQueryName" is, but as yours is an UPDATE query I'm not sure if that would work. No doubt someone will correct me if I'm wrong.

 

[LittleSmudge]

HOWEVER - As you are using Access 2k you shouldn't be using DAO

DAO is the old technology that went out of date with Access97

You need to be using ADO

Okay here's the quick and easy guide to opening ADO Recordsets

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
( The above two lines CAN be done as one line :-
Dim rst As New ADODB.Recordset
The reason why the two line option is usually preferable is in the advanced lesson. - But don't be phased if you see it as one line in some examples. )

rst.ActiveConnection = CurrentProject.Connection
( This is roughly equivalent to "Set db = CurrentDb" and tells ADO that you are refering to data in the current database.
Refering to data in other databases is in the advanced lesson. )

If you are WRITING to the recordset ( Add or Edit ) you need
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
If you are Reading only you don't NEED them.

rst.Open "SELECT DeliverNoteItem FROM tblStockUpdated WHERE StockUpdated = 'No';"

Then do any work on the recordset here

rst.Close
Set rst = Nothing


The rst.Open statement takes a Select SQL string.

If you are sure that what you want to do is update the DeliveryNoteItem field as per the SQL string

"UPDATE [Deliver Note Item] SET [Deliver Note Item].[Stock Updated] = "YES" WHERE ((([Deliver Note Item].[Stock Updated])="no"))"

then you do not need a recordset - you just need

DoCmd.RunSQL "UPDATE [Deliver Note Item] SET [Deliver Note Item].[Stock Updated] = 'YES' WHERE ((([Deliver Note Item].[Stock Updated])='no'))"

Watch the SINGLE and Double quotes - which you use where is important.

Also - on the topic of b$&*$£y space characters in object names - see the FAQ

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190&CFID=76589512&CFTOKEN=96967025

'ope-that-'elps.

G LS

 

[mswilson16]

LittleSmudge,

That is exactly what I needed I was always told to do it using the first method. Cheers I million.

WIlson