Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2015
    Thanked 0 Times in 0 Posts

    Access & Excel Data Updatable

    Hi Group: First timer here.

    I have an Associate Member database in Access 2010. I update this manually with information I get from an Excel spreadsheet. I want to know if it is possible to somehow compare the information in the Excel spreadsheet to the info in the Access database without having to manually go thru each member file. I know there is a way to compare information in 2 different spreadsheets. I haven't mastered that yet, but can I do that with Excel & Access?


  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Evergreen, CO, USA
    Thanked 58 Times in 58 Posts
    Welcome to the Windows Secrets Lounge and the Databases forum.

    There are at least a couple of ways of doing what you describe using Access and Excel, but both require some knowledge regarding the design of queries and how Excel and Access interact. One approach would be to link to the Excel workbook (or you could import the Excel workbook into a temporary Access table) and then creating a set of queries that determine what records if any are different, and then what columns within the rows are different. That assumes that you have some unique cell in each Excel row that can be used to match a unique value in your Access table. One of the issues with Excel is that the order of the rows can be changed by a sort, which means you cannot use the row number as an identity value. (There is also the possibility that an Excel workbook can be scrambles by someone sorting the rows without specifying all of the columns, but there's not much you can do about that.)

    Another possibility would be to link or import the Excel workbook, and use VBA and either DAO or ADO to actually compare each record column by column and actually automate the updating process, but if you are a relative novice with Access, that would be quite a leap. If you want more details about either approach, please post back and forum members will try to give you a hand.

Posting Permissions

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