This is the situation. I open an Excel spread sheet .csv, that is emailed to me on a daily basis. I save this as an .xls file in a folder on my C drive. I then pull this data into an apend query that populates a temp table then appends the data to my permanent table or production table if you will. I then run a delete query that deletes the data from the temp table. The main table contains a running day to day build of all data sent to me.

This data is sent to me every morning and is from the day before, so the data should not be redundant. Because this append query can either run when the main form is open or with the click of a button, there is a chance of redundant data. What I am trying to figure out is how I can stop the process if the download contains duplicate data from a past append.

Any suggestions to help solve this would be appreciated.

Chris

Just some thoughts..

#1
Why can't you just make the Append Query run after one of the Events (On_Click or Form_Open) instead of both?

#2
Can you just call the Delete Query immediately after the Append Query in each event? That way there is no data to be duplicated.?

I do accomplish both append and delete using the same code in a On_Open event. I can even use the On_Click event if I desire and both append and delete happen using the same function. However, this is not the issue.

The issue is, if I were to chose either method, how can I stop the process if the data has been appended previously? Im thinking there may be a way of looking at the newly incoming apended data and the current table to see if the data is already there. Because there are no unique keys fields I would think this may be difficult because the data comes in from an excel spreadsheet and has no unique identifiers.

Hope this is clear?

Thanks,

Chris

So the problem seems to be then that the Data is still available in the Excel file to be imported into the TempTable, right?

I mean if you had a way of renaming those files, or moving them then when you ran either Event, the Excel file would not be there to import data From.

I have a similar situation where data is entered into an Accounting database and then Exported to a Pipe delimited .txt file. That Txt file is then imported into an Engineering database whenever a button is clicked. We get around redundant data by Exporting the file to one certain location, and after the data is imported the file is renamed with the date after the .txt extension (ie. TextFile.txt022607), and then moved to a Jobs Archive folder.

I apologize if I still don't see the problem. It just seems a lot like a situation I have to deal with.

What constitutes duplicate data in the production table? If it is just one field, you can index it with no duplicates and an attempt to add duplicate data will fail. If it is a combination of fields, you can make a composite primary key out of it so that duplicates can not be entered into the table.

When I look at each of the fields involved, they all have the possiblity to carry redundated data and this is acceptable except when there is one field "Customer Question" that should be different from the others unless the date is different. An example would be a question a customer asked today could in effect be the same question posted a month ago. However what if the questions downloaded and appended, for lets say 02/20/07 are accidently downloaded again and now I have two sets of the same data in my table.

I guess I could rename the excel spread sheet and move it to another folder after the append is finished. Seems like a lot of work versus automating the process.

You can still automate. I assume you are opening the file manually and copying data into the Temp table?
Then when you open the Form or click a button the Append Query is Run? I assume the Delete Query is called in the same event after the Append Query?
Assuming I am correct in my assumptions:
Open the File manually
Copy data to Temp table
Close File.
In Form_Open event add the following code AFTER the Append Query call and BEFORE the Delete Query call.


'Get the list of files to be processed.
sCurrentPath = "C:\FolderName"
sNewPath = "D:\New Folder\"

sFileName = Dir(sCurrentPath & "\*.csv ", vbNormal)

'Rename the file

FileCopy sCurrentPath & "\" & sFilename, sNewPath & Left(sFileName, Len(sFileName) - 4) & ".bak"
Kill sFileName

I think that should work. Test it out and post back if you have questions/concerns/problems.
Make sure you back up your files or use OLD data before running this...

I will look at this further and write it in and let you know.

Thanks,

I have attempted to use your code with mine. However, there are some problems.
I have dimmed your variables as strings, I believe this to be correct?

What happens now is this.
The import into my temp table works and the append to my production table works. However, my delete query no longer clears the temp table and the code doesnt create the folder I want and nothing happens to the xls.

Private Sub cmdImportProcess_Click()
Dim sCurrentPath As String
Dim sNewPath As String
Dim sFileName As String

' add error handler if excel file cannot be found
On Error GoTo ErrorHandler
'import spreadsheet
DoCmd.TransferSpreadsheet acImport, , "TblDealerRewardsDataTemp", "C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerConnectData.xls", True
'append data to real table
DoCmd.OpenQuery "QryAppendDealerRewardsDataTemp"

