Import append vba MS-Access

Question

Need help with the Access VBA I am trying to import spreadsheets from excel into access. Once it is imported it will be appended into a different table and the import table will be deleted so that i can then import a new spreadsheet. However i have error when appending i need to append the entire table because i will not always know the header from the spreadsheet and how many their are. Please Help. Below is the code i have been work on.

Module-

Option Compare Database

Function selectFile()
Dim fd As FileDialog, fileName As String

On Error GoTo ErrorHandler

Set fd = Application.FileDialog(msoFileDialogFilePicker)

fd.AllowMultiSelect = False

If fd.Show = True Then
    If fd.SelectedItems(1) <> vbNullString Then
        fileName = fd.SelectedItems(1)
    End If
Else
    'Exit code if no file is selected
    End
End If

'Return Selected FileName
selectFile = fileName

Set fd = Nothing

Exit Function

ErrorHandler:
Set fd = Nothing
MsgBox "Error " & Err & ": " & Error(Err)

End Function

VBA -

Private Sub cmdImportNoDelete_Click()
'Unset warnings
DoCmd.SetWarnings False

'Import spreadsheet
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "Import", selectFile, True

'SQL append statement, Needs to append entire table (must be able to append multiply table with different headers
DoCmd.RunSQL "INSERT INTO Tbl_ImportDump * FROM Import"

'SQL delete Table
DoCmd.DeleteObject acTable, "Import"


DoCmd.SetWarnings True
End Sub

Is the fundamental issue you're facing that Tbl_ImportDump and Import have different sets of fields ("headers") ... one table includes fields not present in the other, or maybe both have fields for the same data but the field names and/or data types don't match exactly? If that is the problem, showing us the code you use to select an Excel file is not helpful. — HansUp
HansUp The sheets have for the most part similar field names but there are some that have new fields or different name fields. I want to dump all this into one table to then i can write some qry to identify fields and create a standard naming convention. — Leocodetwist

John Bingham John Bingham · Accepted Answer · 2016-05-18T03:55:08

Tbl_ImportDump must exist. If it doesn't already, create it:

DoCmd.RunSQL _
    "CREATE TABLE Tbl_ImportDump (Col1 Col1DataType, Col2 Col2DataType, etc)"

Then after importing your spreadsheet (as table Import, as you've already done), get the column names in the Import table, and put these into the insert statement:

dim ImportCols as String: ImportCols = ""
dim ii as long
dim rs as Recordset

rs.OpenRecordset "SELECT TOP 1 * FROM Import"
for ii = 0 to 2
    ImportCols = ImportCols & "," & rs.Fields(0).Name
next
importCols = mid(importCols, 2)

DoCmd.RunSQL _
    "INSERT INTO Tbl_ImportDump (Col1, Col2, Col3) " & _
    "SELECT " & importCols & " FROM Import"

Import append vba MS-Access

1 Answers