PDA

View Full Version : Solved: Comma delimited to access 2000 table



calenger
07-20-2005, 02:14 PM
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.

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

OBP
07-20-2005, 03:31 PM
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
07-20-2005, 04:56 PM
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
07-21-2005, 06:17 AM
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.


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


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
07-21-2005, 10:15 AM
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?