'Get the list of files to be processed.
sCurrentPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerConnectData.xls"
sNewPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect\Dealer Rewards Archives\"
sFileName = Dir(sCurrentPath & "\*.xls ", vbNormal)

'Rename the file
FileCopy sCurrentPath & "\" & sFileName, sNewPath & Left(sFileName, Len(sFileName) - 4) & ".bak"
Kill sFileName

'delete data from temp table
DoCmd.OpenQuery "QryDeleteDealerRewardsDataTemp"
'assign error message for excel file not found
ErrorHandler:
If Err.Number = "3011" Then
MsgBox "Access cannot find your file, check to see if the file is where it should be", 64, "Access Helper"
Exit Sub
End If
End Sub

You will need to create the "Dealer Rewards Archives" folder. The code will not create it, it just references the location.
Also...
The sCurrentPath variable should read...
sCurrentPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect"

The filename.xls portion is added with the sFileName string concatenation.

hmmm, I must be missing something because I still cant get this to work.
I like the idea and will continue to play with it but will revert back to what works for now.

Thanks

Put a LineBreak at the beginning of your code and use F8 to step through line by line, so you can see
where the code is erroring out. Also check to see what each Variable is being set to. This should give us an idea of
where the code is messing up and why.

Can you post your code again (with the updates in it)?

I did as you asked and stepped through the code and did not find any errors. the section of code you gave me doesnt seem to work and Im sure it is something Im doing wrong P

Private Sub cmdImportProcess_Click()
Dim sCurrentPath As String
Dim sNewPath As String
Dim sFileName As String

' add error handler if excel file cannot be found
On Error GoTo ErrorHandler
'import spreadsheet
DoCmd.TransferSpreadsheet acImport, , "TblDealerRewardsDataTemp", "C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerConnectData.xls", True
'append data to real table
DoCmd.OpenQuery "QryAppendDealerRewardsDataTemp"
'delete data from temp table
DoCmd.OpenQuery "QryDeleteDealerRewardsDataTemp"
'assign error message for excel file not found

'Get the list of files to be processed.
sCurrentPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerConnectData.xls"
sNewPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerRewardsArchives"
sFileName = Dir(sCurrentPath & "\*.xls ", vbNormal)

'Rename the file
FileCopy sCurrentPath & "\" & sFileName, sNewPath & Left(sFileName, Len(sFileName) - 4) & ".bak"
Kill sFileName

ErrorHandler:
If Err.Number = "3011" Then
MsgBox "Access cannot find your file, check to see if the file is where it should be", 64, "Access Helper"
Exit Sub
End If
End Sub

Copy and Paste the all the code below over your current code.....

Private Sub cmdImportProcess_Click()
Dim sCurrentPath As String
Dim sNewPath As String
Dim sFileName As String

' add error handler if excel file cannot be found
On Error GoTo ErrorHandler
'import spreadsheet
DoCmd.TransferSpreadsheet acImport, , "TblDealerRewardsDataTemp", "C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerConnectData.xls", True
'append data to real table
DoCmd.OpenQuery "QryAppendDealerRewardsDataTemp"
'delete data from temp table
DoCmd.OpenQuery "QryDeleteDealerRewardsDataTemp"
'assign error message for excel file not found

'Get the list of files to be processed.
sCurrentPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect"
sNewPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerRewardsArchives\"
sFileName = Dir(sCurrentPath & "\*.xls ", vbNormal)

'Rename the file
FileCopy sCurrentPath & "\" & sFileName, sNewPath & Left(sFileName, Len(sFileName) - 4) & ".bak"
Kill sFileName

ErrorHandler:
If Err.Number = "3011" Then
MsgBox "Access cannot find your file, check to see if the file is where it should be", 64, "Access Helper"
Exit Sub
End If
End Sub

Excellent it created a copy of the excel spreadsheet in the designated folder and gave it the .bak extension. However, the original file remains and I would like this to go away as I would have thought the kill method would do?

Do you have multiple .xls files in this directory that could cause confusion between the file you are importing and any others?

The Dir function returns the first filename of the type you specify (*.xls) it finds, in no apparent order.
If the file names will always be
"C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerConnectData.xls"
then change this line of your code:
sFileName = Dir(sCurrentPath & "\*.xls ", vbNormal)
with this one:
sFileName = Dir(sCurrentPath & "\DealerConnectData.xls ", vbNormal)

