DoCmd.TransferText Import file Structure

[trihanhcie]

Hello,

I have a imported file from another application where one field is a
date.

Using DoCmd.TransferText, the structure and the data of a temptable is
created. The temptable has to be delete (data + structure) for each
import because its structure can changed following the import options.

Here is the problem :
A field date is created but if I use a english version of Windows,
then the date is not correct anymore (i m using a french version of
windows)

I would like to change the format of the date in the temptable one
it's created. How can I do it using SQL or VBA?

Thanks

 

[Guest]

trihanhcie,

I think the way to resolve your date problem is to go back a step and fix
your
DoCMD.Transfer Text to use a Import Specification (the Import Specification
should be setup by you to change the DATE format to what you want it to be
(MMDDYY DDMMYY YYYYMMDD etc.). First try using the manual method of importing
the text file through the menu File, Get External Data - Import, (Import Text
Wizard should come up) click on the Advanced button, Import Specification
window should come up, here you create the specification and save it for
future use (manually or using the DoCMD). Create a Import specification and
test it manually importing some of your data to a test table. It usually
takes me several tries (I am still learning). Once you have the format
working and saving the dates to the table the way you want them, then go and
switch your DoCMD to use that Import Specification (the name you saved it as)
to process your import data the way you like. Do searches on this
question/anwers system and you should find others that have asked the same
type of question as you. It is the way I am trying to learn the same thing,
by reading the anwers of questions like yours and putting the pieces
together. I found one question/answer that also showed a way to ask the user
the name of the file they wanted to import (I guess the name was changing all
the time, a problem I have. Plus the source directory for my file changes
everyday too.)
I am sorry if I don't have all of the code to show you, because I only know
the part you don't know, and you know what I don't know. I bet you will get
the full thing working before I do, Good Luck.
Thanks for your Help and Patience.