TransferText

[za20001]

I need to move comma-delimited text files into Access.



I want to loop through a directory using VBA and create new tables in Access for each text file.

Right now, my VBA shell looks like this, pulled form another online forum:

Sub importFiles()

Dim strPath As String
Dim strFileName As String
Dim strTableName As String
Dim dbf As Database

Set dbf = CurrentDb
' Set the path to the directory where the files are.
strPath = "c:\users\zabrahamson\downloads\AmericasFoundationPACFilings\"
strFileName = Dir(strPath) ' Retrieve the first entry.
Do While strFileName <> ""
    strTableName = Left(strFileName, InStr(strFileName, ".txt") - 1)
    DoCmd.TransferText acImportDelim, Import, strTableName, strPath & strFileName, False
    strFileName = Dir
Loop

End Sub

When I use Access's import wizard and step through (Delimited >> Delimiter = Comma, Qualifier = " >> Finish), my file imports without errors.

I save an import specification with those settings (Delimited, Delimiter = Comma, Qualifier = ") and re-import the file to verify the specification. Using the wizard, (Advanced, Specification = "Import" >> OK >> Finish). My file imports without errors.

But when I run the shell above, my file imports with 279 Type Conversion errors.

This occurs whether the syntax that follows TransferText is acImport or acImportDelim.

Any thoughts on why this might occur?

Thanks,

Zack

[Stingaway]

When you use the import wizard, do you change any field type specifications? (Number to text for example?)

You might consider setting up an import specification and calling it through the code - that way your conversion errors should be caught or explicitly handled by saying "i don't care if it looks numeric, make it text..." etc.

Also, you said the file imports - can you tell what kind of conversion error it is hitting? It may give you your next clue towards solving..