Excel Vba Write to Access Database

---

Introduction

---

We have seen in the past how to run Access queries from Excel, as well as how to export Access data to Excel. It's time now to learn how to add records to an existing Access table. In the next paragraph, you will find a VBA code snippet used from Excel to add data into an existing Access table. The idea behind the code is the following:

• Create and open a connection to the Access database.
• Create and open a recordset that will contain the table data.
• Loop through Excel data and add them to the recordset (row by row).
• Update the recordset (row by row).
• Close both recordset and connection.

Before we dive into the VBA code, let's see how to accomplish the same thing, but manually, shall we?

---

How to manually add records into Access table from Excel

---

The 6-step importing process can be done by using the Access wizard that was created for this reason.

Step 1: Open the Access database, select the "External Data" tab in the Ribbon, and in the group "Import & Link," click the Excel button.

Step 2: Click the "Browse" button and find the spreadsheet file that contains the data to be imported. Click the "Append a copy of the records to the table" radio button, and from the dropdown, select the appropriate table (in this case, "Customers"). Then, click the "OK" button.

Step 3: If your spreadsheet contains multiple sheets, then you will have to click on the "Show Worksheets" radio button, select the appropriate sheet (here "Sheet1") and click the "Next" button. Note that this form will not appear if the selected spreadsheet contains only a single worksheet.

Step 4: If your headers are in the first row, the corresponding checkbox will already be checked on the next screen. Click the "Next" button.

Step 5: In the next form of the wizard, check the "I would like a wizard to analyze my table after importing the data." If you like, or you can click the "Finish" button.

Step 6: In the latter case, the wizard's final form will ask you if you want to save the import step. You can click the "Close" button.

Congratulations! You just imported the Excel data into the Access table.

---

VBA code

---

As the above image shows, two things should be carefully considered when using this VBA code:

1. The Excel sheet headers should match the Access table ones (e.g., cell A1: FirstName, Access header: FirstName).
2. The Excel sheet data should be of the same data type as the ones in the Access table (e.g., you cannot add a string value in an integer field).

            Option Explicit  Sub AddRecordsIntoAccessTable()              '-----------------------------------------------------------------------------     'The macro opens the Sample.accdb database and adds the 7 rows from the sheet     '"Excel Data" in the "Customers" table of the database.     'The code uses late binding, so no reference to external library is required.          'Written By:    Christos Samaras     'Date:          27/06/2020     'E-mail:        [email protected]     'Site:          https://myengineeringworld.net     '-----------------------------------------------------------------------------      'Declaring the necessary variables.     Dim accessFile  As String     Dim accessTable As String     Dim sht         As Worksheet     Dim lastRow     As Long     Dim lastColumn  As Integer     Dim con         As Object     Dim rs          As Object     Dim sql         As String     Dim i           As Long     Dim j           As Integer                  'Disable the screen flickering.     Application.ScreenUpdating = False          'Specify the file path of the accdb file. You can also use the full path of the file like this:     'AccessFile = "C:\Users\Christos\Desktop\Sample.accdb"     accessFile = ThisWorkbook.Path & "\" & "Sample.accdb"               'Ensure that the Access file exists.     If FileExists(accessFile) = False Then         MsgBox "The Access file doesn't exist!", vbCritical, "Invalid Access file path"         Exit Sub     End If          'Set the name of the table you want to add the data.     accessTable = "Customers"                      'Set the worksheet that contains the data.     On Error Resume Next     Set sht = ThisWorkbook.Sheets("Excel Data")     If Err.Number <> 0 Then         MsgBox "The given worksheet does not exist!", vbExclamation, "Invalid Sheet Name"         Exit Sub     End If     Err.Clear              'Find the last row and last column in the given worksheet.     With sht         lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row         lastColumn = .Cells(1, .Columns.Count).End(xlToLeft).Column     End With          'Check if there are data in the worksheet.     If lastRow < 2 Or lastColumn < 1 Then         MsgBox "There are no data in the given worksheet!", vbCritical, "Empty Data"         Exit Sub     End If              'Create the ADODB connection object.     Set con = CreateObject("ADODB.connection")          'Check if the object was created.     If Err.Number <> 0 Then         MsgBox "The connection was not created!", vbCritical, "Connection Error"         Exit Sub     End If     Err.Clear          'Open the connection.     con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessFile          'Create the SQL statement to retrieve the table data (the entire table).     sql = "SELECT * FROM " & accessTable          'Create the ADODB recordset object.     Set rs = CreateObject("ADODB.Recordset")          'Check if the object was created.     If Err.Number <> 0 Then         Set rs = Nothing         Set con = Nothing         MsgBox "The recordset was not created!", vbCritical, "Recordset Error"         Exit Sub     End If     Err.Clear                   'Set the necessary recordset properties.     rs.CursorType = 1   'adOpenKeyset on early binding     rs.LockType = 3     'adLockOptimistic on early binding              'Open the recordset.     rs.Open sql, con          'Add the records from Excel to Access by looping through the rows and columns of the given worksheet.     'Here the headers are in the row 1 and they are identical to the Access table headers.     'This is the reason why, for example, there are no spaces in the headers of the sample worksheet.     For i = 2 To lastRow         rs.AddNew         For j = 1 To lastColumn             'This is how it will look like the first time (i = 2, j = 1):             'rs("FirstName") = "Bob"             rs(sht.Cells(1, j).Value) = sht.Cells(i, j).Value         Next j         rs.Update     Next i              'Close the recordet and the connection.     rs.Close     con.Close          'Release the objects.     Set rs = Nothing     Set con = Nothing          'Re-enable the screen.     Application.ScreenUpdating = True      'Inform the user that the macro was executed successfully.     MsgBox lastRow - 1 & " rows were successfully added into the '" & accessTable & "' table!", vbInformation, "Done"      End Sub  Function FileExists(FilePath As String) As Boolean       '--------------------------------------------------     'Checks if a file exists (using the Dir function).     '--------------------------------------------------       On Error Resume Next     If Len(FilePath) > 0 Then         If Not Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True     End If     On Error GoTo 0   End Function                      

The code uses the Microsoft ActiveX Data Objects (ADO) library, but in late binding, so no external reference is required.

---

Downloads

---

The zip file contains an Excel workbook containing the VBA code presented above, a second workbook that can be used to import the data manually, as well as a sample Access database. The workbooks can be opened with Excel 2007 or newer.

---

Final thoughts

---

I know that the manual way (i.e., using the wizard) may seem easier than the VBA approach. Indeed, for cases where you want to add records from one table with "static" data, this is probably the preferable way to do it. However, if the spreadsheet data are the intermediate results of a longer calculating process, then the VBA approach, adjusted accordingly, will probably offer more flexibility. The final decision is up to you!

---

Read also

---

Running Access Queries From Excel Using VBA
Export A Large Access Table/Query To Excel

Page last modified: October 3, 2021

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Add Content Block

Excel Vba Write to Access Database

Source: https://myengineeringworld.net/2020/06/add-records-access-excel-vba.html

Share this post