Appending Data from Excel to Access ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

Last week we tried out an interesting method of writing a range of excel data range directly into a Microsoft Access Table. Each row of cells is transferred into the table as a single record. If you have not come across that article then you may find it here.

This is an equally interesting method. The selected Worksheet contents are appended to the Access Table with its specified columns in the SQL.

A sample image of the Worksheet data is given below:

The VBA Code

Private Sub CommandButton1_Click()
On Error GoTo CommandButton1_Click_Error
'Create Database connection Object
Set cn = CreateObject("ADODB.Connection")
'Access Database must be in the same location of the Worksheet 
dbpath = Application.ActiveWorkbook.Path & "\Database4XL.accdb"
'Get Workbook Full Pathname
dbWb = Application.ActiveWorkbook.FullName
'Get Active worksheet name
dbWs = Application.ActiveSheet.Name
'Create Data Target Connection string to open a session for data transfer
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath
'Datasheet name to add at the end of the Workbook Name, to complete the
'FROM clause of the SQL String.
dsh = "[" & dbWs & "$]"
'Open session
cn.Open scn

'Append Query SQL String
ssql = "INSERT INTO Table1 ([Desc], [Qrtr1], [Qrtr2], [Qrtr3], [Qrtr4]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh

'Run SQL
cn.Execute ssql

MsgBox "Data Added to " & dbpath & " Successfully."

CommandButton1_Click_Exit:
Exit Sub

CommandButton1_Click_Error:
MsgBox Err & " : " & Err.Description, , "CommandButton1_Click()"
Resume CommandButton1_Click_Exit

End Sub

Courtesy:

The above VBA code was taken from a Forum Post on www.mrexcel.com/Forum and modified to run on the same sample data presented in the earlier article published last week.

The Code on the Worksheet VBA Module is run from the Command Button1 Click Event Procedure.