I am having difficulty importing a .csv file to Access 2007. The file will import using the transfertext method but one field which is a text type keeps being imported as a numeric field which results in errors in the table created.
The procedure which will be automated will import the file on a regular basis and once the data is imported then the code deletes the source file.
The datafile Data08142010.csv
has the current date in the name to ensure that the correct data is imported. Field16 or column "P" in the Excel file is the field causing the problem. Below is the code.
Private Sub TableData_Import()
Dim SourceFile As String
Dim ThsDay As String
On Error GoTo error_handler:
ThsDay = Date$
ThsDay = Right([ThsDay], 4) & Left([ThsDay], 2) & Mid([ThsDay], 4, 2)
SourceFile = "L:\VBA Tool Project\Data\Data" & ThsDay & ".csv"
DoCmd.TransferText acImportDelim, , "MainData1", SourceFile
On Error Resume Next
I then created an import specification to use with the DoCmd.Transfertext command in order to make sure that the correct field types were used but VBA would not recognize the import specification file. Here is the error message.
Run-time error '3625':
The text file specification 'Import-MainDataDtl_Data' does not exist.
You cannot import, export, or link using the specification.
From what I understand this is a problem with Access 2007. Apparently, Access 2007 VBA does not have the capability to recognize file specifications - at lest for comma delimited files.
Does anyone know of a work-around to deal with this problem? I guess that I will probably have to use another method but I am not sure which one to use.
Thanks in a advance for your help.