Excel Read Write Access DB (CRUD)

Microsoft Excel can integrate with other Office applications to leverage their capabilities and functionality. We have already seen several integrations of Excel and other Office applications in this blog (integrate with Outlook to send emails, with Word to export worksheet data as document, etc). We can also integrate with Access to get and manage data from an Access database. In this post we see how to create, read, update, and delete (CRUD) data in an Access DB from Excel.

Connect to Access DB

Whenever we want to read or update data in a DB, we need first a secure connection to the DB. To connect to an Access DB from Excel, we need to add the Microsoft ActiveX Data Objects (ADO) library to the references of the VBA project.

We define a new ADO connection as indicated below. Note that this is declared at module level because it will be used in various macros (connect macro, close connection macro, and respective CRUD macros).

 Dim conn As New ADODB.Connection

 

We need to specify the path and name of the Access DB. The code below assumes the Access DB is in the same directory where the Excel file is. The name of the DB is “Database1.accdb”. That path and name can be updated as needed.

 DBpath = ThisWorkbook.Path & "\Database1.accdb"

 

We need to specify the provider and connection string before opening the connection to the Access DB. The code below establishes a connection to an Access DB without password.

 Prvd = "Microsoft.ACE.OLEDB.12.0;"
 With conn
   .Provider = Prvd
   .ConnectionString = "Data Source=" & DBpath & ";Persist Security Info=False"
   .Open
 End With

Add the following line to the connection string to connect to the Access DB with password (psw variable in code below).

 ";Jet OLEDB:Database Password=" & psw

 

Read from DB

Once connected to the Access DB, we can read data from any table in the DB creating and opening a query in the ADO DB connection established earlier (conn). We also need to define a DB record set variable (rec).

 Dim rec As New ADODB.Recordset

Then we build the query using structured query language (SQL). As an example, the query below selects all the records in table “Users”. This is a table in the target DB (Database1).

 query = "SELECT * from Users;"

 

Finally, we open a record set query within the established connection. The variable “conn” was defined earlier when connecting to the Access DB.

 rec.Open query, conn

 

Now we can get any data from that record set and, for example, we can write it to the Excel worksheet. The code below writes the first and second entries (name and country of a user) in the record set (which has all data from table Users) in column A and B of the active sheet.

 If (rec.RecordCount <> 0) Then
     Do While Not rec.EOF
          r = r + 1
          Range("A" & r).Value = rec.Fields(1).Value
          Range("B" & r).Value = rec.Fields(2).Value
          rec.MoveNext
     Loop
 End If

 

That’s basically how we read Access DB data from Excel. That’s useful when dealing with many tables or large datasets. Excel is then used to manage records in the Access DB.

We need to close the record set once finished and close the connection if there are no further operations on the Access DB.

 rec.Close
 conn.Close

  

Create record in DB

The process repeats to create (or update or delete) records in the Access DB. We define a record set and create a query with SQL. In this case, we need to specify the table, fields, and values to be added. The example below adds the name and country of a new user in the table “Users” of the DB.

 query = "INSERT INTO Users(Name, Country) Values('John', 'USA')"
 conn.Execute query

 

There is another way to create a record that does not require building the SQL query. This other option uses properties of the record set. Hence, we need to define the record set as done earlier.

 rec As New ADODB.Recordset

Then we open a record set for the DB connect with specific settings and use the properties AddNew and Fields to specify each new record. The code below adds the name, country, and age of a new user in table Users.

 rec.Open "Users", conn, adOpenKeyset, adLockOptimistic, adCmdTable
 With rec
     .AddNew
    .Fields("Name") = "Thomas"
    .Fields("Country") = "Germany"
    .Fields("Age") = 43
    .Update
 End With
 rec.Close

It’s good practice to always close the record set and also the DB connection if not used anymore (conn.Close).

We can use data in the Excel worksheet to create a new record in a particular table of the Access DB. This is useful when dealing with large sets of data and several tables in Excel.

 

Update and delete record in DB

The same process is used to update or delete a record in the Access DB. The query is defined accordingly using SQL. For example, the code below creates and executes a query that updates the name and country for a user with ID 15 in the table Users of the Access DB.

 query = "UPDATE Users SET Name='Peter', Country='Germany' WHERE ID=15"
 conn.Execute query

 

The ID is a unique identifier of a record in a table in the Access DB. Similarly, we can delete a given record in a table. The code below creates and executes a query that deletes the user with ID 28 in the table Users of the Access DB.

 query = "DELETE FROM Users WHERE ID=28"
 conn.Execute query

We can update or delete records based on data in the Excel worksheet. The data should be put in variables that can be concatenated in the query string. Thus, we can manage several tables and large sets of data with Excel and Access.

 

Click here to download Excel-Access CRUD Integration