How to create a Updatable recordset

  • Thread starter Thread starter shirley Lu
  • Start date Start date
S

shirley Lu

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

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

Thanks
Click to expand...

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]
 
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
 
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!)



shirley Lu said:
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




John Vinson said:
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]
Click to expand...
Click to expand...
 
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?
Click to expand...

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]
 
Doug and John, can you use an = sign with a wildcard? i was thinking you had
to use Like.


John Vinson said:
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?
Click to expand...

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]
Click to expand...
 
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!)



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


John Vinson said:
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?
Click to expand...

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]
Click to expand...
Click to expand...
 
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>


Douglas J. Steele said:
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!)



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


John Vinson said:
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]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top