Access VBA to import multiple csv files using import button

Question

I am trying to create a form with a dialogue box, such that it allows users to provide the location of the file and import it to the database. I have provided the import specification as well as the code will append and update the existing table. However, the code I am using works only for one file (WM_3M) in my case. I am looking for the code that will update the existing tables based on the CSV file the user uploads. For example, if a user uploads a file for WM_3M it should update the table associated with it, if WM_5M then the table associated with it, and so forth.

Code for the dialogue box:

Option Compare Database
Option Explicit
Public Sub ImportFile()
    Const FORM_NAME As String = "ImportFile"
    DoCmd.OpenForm FORM_NAME, , , , , acDialog
    If formIsOpen(FORM_NAME) Then
       ImportCSVFiles Forms(FORM_NAME).fileName
        DoCmd.Close acForm, FORM_NAME, acSaveNo
    MsgBox "Import Completed"
    End If
End Sub
Public Function formIsOpen(ByVal formName As String) As Boolean
    formIsOpen = SysCmd(acSysCmdGetObjectState, acForm, formName)
End Function

Public Sub RunImportProcedure(ByVal fileName As String)
    MsgBox " RunImportProcedure called for file" & fileName
End Sub

Code for Import:

Option Compare Database
Option Explicit
Public Sub ImportCSVFile(fileName As String)
    Const TARGET_TABLE As String = "WM_3M_Export_Imported"
    deleteTableIfExists TARGET_TABLE
    DoCmd.TransferText acImportDelim, "WM Import Specification", TARGET_TABLE, _
    fileName, True, , 1252
    
    CurrentDb.Execute "qryWM_3M_Update", dbFailOnError
    CurrentDb.Execute "qryWM_3M_Append", dbFailOnError
End Sub

Public Sub deleteTableIfExists(ByVal tableName As String)
    Dim db As DAO.Database
    Dim td As TableDef
    Set db = CurrentDb
    For Each td In db.TableDefs
        If td.Name = tableName Then
        db.TableDefs.Delete tableName
        Exit For
    End If

Codes for the form:

Option Compare Database
Option Explicit
Private Sub Cancel_Click()
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub
Private Sub ImportFile_Click()
 If Len(Me.txtFileName.Value) > 0 Then
        Me.Visible = False
    Else
        MsgBox " Please enter file name"
    End If
End Sub
Public Property Get fileName() As String
    fileName = Nz(Me.txtFileName.Value, "")
End Property


Private Sub Select_Click()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogOpen)
With fd
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Any file", "*.*", 1
    .Filters.Add "Comma seperated file", "*.csv;*.txt", 2
    .FilterIndex = 2
   
    If .Show Then
        Me.txtFileName.Value = .SelectedItems.Item(1)
    End If
   
End With


End Sub

Every table needs its own ImportSpec (stored in hidden systemtables MSysImexColumns' and 'MSysIMEXSpecs ) as fields differ. If not you can make TARGET_TABLE a variable (not a constant), but your database is not normalized then (not recommended, instead use one table and a a field for importname (WM_3M, ...) .to tell different imports apart). — ComputerVersteher

jbud jbud · Accepted Answer · 2020-09-11T17:23:14

You can pass the table name to the function and dynamically update the QueryDef of the qryWM_3M_Update and qryWM_3M_Append to the target table, before executing the query.

Public Sub ImportCSVFile(fileName As String, TARGET_TABLE as String)
    deleteTableIfExists TARGET_TABLE
    DoCmd.TransferText acImportDelim, "WM Import Specification", TARGET_TABLE, _
    fileName, True, , 1252
    
        Dim db As Database
        Set db = CurrentDb

        Dim qdf1 As QueryDef
        Set qdf1 = db.QueryDefs("qry_Update")
        qdf1.SQL = "UPDATE " &  TARGET_TABLE & " SET Field1 = ...."
        qdf1.Close
        Set qdf1 = Nothing
        
        Dim qdf2 As QueryDef
        Set qdf2 = db.QueryDefs("qry_Append")
        qdf2.SQL = "INSERT INTO " & TARGET_TABLE & " SELECT ...."
        qdf2.Close
        Set qdf2 = Nothing

        db.Execute "qry_Update", dbFailOnError
        db.Execute "qry_Append", dbFailOnError
    End Sub

Please complete the SQL definition by yourself based on your structure, but the idea is to build up the SQL via string concatenation. The destination table should exist for this to work.

You can build the TARGET_TABLE name like this Forms(FORM_NAME).fileName & "_Export_Imported" if you need the post-fix to the table name.

Access VBA to import multiple csv files using import button

1 Answers