MS Access Using ADO & ADOX
Using ADO and ADOX with Data in a Table
This article will show how to use ADO to Insert, Update and Delete data in a table how to use the ADO command object, and how to use an ADOX catalog and table.
This article will use MS Access and a local table called tblSalesLabels which was constructed with data from the AdventureWorks database.
MS Access Module to Update Rows in a Table Using ADO
This exercise will use the tblSalesLabels table and an ADO recordset and connection to read a field from the recordset (CountryRegionName) and based on a condition, update the a different field in the recordset (IntlAddr) to Yes or No.
Add a Module to the Access database
Add code to the module to create a sub that will
- create the connection and recordset
- a while loop to go through the recordset and get the CountryRegionName field
- A conditional If Then Else statement to test if the CountryRegionName field <> United States or is Not Null
- Update the recordset.
Public Sub adodbUpdate()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sSql As String
Dim sCountry As String
On Error GoTo exitspot
sSql = "SELECT * FROM tblSalesLabels"
Set rs = New ADODB.Recordset 'set the new recordset
Set cn = CurrentProject.Connection 'set the connection
'open the recordset
rs.Open sSql, cn, adOpenDynamic, adLockOptimistic
Do While Not rs.EOF 'loop to go through recordset row by row
sCountry = rs.Fields("CountryRegionName").Value
Debug.Print sCountry
If (sCountry <> "United States" Or Nz(sCountry, "") <> "") Then
rs.Fields("IntlAddr").Value = True
Else
rs.Fields("IntlAddr").Value = False
End If
rs.Update
rs.MoveNext
Loop
Set rs = Nothing
Set cn = Nothing
Debug.Print "Done"
exitspot:
Debug.Print Err.Description
End Sub
Verify that the table was updated with a quick query.
Delete Rows in a Table with MS Access ADO
This exercise will use the tblSalesLabels table and an ADO recordset and connection to delete rows based on criteria met when the PerformanceRating column = "Poor".
Add a new sub to the module created.
- Create the ADO recorset and connection
- a while loop to go through the recordset and get the PerformanceRating field
- A conditional If Then Else statement to test if the PerformanceRating field = Poor or is Null
- Delete the row and Update the recordset.
Verify the values in tblSalesLabels before the code is executed with a query.
Public Sub adodbDelete()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sSql As String
Dim sRating As String
On Error GoTo exitspot
sSql = "SELECT * FROM tblSalesLabels"
Set rs = New ADODB.Recordset 'set the new recordset
Set cn = CurrentProject.Connection 'set the connection
'open the recordset
rs.Open sSql, cn, adOpenDynamic, adLockOptimistic
Do While Not rs.EOF 'loop to go through recordset row by row
sRating = rs.Fields("PerformanceRating").Value
Debug.Print sRating
If (sRating = "Poor") Then
rs.Delete
End If
rs.MoveNext
Loop
Set rs = Nothing
Set cn = Nothing
Debug.Print "Done"
exitspot:
Debug.Print Err.Description
End Sub
Verify the records with the PerformanceRating = Poor were deleted from the table.
Insert to a Table with MS Access ADO and ADOX
This exercise will use the tblSalesLabels table and 2 ADO recordsets and connection to read a field from the 1st recordset (PerformanceRating) and based on a condition ("Outstanding"), will insert part of the recordset data into the 2nd recordset. The 2nd recordset will be based on a table called tblSalesOutstanding, which will be created using an ADOX catalog and table in a Sub so it may be recreated each time.
In the VBA window, go to Tools, References and add ADO 2.x for DDL and Security to the Project.
Create a query to preview the data for the 1st recordset.
Add code to a the module to create the ADOX catalog and table, "tblSalesOutstanding". The columns to be captured from the 1st recordset will be the SalesPersonID, SalesName and SalesYTD.
Public Sub CreateTablesADO()
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
'Create the catalog object
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
On Error Resume Next
cat.Tables.Delete "tblSalesOutstanding"
On Error GoTo 0
'create the table and columns
Set tbl = New ADOX.Table
tbl.Name = "tblSalesOutstanding"
tbl.Columns.Append "SalesID", adInteger
tbl.Columns.Append "SalesName", adVarWChar, 100
tbl.Columns.Append "SalesYTD", adCurrency
cat.Tables.Append tbl
cat.Tables.Refresh
cat.ActiveConnection = Nothing
Set tbl = Nothing
Set cat = Nothing
Debug.Print "Done"
End Sub
An alternate way to create the table, tblSalesOutstanding would be to use the ADODB command object
Public Sub createTableCmd()
Dim cmd As New ADODB.Command
With cmd
.CommandText = "CREATE TABLE tblSalesOutstanding (SalesID Int, SalesName text,
SalesYTD currency)"
.CommandType = adCmdUnknown
.ActiveConnection = CurrentProject.Connection
End With
cmd.Execute
Set cmd = Nothing
End Sub
Add the sub to SELECT the data from tblSalesLabels into the 1st recordset and set the 2nd recordset up as the table, tblSalesOutstanding.
- Create 1 ADO connection and 2 recordsets
- a while loop to go through the recordset and get the PerformanceRating field
- A conditional If Then Else statement to test if the PerformanceRating field = Outstanding
- Use the AddNew method of the 2nd recordset.
- Set the fields from the 2nd recordset to the fields from the 1st recordset
- Update the recordset
Public Sub adodbInsert()
Dim rs As ADODB.Recordset, rs2 As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sSql As String
Dim sRating As String
On Error GoTo exitspot
sSql = "SELECT * FROM tblSalesLabels"
Set rs = New ADODB.Recordset 'set the new recordsets
Set rs2 = New ADODB.Recordset
Set cn = CurrentProject.Connection 'set the connection
'open the recordsets
rs.Open sSql, cn, adOpenDynamic, adLockOptimistic
sSql = "tblSalesOutstanding"
rs2.Open sSql, cn, adOpenDynamic, adLockOptimistic, adCmdTable
Do While Not rs.EOF 'loop to go through recordset row by row
sRating = rs.Fields("PerformanceRating").Value
Debug.Print sRating
If (sRating = "Outstanding") Then
rs2.AddNew
rs2!SalesID = rs.Fields("SalesPersonID").Value
rs2!SalesName = rs.Fields("SalesName").Value
rs2!SalesYTD = rs.Fields("SalesYTD").Value
rs2.Update
End If
rs.MoveNext
Loop
Set rs = Nothing
Set rs2 = Nothing
Set cn = Nothing
Debug.Print "Done"
exitspot:
Debug.Print Err.Description
End Sub
Execute the code in the Immediate window and open the table to verify that the data was successfully inserted.
ADO is a quick way to automate some of the basic tasks in VBA code in Access databases.