Share

Updating Tables in an Access Database Using Excel VBA

In this article, we will see how to update records in Access database through Excel VBA using ActiveX Data Objects (ADO).

Let’s say you have a database named “Products". In that database, there is a table “ProductTable" that contains the product details of your business as shown below:

Now imagine if you have thousands of products and you get a price update for the products through an excel sheet as below:

Obviously, you cannot manually edit the price for each and every product. Besides being time-consuming, you are likely to make mistakes.

So, what we do instead is to access the Access DB through VB and update all the values using ADO.

Before you start, make sure you add the Microsoft ActiveX Data Object Library to your code. For that, in the VBA Editor, go to Tools > Reference and search for the above libraries.

Step 1: First, we start with creating a connection to the Access Database and open the connection using the code:

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Products.accdb;Persist Security Info=False;"
cn.Open

 

The things to note here:

  • For Excel, the provider is “ACE.OLEDB.12.0"
  • You need to provide the entire path to your access file in the data source (even if your Excel and Access files are in the same folder)

If you just need to get the data from Access database into Excel, you can simply import the data without using VBA. The article Excel Connect To External Data (Access Database) describes the process in detail.

Step 2: Create a new recordset that will store the data from the table, “ProductTable", in our case.

Set rstProducts = New ADODB.Recordset
With rstProducts
.Open "ProductTable", cn, adOpenKeyset, adLockPessimistic, adCmdTable
End With

 

Argument 3 – adOpenKeyset is the CursorType. When you use a keyset cursor you can’t see records that other users add, although records that other users delete are inaccessible from your Recordset. Data changes by other users are still visible.

Argument 4 – adLockPessimistic is the LockType. It indicates pessimistic locking, record by record. The provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing.

Argument 5 – adCmdTable implies that a SQL query is generated to return all rows from the table named in the source.

 

Step 3: Now we have the entire table contents in the record set. What we need to do next is, for each product (row) in Excel, we need to update the corresponding price in the Access Database. For that we need to filter the recordset based on the Product Name using:

rstProducts.Filter = "ProductName = '" & sProduct & "'"

 

where sProduct contains product name from each row in the Excel (one by one in a loop). You can add multiple criteria in the filter string using the “&" operator. Make sure that the quotes are taken care of.

So, now the recordset now contains either a single row with the desired product name or is empty if that product name does not exist.

Step 4: So, we first check whether the filtered resultset is empty using “EOF" property. If it is empty we use the .AddNew method to insert a new record into the table and set the values for the product name and price. (In our example, this piece of code will not be executed). Else, we simply update the price of the product found in the recordset.

If rstProducts.EOF Then
rstProducts.AddNew
rstProducts("rstProducts!ProductName").Value = sProduct
rstProducts("rstProducts!Price").Value = cPrice
Else
rstProducts!Price = cPrice
End If

 

Notice how we access individual columns in a recordset using the “!" operator. For example, “rstProducts!ProductName" implies the ProductName column in the recordset.

 

Step 5: Finally, we update the recordset and perform cleanup operations.

rstProducts.Update
rstProducts.Close
Set rstProducts = Nothing
cn.Close
Set cn = Nothing

This is how the table will look after the execution of the code.

See also, Modify Existing Data in Table, Access VBA Recordsets

You can also import an excel sheet into a table. Please refer to the article: Access VBA, Import Excel Worksheet to Existing Table

 

Here is the entire code put together. We are using a do while loop to loop through all the products in the Excel sheet. Also notice how we have declared the variable types with the ADODB prefix.

 

Sub updateAccess()
Dim cn As ADODB.Connection
Dim rstProducts As ADODB.Recordset
Dim sProduct As String
Dim cPrice As String
Dim counter As Integer
Application.DisplayAlerts = False
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Products.accdb;Persist Security Info=False;"
cn.Open
Set rstProducts = New ADODB.Recordset
With rstProducts
.Open "ProductTable", cn, adOpenKeyset, adLockPessimistic, adCmdTable
End With
sProduct = Sheet1.Cells(2, 1).Value ' row 1 contains column headings
counter = 0
Do While Not sProduct = ""
sProduct = Sheet1.Cells(2 + counter, 1).Value
cPrice = Sheet1.Cells(2 + counter, 2).Value
rstProducts.Filter = "ProductName = '" & sProduct & "'"
If rstProducts.EOF Then
rstProducts.AddNew
rstProducts("rstProducts!ProductName").Value = sProduct
rstProducts("rstProducts!Price").Value = cPrice
Else
rstProducts!Price = cPrice
End If
rstProducts.Update
counter = counter + 1
sProduct = Sheet1.Cells(2 + counter, 1).Value
Loop
rstProducts.Close
Set rstProducts = Nothing
cn.Close
Set cn = Nothing
Application.DisplayAlerts = True
End Sub

Thus, we can easily connect to an Access database using Excel VBA and perform basic operations on the data tables.

Leave a Reply

Your email address will not be published. Required fields are marked *