Appending Data from Excel to Access
Last week we have tried out an interesting method of writing a range of excel data range directly into a Microsoft Access Table. Each row of cells are 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 in it’s specified columns in the SQL.
Sample image of the Worksheet data is given below:
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 of 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.