Importing CSV file into MS Access 2010 Tables

Question

I'm new into VBA coding, I am looking into creating an Import UI in ms access for user to import CSV files and insert into a new temp table, from the temp table there will be SQL query to split data into different tables and lastly drop the temp table.

As per the codes, I have created the import button and coded the file picker. However I am lost on how to code it to import into a new temp table and follow by the SQL queries.

Appreciate all helps

Sub ImportButton_Click()

Dim strfilename As String

With Application.FileDialog(msoFileDialogFilePicker)

    .Title = "Select the CSV file to import"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "CSV Files", "*.csv", 1
    .Filters.Add "All Files", "*.*", 2
    If .Show = -1 Then
        strfilename = .SelectedItems(1)
        DoCmd.TransferText TransferType:=acImportDelim, _
            TableName:="Import_Table", FileName:=strfilename
        Else
        Exit Sub
    End If
End With

End Sub

Answer 1

Have a look at this article Working with external text files in MS Access, it should help you.

Basically you can create some schema for the text file and then query it using SQL, it as if it was a table in a database.

From there you can easily go through the data using recordsets in VBA or INSERT queries to create or fill your other tables.

Answer 2

Try

docmd.RunSQL("SELECT * INTO myTable FROM Import_Table WHERE [conditions]")

Replacing the [conditions] with your criteria and myTable with the name of the table you want to create.

You would likely want to set

DoCmd.SetWarnings False

first, to avoid users seeing record commit prompts. But it should be set back to True afterwards.

Alternatively, you could just create some Access "Make Table" queries (change query type on the Design tab of the ribbon) if you'd rather use the query designer.