Importing from text file, then create date field

  • Thread starter Thread starter Joey_83
  • Start date Start date
J

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
 
In
Joey_83 said:
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.
Click to expand...

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".
 
Back
Top