I just had this problem too yesterday!! This is how I solved it and it should help you get on your way!
My design tracks bank account activity but the import process is the same for any project.
Ask if you have more questions on how to get going to the finish line. As of now this only works with .csv but you can change that to .txt files or other ones too. Cheers
SOLUTION:
Option Compare Database
Option Explicit
Public Function IMPORT_FILE()
Dim f As Object
Dim i As Integer
Dim sFile As String
'-------------------
On Error GoTo getFP_Err
Set f = Application.FileDialog(3)
With f
.AllowMultiSelect = True
'.AllowMultiSelect = False
.Title = "Please Select the .CSV For Import"
.Filters.Clear
.Filters.Add "Text Files", "*.csv"
'.Filters.Add "All Files", "*.*"
If .Show Then
For i = 1 To .SelectedItems.Count
sFile = .SelectedItems(i)
If Right(sFile, 3) = "CSV" Then
Dim sTable As String
sTable = Mid(sFile, 1 + InStrRev(sFile, "\", , _
vbTextCompare), _
Len(sFile) - 4 - InStrRev(sFile, "\", , vbTextCompare))
sTable = "timp_" & sTable
If IsTableQuery("", sTable) Then
Call DeleteAll(sFile, sTable)
End If
Call GetTextFileData(sFile, sTable)
MsgBox "Import Complete"
End If
Next
End If
End With
getFP_Exit:
Exit Function
getFP_Err:
MsgBox Error$
Resume getFP_Exit
End Function
Function DeleteAll(sFile As String, sTable As String)
Dim sSQL As String
sSQL = "Delete * from " & sTable & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
End Function
Function GetTextFileData(sFile As String, sTable As String)
Dim blnHasFieldNames As Boolean
Dim strTable As String
Dim strPathfile As String
' Change this next line to True if the first row in CSV worksheet
' has field names
If InStr(sTable, "AMEX") > 0 Or InStr(sTable, "WELLS") > 0 Then
blnHasFieldNames = False
Else
blnHasFieldNames = True
End If
DoCmd.TransferText acImportDelim, , sTable, sFile, blnHasFieldNames
End Function
'********************************************************
' FUNCTION: IsTableQuery()
' SOURCE:
https://support.microsoft.com/en-us/kb/113549
' PURPOSE: Determine if a table or query exists.
'
' ARGUMENTS:
' DbName: The name of the database. If the database name
' is "" the current database is used.
' TName: The name of a table or query.
'
' RETURNS: True (it exists) or False (it does not exist).
'
'********************************************************
Public Function IsTableQuery(DbName As String, TName As String) As Integer
Dim Db As Database, Found As Integer, Test As String
Const NAME_NOT_IN_COLLECTION = 3265
' Assume the table or query does not exist.
Found = False
' Trap for any errors.
On Error Resume Next
' If the database name is empty...
If Trim$(DbName) = "" Then
' ...then set Db to the current Db.
Set Db = CurrentDb()
Else
' Otherwise, set Db to the specified open database.
Set Db = DBEngine.Workspaces(0).OpenDatabase(DbName)
' See if an error occurred.
If Err Then
MsgBox "Could not find database to open: " & DbName
IsTableQuery = False
Exit Function
End If
End If
' See if the name is in the Tables collection.
Test = Db.TableDefs(TName).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True
' Reset the error variable.
Err = 0
' See if the name is in the Queries collection.
Test = Db.QueryDefs(TName$).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True
Db.Close
IsTableQuery = Found
End Function
