UPDATE

[Onion]

Trying to find a way to update fields in a table. Example syntax doesn't
work in Access 2000 using code builder for VB 6.03. I don't even find any
reference to the "UPDATE" SQL command in Access 2k help files. Very
frustrating it's taking so much effort to write simple code to change the
value of a field in a table....

The following code samples produce the error: "User defined type - not
defined"

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
---or---
Dim dbs As Database
Dim qdf As QueryDef
...
dbs.Execute "UPDATE tblCustomer" _
& "SET fname = 2" _
& "WHERE ReqID = y"

-------------------------------------------------------------------------------------------------
When I try to simply add the lines below without the "dbs.Execute", I get
the error: " Expected: Line Number ... or End of Line"

"UPDATE tblCustomerRequirements" _
& "SET fname = 2" _
& "WHERE RequirementsID = y"

Seems like this should work fine.

[pietlinden]

If you're going to use SQL, then do that. If you want to use a canned
query, then that's fine too. But you can't really mix them.

dim strSQL as string
strSQL = "UPDATE tblCustomerRequirements SET fname = 2 WHERE
RequirementsID = y"
DBEngine(0)(0).Execute strSQL, dbFailOnError

What's y? A variable? I would hope so, because otherwise, this makes
even less sense. In that case, it's:

dim strSQL as string
strSQL = "UPDATE tblCustomerRequirements SET fname = 2 WHERE
RequirementsID =" & y
DBEngine(0)(0).Execute strSQL, dbFailOnError

Does your query work if you build it in code, output it to the
immediate window, and copy that to a query, and run it?

[Onion]

Thanks for the help.

The code you provided (below) produces an error dialogue - "Too few
parameters: Expected 1".
When I debug, it highlights the "DBEngine" statement below.

dim strSQL as string
strSQL = "UPDATE tblCustomerRequirements SET fname = 2 WHERE
RequirementsID =" & y
->DBEngine(0)(0).Execute strSQL, dbFailOnError
<pi********@hotmail.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

If you're going to use SQL, then do that. If you want to use a canned
query, then that's fine too. But you can't really mix them.

dim strSQL as string
strSQL = "UPDATE tblCustomerRequirements SET fname = 2 WHERE
RequirementsID = y"
DBEngine(0)(0).Execute strSQL, dbFailOnError

What's y? A variable? I would hope so, because otherwise, this makes
even less sense. In that case, it's:

dim strSQL as string
strSQL = "UPDATE tblCustomerRequirements SET fname = 2 WHERE
RequirementsID =" & y
DBEngine(0)(0).Execute strSQL, dbFailOnError

Does your query work if you build it in code, output it to the
immediate window, and copy that to a query, and run it?