TransferText and add todays date

Tanya Addison

New Member
Joined
Jul 10, 2003
Messages
45
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

bat17

Well-known Member
Joined
Aug 15, 2003
Messages
1,470
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

New Member
Joined
Jul 10, 2003
Messages
45
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

Well-known Member
Joined
Aug 15, 2003
Messages
1,470
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

New Member
Joined
Jul 10, 2003
Messages
45

ADVERTISEMENT

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

Well-known Member
Joined
Aug 15, 2003
Messages
1,470
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,052
Members
414,357
Latest member
Gemma_R

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top