Only do this if the names are always the same. (and in same location - if not we'll need to add a few things)

Also, if there can be several *.xls files to Import, Append, and Copy/Delete, then we'll need to structure a
loop in the code to iterate through each file.

Nope only one xls to copy and paste to the new archive directory.

Ive changed the code to reflect the permenant file name and ran it and it still doesnt delete the file after it has been copied to the new directory.
This is very puzzling for me, everything seems to work except the fact I cant delete the file from the folder.

I hate to look a gift horse in the mouth but is there a way to append the copied file with the date because I would like to archieve each days files in the same folder.
This is a daily occurance and I dont want to copy the same file overtop the same one.

Thanks

Let me put something together and get back to you on that....
Until I post back (or someone else) just rename the Archived file to *.bak022707 (or whatever the date is)
I assume you only deal with one per day?

It puzzles me as well concerning the Kill method. ....
Try this..
Replace:
Kill sFilename
With
Kill CurrentPath & "\" & sFileName

Ok here is what I found out.

The file name was set to read only and this is why I couldnt delete it.

I also created a date variable and added some formatting.
Here is the code that works.

Thanks for your help

Private Sub cmdImportProcess_Click()
Dim sCurrentPath As String
Dim sNewPath As String
Dim sFileName As String
Dim MyDateStr As String
Dim MyDate As Date


MyDateStr = Format(MyDate, "Long Date")

' add error handler if excel file cannot be found
On Error GoTo ErrorHandler
'import spreadsheet
DoCmd.TransferSpreadsheet acImport, , "TblDealerRewardsDataTemp", "C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerConnectData.xls", True
'append data to real table
DoCmd.OpenQuery "QryAppendDealerRewardsDataTemp"
'delete data from temp table
DoCmd.OpenQuery "QryDeleteDealerRewardsDataTemp"
'assign error message for excel file not found


'Get the list of files to be processed.
sCurrentPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect"
sNewPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect\Dealer Rewards Archives\"
sFileName = Dir(sCurrentPath & "\DealerConnectData.xls", vbNormal)

'Rename the file
FileCopy sCurrentPath & "\" & sFileName, sNewPath & Left(sFileName, Len(sFileName) - 4) & ".bak" & "-" & MyDateStr
'Kill sFileName
Kill sCurrentPath & "\" & sFileName

ErrorHandler:
If Err.Number = "3011" Then
MsgBox "Access cannot find your file, check to see if the file is where it should be", 64, "Access Helper"
Exit Sub
End If
End Sub

OOoops it seems my date variable is returning 1899 versus 2007 P

You don't need to make a Variable for Date, it is already a constant. Just use Date, like so
Dim strDate as string

strDate = Format(Date, ...)

Paste this new code over your old code, and let me know how it works out...

Private Sub cmdImportProcess_Click()
Dim sCurrentPath As String
Dim sNewPath As String
Dim sFileName As String
Dim MyDateStr As String


MyDateStr = Format(Date, "mmddyyyy") ' this makes file ext. *.bak02272007

' add error handler if excel file cannot be found
On Error GoTo ErrorHandler
'import spreadsheet
DoCmd.TransferSpreadsheet acImport, , "TblDealerRewardsDataTemp", "C:\Documents and Settings\T1456LC\RNT\dealerconnect\DealerConnectData.xls", True
'append data to real table
DoCmd.OpenQuery "QryAppendDealerRewardsDataTemp"
'delete data from temp table
DoCmd.OpenQuery "QryDeleteDealerRewardsDataTemp"
'assign error message for excel file not found


'Get the list of files to be processed.
sCurrentPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect"
sNewPath = "C:\Documents and Settings\T1456LC\RNT\dealerconnect\Dealer Rewards Archives\"
sFileName = Dir(sCurrentPath & "\DealerConnectData.xls", vbNormal)

'Rename the file
FileCopy sCurrentPath & "\" & sFileName, sNewPath & Left(sFileName, Len(sFileName) - 4) & ".bak" & "-" & MyDateStr
Kill sCurrentPath & "\" & sFileName

ErrorHandler:
If Err.Number = "3011" Then
MsgBox "Access cannot find your file, check to see if the file is where it should be", 64, "Access Helper"
Exit Sub
End If
End Sub

Excellent!
You have been a great help.
Thank you for your time.

Chris

Glad I could help.