Append, Update or Delete?

[Deggsy]

I have a table of employees' details, and every Monday an Excel arrives with an updated list. There is an Append Query set up to import this each week into the database, but being append, it is adding records that aren't already there, but not deleting ones that should be deleted because they will have left the organization (I'll include the code below in case it's needed). The Excel sheets received each week do not list employees who have left.

1. I want to be able to remove the records in the table that don't correspond to what is in the Excel being imported. If possible, how could I do this?
2. Is there a way of updating a particular record if the new Excel details something different for the employee (such as a Last Name or Position Name change), or would it be simpler just to replace the record each week from the new Excel, even if there has been no change to the employee details?

The Employee Number acts as the primary key and doesn't change. Thanking you in advance! :-)

INSERT INTO Employees ( [Organization Name], [Subordinate Organization], [Subordinate Cost Centre], [Employee Number], [Person Name], [Email Address], [Position Name], [Job Name], [Grade Name], [Supervisor Number], [Supervisor Name], [Hire Date], [Employment Category], [Date Of Birth], [Age (at effective date)], [National Identifier], Gender, [Working Hours], [Collective Agreement Name], [All Inclusive Salary], [Payroll Name], [Pay Group], [User Assignment Status], Title, [First Name], [Last Name], [People Group] )
SELECT [New Starters].[Organization Name], [New Starters].[Subordinate Organization], [New Starters].[Subordinate Cost Centre], [New Starters].[Employee Number], [New Starters].[Person Name], [New Starters].[Email Address], [New Starters].[Position Name], [New Starters].[Job Name], [New Starters].[Grade Name], [New Starters].[Supervisor Number], [New Starters].[Supervisor Name], [New Starters].[Hire Date], [New Starters].[Employment Category], [New Starters].[Date Of Birth], [New Starters].[Age (at effective date)], [New Starters].[National Identifier], [New Starters].Gender, [New Starters].[Working Hours], [New Starters].[Collective Agreement Name], [New Starters].[All Inclusive Salary], [New Starters].[Payroll Name], [New Starters].[Pay Group], [New Starters].[User Assignment Status], [New Starters].Title, [New Starters].[First Name], [New Starters].[Last Name], [New Starters].[People Group]


FROM [New Starters] LEFT JOIN Employees ON [New Starters].[Employee Number] = Employees.[Employee Number]
WHERE ((([New Starters].[Employee Number]) Is Not Null) AND ((Employees.[Employee Number]) Is Null));

[ItsMe]

Unfortunately, there is not a simple and straightforward way to do this. Synchronizing data is not easy and requires the developer of the app to apply techniques to validate data and observe constraints in an effort to maintain referential integrity.

1. I want to be able to remove the records in the table that don't correspond to what is in the Excel being imported. If possible, how could I do this?

You could do some fancy footwork and do some programming to compare the excel sheet with the data in the DB to determine what needs to be deleted. You could also manually audit the data and delete the appropriate records. A search form could be created and a User could search for keywords to determine which records need to be deleted. Automating the process can be tricky because there can be errors in the excel sheet and or the DB when fields like First Name or Last Name have typos.

2. Is there a way of updating a particular record if the new Excel details something different for the employee (such as a Last Name or Position Name change), or would it be simpler just to replace the record each week from the new Excel, even if there has been no change to the employee details?

It would be ideal to determine the authoritative source for the data and ensure only the responsible personnel have access to edit the authoritative source. If you can have the responsible personnel use the Access database as the authoritative source and always perform edits in the DB, you will not need to sync an Excel spreadsheet. To answer your question, yes, you can do this via queries and forms. A form could have a single button that could automate the entire process. However, implementing checks and balances takes advanced programming skills and may not be successful if the data structure does not follow rules of normalization. Determining the authoritative source, limiting access to the authoritative data, and implementing rules of normalization is how you would implement automation and maintain referential integrity.

[Deggsy]

Unfortunately, there is not a simple and straightforward way to do this. Synchronizing data is not easy and requires the developer of the app to apply techniques to validate data and observe constraints in an effort to maintain referential integrity.

Yeah, based on my efforts so far it's beginning to look like it might be easier to delete all the records in the existing table, and then just import all the records from the Excel received.

[ItsMe]

Yeah, based on my efforts so far it's beginning to look like it might be easier to delete all the records in the existing table, and then just import all the records from the Excel received.

