Updating Records in MS Access - Updating the column using ADO
(Page 4 of 4 )
While the earlier methods discussed the options available in the MS Access program for updating the records, records can also be updated programmatically using ADO. This can be carried out within the MS Access application, or from an independent Visual Basic, or an ASP application using the SQL update statement whose syntax is shown here.
UPDATE "table_name"
SET "column_1" = [new value]
WHERE {condition}
In order to access the properties, methods and events of ADO you need to add a reference to the ADO library as discussed in several earlier tutorials. Now to go to the load event of an MS Access form, type in the code shown in the next paragraph. Relevant sections which need some explanation are commented. Basically you should use the recordset object which takes the SQL update statement similar to the one produced by the MS Access query designer. You also display the field values before you run the update and again after you run the update in order to display the changes made. These are displayed using the debug statements.
Private Sub Form_Load()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:Documents and SettingsJayMy Documentscrud.mdb;" & _
"Persist Security Info=False"
Set conn = New ADODB.Connection
Set rst = New ADODB.Recordset
conn.Open strg
MsgBox ("Open")
Dim strsql As String
strsql = "Select * from TestProducts where ProdID=10"
rst.CursorLocation = adUseClient
rst.Open strsql, conn, adOpenKeyset, adLockOptimistic
MsgBox ("Cursor Location: " & rst.CursorLocation)
Dim strresult As String
strresult = ""
strresult = strresult + "Prod ID= " & rst.Fields(0).Value & vbCrLf
strresult = strresult + "Product Name= " & rst.Fields(1).Value & vbCrLf
strresult = strresult + "Quantity Per Unit= " & rst.Fields(2).Value &
vbCrLf
strresult = strresult + "Unit Price= " & rst.Fields(3).Value & vbCrLf
strresult = strresult + "Unit In Stock= " & rst.Fields(4).Value & vbCrLf
'Printout the columns for ProductID=10 before updating the values
Debug.Print strresult
rst.Close
'Change product name to 'Japanese Tea'
strupdate = "UPDATE TestProducts SET ProductName= 'Japanese Tea'
WHERE ProdID=10"
MsgBox (strupdate)
'Update the table changing column using update statement
rst.Open strupdate, conn, adOpenKeyset, adLockOptimistic
rst.Open strsql, conn, adOpenKeyset, adLockOptimistic
'reselect the same columns to verify if the change has taken place
strnew = ""
strnew = strnew + "Prod ID= " & rst.Fields(0).Value & vbCrLf
strnew = strnew + "Product Name= " & rst.Fields(1).Value & vbCrLf
strnew = strnew + "Quantity Per Unit= " & rst.Fields(2).Value & vbCrLf
strnew = strnew + "Unit Price= " & rst.Fields(3).Value & vbCrLf
strnew = strnew + "Unit In Stock= " & rst.Fields(4).Value & vbCrLf
'Print out the new values after the update has been made
Debug.Print strnew
rst.Close
conn.Close
End Sub
When the form loads, the table gets updated and the result of the debug statements written to the immediate window are shown in the next paragraph.
Product Name= Ikura
Quantity Per Unit= 12 - 200 ml jars
Unit Price= 31
Unit In Stock= 31
Prod ID= 10
Product Name= Japanese Tea
Quantity Per Unit= 12 - 200 ml jars
Unit Price= 31
Unit In Stock= 31
Summary
The MS Access query designer is a convenient tool for creating update action queries. It is also a good learning tool for studying data modifying SQL statements although the syntax is different. Browsing the data view of the table and making changes, Form and Data Access Pages based updates are other options. ADODB offers a programmatic way of modifying data which can use both types of CursorLocation property. Updating records using ADO enables you to use a lot more features than discussed here. Before you want to work with update queries make sure you consult the online help in MS Access and this Knowledge Base article on the Microsoft site.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
