asked on

update/add importdate to table

Hi,
I need to import excel file into an existed table.
also i need to add importdate record into each row i import,
my code attached, have an issue, it`s update the importdate field for all new imported excel file and in all the table.
i need to edit it to be able to update only the new imported rows .
how too?

CurrentDb.Execute "UPDATE [" & strTable & "]  SET ImportDate = #" & Format(Date, "mm/dd/yy") & "#", dbFailOnError

Open in new window

als315

Link excel table (permanently, if excel file is always same or temporarily, if not). Create query, which will add excel data to your table, add column for this query with ImportDate and run it for import.
If you don't like linked tabled, use temp table, import data to this table and add data from it with query (with added ImportDate field).

drtopserv

ASKER

this is the code i use and works for me fine, but lacking the issue i mentioned (can`t i fix the code??)
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "path\"

'Name the table
strTable = "tblname"

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

strFile = Dir()
Loop
CurrentDb.Execute "UPDATE [" & strTable & "] SET ImportDate = #" & Format(Date, "mm/dd/yy") & "#", dbFailOnError

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

drtopserv

ASKER

LOLLLLLLLLLLLLLLLL!!
Perfecto:}
thnx peter57r :{ didn`t think about it!!

drtopserv

ASKER

thnx alot,works!

als315

Copy your resulting table (only structure) to tblTmp
You can change your code to:

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable, strTblImport As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True

strPath = "C:\TMP\2\"
strTable = "tblTmp"
CurrentDb.Execute "DELETE * FROM [" & strTable & "]"

'Name the table

strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

strFile = Dir()
Loop
CurrentDb.Execute "UPDATE [" & strTable & "]  SET ImportDate = #" & Format(Date, "mm/dd/yy") & "#", dbFailOnError

strTblImport = "tblname"
CurrentDb.Execute "INSERT INTO [" & strTblImport & "] SELECT [" & strTable & "].*, * FROM [" & strTable & "]"

Open in new window

als315

Peter, my mind was dusted.
But there is next step: set default value for ImportDate in table tblname to =Date() and you will not need run update at all.

peter57r

als315
I agree - that would be a better idea than using code to to do it.

Peter