?
Solved

MSaccess date problems when reading from text file.

Posted on 2006-05-24
5
Medium Priority
?
291 Views
Last Modified: 2006-11-18
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.



0
Comment
Question by:johnf1
  • 2
4 Comments
 
LVL 7

Expert Comment

by:ExpertAdmin
ID: 16764780
Just to clarify...the target field is text or date?

M@
0
 

Author Comment

by:johnf1
ID: 16765544
M@
the target field is text as is the source field.

johnf1
0
 

Author Comment

by:johnf1
ID: 16776548
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


0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 17174954
PAQed with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question