Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

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

0
drtopserv
Asked:
drtopserv
  • 3
  • 3
  • 2
1 Solution
 
als315Commented:
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).
0
 
drtopservAuthor 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
0
 
peter57rCommented:
DO you mean...?

CurrentDb.Execute "UPDATE [" & strTable & "]  SET ImportDate = #" & Format(Date, "mm/dd/yy") & "# where Importdate is null", dbFailOnError
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
drtopservAuthor Commented:
LOLLLLLLLLLLLLLLLL!!
Perfecto:}
thnx peter57r :{ didn`t think about it!!
0
 
drtopservAuthor Commented:
thnx alot,works!
0
 
als315Commented:
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

0
 
als315Commented:
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.
0
 
peter57rCommented:
als315
I agree - that would be a better idea than using code to to do it.

Peter
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now