Import and update rows in Access database based on date changes from Excel

Hi

I would like to know how to import an excel worksheet into Access base on data changes. I am exporting data from global ticketing solution to excel on a weekly basis based on Region and modified date.
I want to then merge changes to a local database.
If a ticket (row) has been modified replace the old entire row in the local database, if its new just add the entire row to the data base.

I have a primary key for each row which never changes. This is the ticket number.
Is it possible to compare the two values in Excel and the Access Database and if match then compare the date field in that row.
If date is newer overwrite the entire row in the database. (Not sure if the date format is going to be an issue?)
Below is the sample fields, Incident ID+ is the key unique field and the last modified date is the comparison field.

Incident ID*+	Priority*	Incident Type*	Status*	Resolution	Last Modified Date
INC000001247345	Medium	User Service Request	Assigned	Token synchronization	5/22/2013 3:01:28 PM

Lastly if a new Incident ID is found it just adds it regardless of the date.

Hope this is clear.
I am using Excel and Access 2010

Interesting issue. You would think it would be easy but I've had trouble getting something simple to work. I am very new to Access so hopefully there is someone out there who can help.


In theory you should be able to do this using an update query and an append query. The append query works good but you need to make sure you only append new records. Not too hard to do. You can use the query wizard to create a query that finds records that don't exist in one table then convert it to an append query.

It's not too difficult to open access, run the query and close access all from within Excel.

Updating records from an external table seems to be more difficult. The issue that I ran into is that, from what I can tell, Access won't run an update query unless there is a primary key IN BOTH TABLES. The problem is with the external table not having a primary key.


As a temporary fix what I've resorted to is deleting all records in the access table then running the append query. Hopefully there is an easier way.

Sorry for the delay, I had to go away and teach myself the append query.
But I am stuck.
The good news is I have a primary key matching in both and all the fields are exactly the same name and type.
the question is how do I do the criteria.

So I have two tables. The table of new or modified values called "update" and the table I want to update called "incidents"
I create the query as this;
Criteria=
[update].[Incident ID] = [Incidnet].[Incident ID] and [update].[Last Modified Date] > [Incident].[Last Modified Date]
update to=
[update].[Last Modified Date]

Maybe I am miss understand this technique. I assumed it would compare incident ID number if they match then update the corresponding Last modified date?

Here is the SQL view
UPDATE Incidents INNER JOIN [Update] ON Incidents.[Incident ID*+] = Update.[Incident ID*+] SET [Update].[Last Modified Date] = [Update].[Last Modified Date]
WHERE (((Incidents.[Incident ID*+])=[Update].[Incident ID*+]) AND (([Update].[Last Modified Date])>([Incidents].[Last Modified Date])));

It runs and confirms that two records are to be updated but does not update that record.? If I change the formula to "=" then it says Zero records to update which is correct?
I confirm the Format of the fields match "Date/Time"

Access syntax is a bit icky but your SET is just setting the update.LastModifiedDate to itself, try the below:

PHP Code: 

UPDATE Incidents INNER JOIN [Update] ON Incidents.[Incident ID*+] = Update.[Incident ID*+] SET [incidents].[Last Modified Date] = [Update].[Last Modified Date]
WHERE (((Incidents.[Incident ID*+])=[Update].[Incident ID*+]) AND (([Update].[Last Modified Date])>([Incidents].[Last Modified Date])));