Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


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

Module

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.

Table Updated

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.

Before ADO Delete

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.

After ADO Delete

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.

Tools ADO 2.x

Create a query to preview the data for the 1st recordset.

Recordset1

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.

ADOX Table

ADO is a quick way to automate some of the basic tasks in VBA code in Access databases.