If the data structure is an exact match with the Excel sheet and your solution is to simply delete the existing data to replace it with the data in the Excel sheet, this process can be done via the wizard and a couple of basic lines of code. However, this is not a Best Practices approach. Doing it this way avoids data validation and will, very likely, introduce incorrect data into your data base. Having said that, operations that depend on Excel exclusively do not have these data constraints in place anyway.

[Micron]

I had to design for a similar situation where the data was no longer needed if the work order status became 'closed'. Before running the update process, code calls a batch file to back up the backend just in case the data rebuild fails after the delete, and it has been satisfactorily working this way for five years. Maybe you should consider some sort of backup before wiping tables.

[Deggsy]

I had to design for a similar situation where the data was no longer needed if the work order status became 'closed'. Before running the update process, code calls a batch file to back up the backend just in case the data rebuild fails after the delete, and it has been satisfactorily working this way for five years. Maybe you should consider some sort of backup before wiping tables.

That makes sense. Do you know how the query should look?

[Micron]

I can't tell you how your queries need to be written for your update process as I don't know what you want to delete. If you want to proceed with your idea of deleting all and rebuilding the list, here's how the backup was written.
shell function calls batch file. I use UNC path for this (\\ServerName\etc) because not everyone has the same drive letter associate with the network folder. Pause delays query execution - I find it helps. Pause function is in a standard module. You pass the number of seconds delay you want:

Public Function Pause(intSecs As Integer)
Dim Start As Variant
Start = Timer
Do While Timer < Start + intSecs
DoEvents
Loop
End Function

all needed code before backup here
Call Shell("\\ServerName\filePathhere\yourBe_backup.bat")
Pause (3)
rest of code, delete unwanted data from tables, etc.
Call Shell("\\ServerName\filePathhere\yourBe_backup.bat")
Pause (3)

What you have done is
- backed up the back end tables (your db is split, yes?) in case the next part fails
- deleted, updated or appended as required
- backed up the new data set
You will always have a backend copy that is only one day old, assuming you run your update process each work day.

batch file in same folder as database
**********************
rem to copy yourBE to backup database
rem must be run from this folder
cls
@echo off
rem this syntax works with any drive label assigned to the network folder (e.g. F, L, etc.)
set MyDir=%~dp0
rem now use that value by reference
cd %MyDir%
rem now copy YourBE as YourBE_back and overwrite existing
:CopyFiles
copy %MyDir%YourBE.mdb %MyDir%ExpediterBE_bak.mdb /y
:quit
****************************
rest of update process code
end sub, function, quit, whatever

Hope that helps.

[ItsMe]

You can use the FileSystem Library from within a form's module to copy files. This code snippet uses early binding so you would need to make a reference to the Microsoft Scripting Runtime

    Dim FSO As FileSystemObject
    Set FSO = New FileSystemObject
     FSO.CopyFile strOrigin, strDestination
     Set FSO = Nothing

[Deggsy]

Thanks everyone, your tips seem to have done the trick.
One slight snag: during the process of importing from an Excel sheet via a query, I keep getting the message "Database can't find the object 'DIS003 Employee Analysis Food S$'_ImportErrors' (see below image).
All of the spreadsheets we receive with the updated information are titled 'DIS003 Employee Analysis Food Stores 25.05.15' (the date at the end changing each week, of course).
I press OK and it proceeds and does what it's supposed to do (there are no importing errors popping up), but I'd like to get rid of the error message so as not to confuse those who'll be using the system. Any ideas?

[ItsMe]

The import error is because Access does not know how to handle the period you are using within the Worksheet name ... Food Stores 25.05.15

Perhaps you can use an import procedure that does not use the name of the worksheet or use another naming convention. Maybe the underscore will benefit you.

[Deggsy]

The import error is because Access does not know how to handle the period you are using within the Worksheet name ... Food Stores 25.05.15

Perhaps you can use an import procedure that does not use the name of the worksheet or use another naming convention. Maybe the underscore will benefit you.

I've changed the name of the spreadsheet several times, even kept it a simple "New", and it's still coming up with the same error message :-/

[ItsMe]

I would have to understand more about your import process. Is this a stored and saved import process? How are you calling the import process? Do you use the Buttons within the Ribbon and use the Wizard or do you use a macro/VBA?

If you have code, what does the code look like? If you use a Macro, what does the macro look like?

It is probably because your import procedure is looking for a table that does not exist. I suspect it is because of your naming convention in the worksheet. We will have to determine when Access decided to use this "Worksheet Name".