Results 1 to 4 of 4
  1. #1
    flebber is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Location
    Australia
    Posts
    9

    Setup Update Query to Import Excel file Weekly


    Hi

    I am trying to understand how to setup an import for Access that will allow me to import an excel file into Access weekly and update new and existing records.
    Struggling to get off the ground, I created a test database as my real import will update many tables from one excel sheet.

    Whenever I complete an update query and press run I get '0 rows will be updated'. In SQL form this is what I have done.
    Code:
    UPDATE Groceries, Person, Sheet1 SET Sheet1.FirstName = [Person].[FirstName], Sheet1.LastName = [Person].[LastName], Sheet1.Perishables = [Groceries].[Persihables], Sheet1.Fruit = [Groceries].[Fruit], Sheet1.Vegetables = [Groceries].[Vegetables], Sheet1.Meat = [Groceries].[Meat], Sheet1.BreakFast = [Groceries].[Breakfast];
    So here I have created 2 tables Person and Groceries. I imported the excel file into sheet1, which contains one field for every field in the database. I ran update and still received a 0 to update error.

    What am I missing here?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am not sure what you are trying to do.

    An UPDATE query updates records already in a table.
    An APPEND query adds records to a table.


    Here is a site for Importing from EXCEL Workbook Files that has helped me tremendously:
    http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

    Try explaining like you were in line at Burger King and pretend we don't know anything about Access.

  3. #3
    flebber is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Location
    Australia
    Posts
    9
    Thanks for the link. Yeah found the update query was of no use to me.

    What I am wanting to do is import 1 sheet in 1 excel spreadsheet, this has 43 columns. The 43 columns need to be inserted across 9 tables. I want to repeat this process weekly updating existing records and adding in new records.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, wasn't sure, but I was hoping it would help.


    ....import an excel file into Access weekly and update new and existing records.......
    After re-reading your first post, I think this is going to be a VBA solution.
    You don't say what the relationship is between the 9 tables or how many records are in the import table. But if you have to determine if the record in the import table should be appended or update an existing record, I would use VBA and loop through the import table.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Excel File using a Macro
    By smakkiee in forum Access
    Replies: 6
    Last Post: 04-28-2014, 08:45 AM
  2. Import Excel file based on a date and time in file name
    By nhylan in forum Import/Export Data
    Replies: 4
    Last Post: 04-16-2013, 03:26 PM
  3. Import Data from Excel File
    By zwieback89 in forum Import/Export Data
    Replies: 1
    Last Post: 06-27-2012, 08:44 AM
  4. Help needed With Update Query Setup
    By NewInAccessBusiness in forum Access
    Replies: 18
    Last Post: 06-08-2011, 12:29 AM
  5. Excel Import/Append Data (weekly)
    By MartinL in forum Import/Export Data
    Replies: 1
    Last Post: 08-12-2010, 06:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums