We help IT Professionals succeed at work.

update/add importdate to table

Medium Priority
344 Views
Last Modified: 2012-05-11
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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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).

Author

Commented:
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
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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

Author

Commented:
thnx alot,works!
CERTIFIED EXPERT

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

CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT

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

Peter
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.