Updating a Specific database record

  • Thread starter Thread starter Glenn
  • Start date Start date
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
 
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:
 
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?
 
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:
 
Back
Top