Solved: Comma delimited to access 2000 table

[calenger]

Hi and thanks for reading.

I am attempting to create a routine that will import a comma delimted text file into an exsisting access 2000 table. I have tried the transfertext method but running into a problem with data type mismatch because some fields are number and some are date/time. I understand that there is a way to call an existing import format. but not sure if that is the answer?

what i have so far.
[VBA]
Dim objAccess
Dim strPathToMDB
Dim sTable
Dim sFilePath
Const acImportDelim = 0
strPathToMDB = "C:\Jay_1\Cdscdrtest.mdb"
sFilePath = "C:\Jay_1\200507201632.txt"
sTable = "x_Acct_op"
Set objAccess = CreateObject("Access.Application.9")
objAccess.OpenCurrentDatabase(strPathToMDB)
objAccess.DoCmd.TransferText, acImportDelim,,sTable,sFilePath,True
Set objAccess = Nothing
[/VBA]

[OBP]

You can create a "Specification" for importing the data where you specify what date fields look like. This creates the table for the data to be "Imported/Exported" from/to. Click on the new table you want the data to go in to and then file>exportText Files>Save this starts the export wizard. DO NOT USE THE WIZARD. Click the Advanced button instead, this brings up the specification dialogue box.
You use the "Specification name" that you created after "TransferType in the docmd statement.

[calenger]

Thanks, I did create the spec import type but did not know how to use it. thanks makes perfect sense. Won't be able to test till tomorrow but has to be the answer.

[calenger]

Hi again

I have added the spec in the DoCmd.transfertext but am still getting an type mismatch error. I have included the error message and below is the code.

[VBA]
Dim objAccess
Dim strPathToMDB
Dim sTable
Dim sFilePath
Const acImportDelim = 0
strPathToMDB = "C:\Jay_1\Cdscdrtest.mdb"
sFilePath = "C:\Jay_1\200507201632.txt"
sTable = "x_Acct_op"
Set objAccess = CreateObject("Access.Application.9")
objAccess.OpenCurrentDatabase(strPathToMDB)
objAccess.DoCmd.TransferText, acImportDelim, "Account", sTable, sFilePath, True
Set objAccess = Nothing
[/VBA]

The first line of the text file is the colum names. I have tried it with and without to try to elimante that as the issue.

[OBP]

Have you positively identified what it is in the text file that causes the error message?
You could try deleting certain parts of the data to try and establish what it is.
Have you imported the data manually using the "Import Wizard" to see if your table accepts the data?