drtopserv
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?
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
LOLLLLLLLLLLLLLLLL!!
Perfecto:}
thnx peter57r :{ didn`t think about it!!
Perfecto:}
thnx peter57r :{ didn`t think about it!!
ASKER
thnx alot,works!
Copy your resulting table (only structure) to tblTmp
You can change your code to:
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 & "]"
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.
But there is next step: set default value for ImportDate in table tblname to =Date() and you will not need run update at all.
als315
I agree - that would be a better idea than using code to to do it.
Peter
I agree - that would be a better idea than using code to to do it.
Peter
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).