+ Reply to Thread
Results 1 to 3 of 3

Find records in an Access table that no longer exists in an Excel table

  1. #1
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Find records in an Access table that no longer exists in an Excel table

    Hi,

    Have really been struggling with this and have spent many, many hours (and much coffee) trying to find a solution on the internet. I have an Access database with a table that stores budget data. I use Excel to visualize the budget data and for entering budget items. I have about 25 users. I have code that works great for updating and adding new records to the Access table from Excel. My issue is deleting records from the Access table if a user deletes a budget item in the Excel table. Both the Excel table and the Access table have a unique field called "Item ID". Both the Excel and Access tables have a field called "Project Code". There are 42 different project codes representing 42 different programs.

    Goal - In the Access table, I need to find the Item IDs for a project code that no longer exist is the Excel budget table for the program. Once found, I need to delete the Item ID row from the Access table.

    I had to attach the code for updating and adding records in the Access table because I was blocked from putting it in this post.

    Greatly appreciate assistance with figuring this out.

    Karen

  2. #2
    Valued Forum Contributor
    Join Date
    05-15-2017
    Location
    US
    MS-Off Ver
    365
    Posts
    896

    Re: Find records in an Access table that no longer exists in an Excel table

    The routine you have in the file is just the update logic, where is the delete logic?

    Example would be something like this:

    Please Login or Register  to view this content.
    If you find the suggestion or solution helpful, please consider adding reputation to the post.

  3. #3
    Registered User
    Join Date
    07-31-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    30

    Re: Find records in an Access table that no longer exists in an Excel table

    Correct, I haven't written the delete query because I was stuck on how to find the records in the Access table. Basically when a user opens the Excel file, the Excel budget table is cleared and the records from the Access table which includes the Access table Item ID field are imported into the Excel budget table based on the user's project code. The user can then edit this data and add new rows of data as well as delete rows of data in the Excel budget table. When the user saves or closes the Excel file the code runs updating the Access table. The code I provided takes care of updating and adding new rows to the Access table. I am stuck on how to identify Item IDs present in the Access table but are now lacking in the Excel table because the user deleted the row in Excel.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 02-09-2017, 07:55 AM
  2. Access VBA or SQL to find and delete duplicate records in access table
    By ewong in forum Access Programming / VBA / Macros
    Replies: 10
    Last Post: 09-14-2012, 06:55 PM
  3. Access VBA or SQL to add all records from a table to another table (Append)
    By ewong in forum Access Programming / VBA / Macros
    Replies: 3
    Last Post: 09-14-2012, 06:49 PM
  4. Delete empty (black) records in an access table after I import them from Excel
    By ewong in forum Access Programming / VBA / Macros
    Replies: 0
    Last Post: 09-12-2012, 01:26 PM
  5. Updating multiple records in Access table using excel VBA
    By anthony1312002 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-28-2012, 04:19 PM
  6. Excel Table to Access Records
    By GBR2L in forum Excel General
    Replies: 3
    Last Post: 12-10-2010, 04:59 PM
  7. Deleting all records in a access database table from Excel
    By Dave31 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2008, 02:18 PM

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