Regarding TimeStamp on Importing data

[MI man]

Hi,

I have a database into which I import data from Excel spreadsheet thorugh the form interface.

I need the timestamp of the import in the table for each item that I import.

The structure of the table is as follows:

ItemNo ItemName TimeStamp
1-------ABC -------(Time should get recorded here)
2-------DEF -------(Time should get recorded here)
3------ HIJ --------(Time should get recorded here)

I import the whole chunk at once, and timestamp corresponding to each item should fall in the column as above for every item.

I have a column for Timestamp in database which works when the data is manually entered into the form fields and clicked on UPDATE command button (that I have created).

rsttable.fields(fieldnumber).value=now()

But, when IMPORT command button is clicked, the timestamp doesn't appear. How to store the timestamp in the above-specified column. I hve tried giving the default value of the column as now() in the table itself but no luck. Any help...???

Note: I open up a recordset through coding

dim rsttable as new ADODB.recordset.
if rstable.state=adstateclosed then rsttable.open slqstatment, currentproject.connection, adopenkeyset, adlockoptimistic

Seeking urgent help..!!!

 

[DCrake]

If the source table is an Access table then the default value property should work. If not, and I don't know why, You could run an update query on the table to insert the tiimestamp on all records that have a null value.

Another tip you may consider is to have an additional field, if you don't already have it, to record a "Batch" number. Once you have run the import you then get the highest batch number in the table and add one to it. So when you do the timestamp update you also update the batch number field with the next sequential number.

This way if you need to refer to a particular import run you can do this by referencing the batch number. It is also good if you need to roll back an import. Simply delete all records for a certain batch number.

 

[MI man]

Thanks for the instant response DCrake...!!!

I would try the update query method that you prescribed.

Thanks a lot