How to create a Updatable recordset

[shirley Lu]

How to create a updatable recordset, when I update this recordset, and then
automatically update the relative table?

Thanks

 

[John Vinson]

How to create a updatable recordset, when I update this recordset, and then
automatically update the relative table?

Thanks

Context? What are you trying to do, where, and what problems have you
had?

Most Queries are updateable. You can force a query not to allow
updating by making it a Totals query, setting its Unique Value
property to true, adding too many tables, and other ways. If you mean
a Recordset in VBA code, then either

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)

or

<ditto> , dbOpenTable)

will give you an updateable recordset.

John W. Vinson[MVP]

 

[shirley Lu]

My Code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"
Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

CurrentDb.Execute "UPDATE rs SET [Status]='Close';"

but I cann't update the status value. why?

Thanks

 

[Douglas J. Steele]

You can't update recordsets like that. However, unless you're doing
something else with the recordset, it doesn't actually look as though one is
required.

Try:

CurrentDb.Execute "UPDATE WorkOrders SET [Status]='Close' WHERE [WONo] = '"
& txtWONo & "*'")



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My Code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"
Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

CurrentDb.Execute "UPDATE rs SET [Status]='Close';"

but I cann't update the status value. why?

Thanks

Context? What are you trying to do, where, and what problems have you
had?

Most Queries are updateable. You can force a query not to allow
updating by making it a Totals query, setting its Unique Value
property to true, adding too many tables, and other ways. If you mean
a Recordset in VBA code, then either

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("tablename", dbOpenDynaset)

or

<ditto> , dbOpenTable)

will give you an updateable recordset.

John W. Vinson[MVP]

 

[John Vinson]

My Code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"
Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

CurrentDb.Execute "UPDATE rs SET [Status]='Close';"

but I cann't update the status value. why?

A Recordset is a VBA object - the JET database engine knows nothing
about its existance. The Recordset *uses* JET, but JET doesn't use
recordsets - only tables or queries!

The CurrentDb.Execute method can execute SQL queries which can
reference stored Queries or Tables, but rs is neither of these and
simply isn't available to the query executor.

Just use Douglas' suggestion - cut out the middleman, and include the
criteria in the Update query rather than doing it in two steps.

John W. Vinson[MVP]

 

[tina]

Doug and John, can you use an = sign with a wildcard? i was thinking you had
to use Like.

My Code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"
Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

CurrentDb.Execute "UPDATE rs SET [Status]='Close';"

but I cann't update the status value. why?

A Recordset is a VBA object - the JET database engine knows nothing
about its existance. The Recordset *uses* JET, but JET doesn't use
recordsets - only tables or queries!

The CurrentDb.Execute method can execute SQL queries which can
reference stored Queries or Tables, but rs is neither of these and
simply isn't available to the query executor.

Just use Douglas' suggestion - cut out the middleman, and include the
criteria in the Update query rather than doing it in two steps.

John W. Vinson[MVP]

 

[Douglas J. Steele]

Ooops! You're absolutely correct, Tina (unless for some reason, such as bar
coding, the asterisk is actually being stored in the database)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug and John, can you use an = sign with a wildcard? i was thinking you
had
to use Like.

My Code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"
Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

CurrentDb.Execute "UPDATE rs SET [Status]='Close';"

but I cann't update the status value. why?

A Recordset is a VBA object - the JET database engine knows nothing
about its existance. The Recordset *uses* JET, but JET doesn't use
recordsets - only tables or queries!

The CurrentDb.Execute method can execute SQL queries which can
reference stored Queries or Tables, but rs is neither of these and
simply isn't available to the query executor.

Just use Douglas' suggestion - cut out the middleman, and include the
criteria in the Update query rather than doing it in two steps.

John W. Vinson[MVP]

 

[tina]

oops, myself! you're right Doug, the asterisk could be part of the field
value. when i saw it, my brain didn't serve up anything except "wildcard" -
how's that for thinking *in* the box? <g>

Ooops! You're absolutely correct, Tina (unless for some reason, such as bar
coding, the asterisk is actually being stored in the database)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug and John, can you use an = sign with a wildcard? i was thinking you
had
to use Like.

My Code:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

MySQL="Select * From WorkOrders WHERE [WONo] = '" & txtWONo & "*' ;'"
Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)

CurrentDb.Execute "UPDATE rs SET [Status]='Close';"

but I cann't update the status value. why?

A Recordset is a VBA object - the JET database engine knows nothing
about its existance. The Recordset *uses* JET, but JET doesn't use
recordsets - only tables or queries!

The CurrentDb.Execute method can execute SQL queries which can
reference stored Queries or Tables, but rs is neither of these and
simply isn't available to the query executor.

Just use Douglas' suggestion - cut out the middleman, and include the
criteria in the Update query rather than doing it in two steps.

John W. Vinson[MVP]