Hi,
I am building this database, importing several excel files from different customers. The excel files are standardized. Every month or so they give updates. Then it is up to the database to update the old tables with standard info coming from these excel files.
Now I am looking for a way to automatically delete old customer information and replace it with the new, if the customer already exists.
This is the code I use for import:
This way I append all my records to the database.
But I want to delete the old records first.
So, I came up with this basic code:
That should do it for new dealers only.
But how to detect and delete existing ones?
Thanks for your help,
Timo
I am building this database, importing several excel files from different customers. The excel files are standardized. Every month or so they give updates. Then it is up to the database to update the old tables with standard info coming from these excel files.
Now I am looking for a way to automatically delete old customer information and replace it with the new, if the customer already exists.
This is the code I use for import:
Code:
'cycle through the list of files & import to Access
'appending to tables called DealerLists and DealerContacts
For intFile = 1 To UBound(strFileList)
'Get the counter value
Set objApp = CreateObject("Excel.Application")
objApp.Visible = False
Set wb = objApp.Workbooks.Open(strPath & strFileList(intFile), True, False)
counter = wb.Sheets("counters").Cells(2, "A").Value
dealer = wb.Sheets("counters").Cells(2, "B").Value
wb.Close
Set objApp = Nothing
'Import the parts list
DoCmd.TransferSpreadsheet acImport, , _
"DealerLists", strPath & strFileList(intFile), True, "parts!A1:E" & counter
[I] 'Import or update the dealer file
DoCmd.TransferSpreadsheet acImport, , _
"DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
[/I]
Next
MsgBox UBound(strFileList) & " Files were Imported"
But I want to delete the old records first.
So, I came up with this basic code:
Code:
Dim dbTraderJoe As DAO.Database
Dim tblContacts As DAO.Recordset
Set dbTraderJoe = CurrentDb
Set rstContacts = dbTraderJoe.OpenRecordset("DealerContacts")
If rstContacts!DealerNumber <> dealer Then
'Import or update the dealer file
DoCmd.TransferSpreadsheet acImport, , _
"DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
End If
rstContacts.Close
dbTraderJoe.Close
That should do it for new dealers only.
But how to detect and delete existing ones?
Thanks for your help,
Timo