johnf1
asked on
MSaccess date problems when reading from text file.
I have an MSAccess 2003 application and have to import a file with the HTM extension. Everything good except in 1 of the fields the data is 12-12-12 and it is a text field. This is exactly what I want. When I read the data into another text field in the database, the program changes this field to 12/12/2012 as if it was a date.
How do I stop it from automatically doing this?
Snipets of the code are as follows:
strInputFileName = GetHTMLFileToOpen(STR_FILE _DIR, "Select HTML File to Process")
If Len(strInputFileName) = 0 Then
MsgBox "No Input File was Specified! Process aborted. " & strInputFileName
Exit Function
Else
If MsgBox("Process HTML File: " & strInputFileName, vbYesNo) = vbNo Then
Exit Function '************************* ********** ********** ********** ****
End If
End If
strErrLoc = "RX (CLEAN UP EMAIL RXED TABLE)"
strSQL = "DELETE [EMAIL_DATA].* FROM [EMAIL_DATA];"
DoCmd.RunSQL strSQL
strErrLoc = "RX (IMPORT EMAIL HTML FILE TO EMAIL_DATA)"
DoCmd.TransferText acImportHTML, , "EMAIL_DATA", strInputFileName, True
It is in the DoCmd.TransferText that the change takes place. The field in the HTM file is 12-12-12 and when I look in the table EMAIL_DATA the field is now 12/12/2012
Is there a setting to stop this from happening. The field format in the table is set to Text and the field size set to 255.
Any ideas.
How do I stop it from automatically doing this?
Snipets of the code are as follows:
strInputFileName = GetHTMLFileToOpen(STR_FILE
If Len(strInputFileName) = 0 Then
MsgBox "No Input File was Specified! Process aborted. " & strInputFileName
Exit Function
Else
If MsgBox("Process HTML File: " & strInputFileName, vbYesNo) = vbNo Then
Exit Function '*************************
End If
End If
strErrLoc = "RX (CLEAN UP EMAIL RXED TABLE)"
strSQL = "DELETE [EMAIL_DATA].* FROM [EMAIL_DATA];"
DoCmd.RunSQL strSQL
strErrLoc = "RX (IMPORT EMAIL HTML FILE TO EMAIL_DATA)"
DoCmd.TransferText acImportHTML, , "EMAIL_DATA", strInputFileName, True
It is in the DoCmd.TransferText that the change takes place. The field in the HTM file is 12-12-12 and when I look in the table EMAIL_DATA the field is now 12/12/2012
Is there a setting to stop this from happening. The field format in the table is set to Text and the field size set to 255.
Any ideas.
ASKER
M@
the target field is text as is the source field.
johnf1
the target field is text as is the source field.
johnf1
ASKER
I found a work around that involves creating an import filter through the import/export wizard , then saving this filter, and in the line of code
DoCmd.TransferText acImportHTML, , "EMAIL_DATA", strInputFileName, True
add the saved filter name as such
DoCmd.TransferText acImportHTML, "Email_Import_Filter", "EMAIL_DATA", strInputFileName, True
When creating the filter, the field data types were shown as changed and had to be changed back to the desired type.
This was quite cumbersome and is only usable for "set" tables and import files. It is not feasible for a dynamic type of import where the field names might change.
Does no one out there know of a way around this "trying to be too smart" way of working from Microsoft.
help
DoCmd.TransferText acImportHTML, , "EMAIL_DATA", strInputFileName, True
add the saved filter name as such
DoCmd.TransferText acImportHTML, "Email_Import_Filter", "EMAIL_DATA", strInputFileName, True
When creating the filter, the field data types were shown as changed and had to be changed back to the desired type.
This was quite cumbersome and is only usable for "set" tables and import files. It is not feasible for a dynamic type of import where the field names might change.
Does no one out there know of a way around this "trying to be too smart" way of working from Microsoft.
help
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
M@