Updating a Specific database record

G

Glenn

I found the following script to create a new record in access. What I'm
wondering is, is there a way to modify it to update a specific record in a
table?

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = \\laf\stoodup\inventory23.mdb"

objRecordSet.Open "SELECT * FROM GeneralProperties" , _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.AddNew
objRecordSet("ComputerName") = "Rick"
objRecordSet("Department") = "Construction"
objRecordSet("OSName") = "Windows XP"
objRecordSet("OSVersion") = "5.1.2600"
objRecordSet("OSManufacturer") = "Microsoft Corporation"
objRecordSet.Update

objRecordSet.Close
objConnection.Close
 
N

Nick Coe \(UK\)

First of all add a WHERE clause to your SQL SELECT to get
the row you want.

Second REM out or remove the objRecordSet.AddNew, then just
set values as you are already doing.

It is possible to directly update values by sending
parameters FieldName(s), Value(s) to the Update method
itself if you wish.

--
Nick Coe (UK)
http://www.alphacos.co.uk/




In Glenn typed:
 
G

Glenn

Okay. Did that and it worked great. Now for the next step - I would like a
pop up box to come up to ask me for the ID of the row that I want to update,
along with the info for the fields listed. So, how do I modify this part of
the script:

objRecordSet.Open "SELECT * FROM GeneralProperties WHERE ID=1" , _
objConnection, adOpenStatic, adLockOptimistic

To replace the one with the user input?
 
N

Nick Coe \(UK\)

Look up parameter_query in the Access Help.

That will set you on the right track. Once familiar with
the principle of parameter queries you can develop some
fairly complex form/popup options to feed into a query's
criteria.

--
Nick Coe (UK)
http://www.alphacos.co.uk/




In Glenn typed:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top