TransferText and add todays date

[Tanya Addison]

I have used the transfer text column to insert a table into my Db. Before this command runs it I have a line of code that deletes the exisitng table
Is there a way of of using the transfertext command and placing todays date next to each inserted record?

Many thanks in advanced
T

 

[bat17]

I dont think that you could do it using the TransferText but you should be able to create an update query that would do it.
When you say that you are deleteting the existing table are you actualy deleting the table or just clearing the data in it?

Peter

 

[Tanya Addison]

I am deleting the whole table. Could you explain how I would setup the update query? Then perhaps I can include that in the code after the import.

Thanks a lot!!!

 

[bat17]

Your best bet, providing that the table structure stays the same each time, is to delete the data not the table.
You can then add a column for the date. You may find that by setting its default value to Today it will automatically up date it when you add the data. You should get less database bloat that way as well.

If you post the code you have I will try to tweak it for you if you like.

What version of Access are you using?

Peter

 

[Tanya Addison]

Thanks your a star!

The code is just two DoCmd lines
I am using Access 2K
The message box is just because each month the period number on the file changes. The user knows which period they are in so they will just type a number between 1 to 13 (There are 13 periods). All i need know is a date field! Merci beaucoup!

Dim dbs As Database
Dim Tperiod2 As String
Dim Msg As String
'Set dbs = CurrentDb
Tperiod2 = InputBox("Enter the csv time period", "CSV 9221 File Import", vbOKOnly)
If Tperiod2 = "" Then
Msg = MsgBox("No csv file has been imported", vbOKCancel)
Exit Sub
Else
DoCmd.DeleteObject acTable, "Rep1_9221"
DoCmd.TransferText acImportDelim, "Csvimport", "Rep1_9221", "C:\Booker\rep1_9221_" & Tperiod2 & ".csv", True

End If

 

[bat17]

For the following code to work you will need to add a date field to your table called dDate ( unless there is a blank date field being imported!!) You will also probably need to set a reference to the "Microsoft DAO Libary". To do this open the module the select Tools>References... and scroll down untill you find it and select it.

Dim strSQL As String
Dim qdf As QueryDef
Dim strDate As String
Dim Tperiod2 As String
Dim Msg As String
Tperiod2 = InputBox("Enter the csv time period", "CSV 9221 File Import", vbOKOnly)
If Tperiod2 = "" Then
    Msg = MsgBox("No csv file has been imported", vbOKCancel)
    Exit Sub
Else
    strSQL = "DELETE Rep1_9221.* FROM Rep1_9221;"
    Set qdf = Application.CurrentDb.CreateQueryDef("", strSQL)
    qdf.Execute

    DoCmd.TransferText acImportDelim, "Csvimport", "Rep1_9221", "C:\Booker\rep1_9221_" & Tperiod2 & ".csv", True
    ' put the date in American format!!
    strDate = Format(Month(Date), "00/") & Format(Day(Date), "00/") & Year(Date)
    strSQL = "UPDATE Rep1_9221 SET Rep1_9221.dDate = #" & strDate & "#;"
    Set qdf = Application.CurrentDb.CreateQueryDef("", strSQL)
    qdf.Execute
End If

HTH

Peter

 

[Tanya Addison]

Dear Peter

I am off to try the new code out
A thousand thanks for your help!!!!
TTFN

Tanya