Importing from text file, then create date field

[Joey_83]

Hi Everyone,
I'm trying to import a text file (which is fine I can do that) but
currently this text file doesn't have dates.
Now, I can't change the format of the text file, so I'm wondering if
after I've imported the text file can I write some code to create the
extra field in MS ACCESS.
Ideally I want the date to be the current system date.

Any help would be greatly appeciated!!!

Thanks,
Joe

 

[Dirk Goldgar]

In

Hi Everyone,
I'm trying to import a text file (which is fine I can do that) but
currently this text file doesn't have dates.
Now, I can't change the format of the text file, so I'm wondering if
after I've imported the text file can I write some code to create the
extra field in MS ACCESS.
Ideally I want the date to be the current system date.

There are a couple of ways you could go about this. You could just
import the text file using DoCmd.TransferText, then execute an ALTER
TABLE statement and an UPDATE statement on the imported table, using
code similar to this:

With CurrentDb

.Execute _
"ALTER TABLE MyTable ADD COLUMN LastUpdated DATETIME", _
dbFailOnError

.Execute _
"UPDATE MyTable SET LastUpdated = Date()", _
dbFailOnError

End With

Or, alternatively, you could link to the text file instead of importing
it directly, and then use a make-table query to copy the data from the
linked text file to a new table, also creating the date field as part of
the process:

CurrentDb.Execute _
"SELECT *, Date() As LastUpdated INTO MyTable " & _
"FROM LinkedTextTable", _
dbFailOnError

And after you've done that and completed the export, you'd use
DoCmd.DeleteObject to delete "LinkedTextTable", leaving just "MyTable".

 

[Joey_83]

both options work a treat!!!
Thanks Dirk!