the best way I've found is to create a data import spec in the advanced section of the import table wizard. i then did a function to import as I have up to 50 files, the entire code is below. Please post back if you need any further help,
Function succImportData(strTlName As String) As Boolean
On Error GoTo Oops
If Dir(strTlName) <> "" Then
DoCmd.TransferText acImportFixed, "DataImportSpec", "Data", strTlName, 0
' this is the bit you need to automate the import
Let succImportData = True
Kill strTlName
Else
Let succImportData = False
End If
Exit Function
Oops:
Dim t As Variant
Let t = StdError(Err.Number, Err.Description)
Let succImportData = False
End Function
Sub mDataLoader()
Dim rst As Recordset
Dim strSQL As String
Dim dbs As Database
Dim MyProb As Boolean
Dim strTlName As String
Dim strNope As String ' to track the missing people
Dim y As Variant ' pointless acsysycmd counter
On Error GoTo Oops
Dim h As Boolean
Dim sDBPath$
Let sDBPath = "T:\Whatever etc\Where\"
If Dir$(sDBPath & "oat", vbDirectory) = "" Then
MsgBox ("Unable to locate data folder, please advise your admin")
'docmd.quit
End If
Set dbs = CurrentDb
Let strSQL = "SELECT DISTINCT [Team Leader] FROM [Employee List] WHERE [Job Role]=" & Chr(34) & "CSA " & Chr(34)
Set rst = dbs.OpenRecordset(strSQL, 4)
Let y = SysCmd(acSysCmdInitMeter, "Importing", 100)
Do Until rst.EOF
' check path we're on and then add tlname to get the data, if it doesn't exist append to a
' str to display at the end
Let strTlName = rst.Fields(0).Value
Let MyProb = succImportData(sDBPath & "oat\" & strTlName & ".txt")
If MyProb = False Then
Let strNope = strNope & strTlName & Chr(13)
End If
Let y = SysCmd(acSysCmdUpdateMeter, rst.PercentPosition)
rst.MoveNext
Loop
' get the final Unknown people
Let MyProb = succImportData(sDBPath & "oat\Unknown.oat")
Let y = SysCmd(acSysCmdClearStatus)
MsgBox ("No Import for :" & Chr(13) & strNope)
Exit Sub
Oops:
Dim t As Variant
Let t = StdError(Err.Number, Err.Description)
End Sub
