+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    05-22-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

    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

  2. #2
    Forum Contributor
    Join Date
    05-05-2009
    Location
    Wisconsin
    MS-Off Ver
    Excel 2010 and 365
    Posts
    113

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

    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.

  3. #3
    Registered User
    Join Date
    05-22-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    2

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

    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"

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,218

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

    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 [UpdateON 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]))); 

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1