Results 1 to 4 of 4
  1. #1
    adi2011 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    19

    Creating "append to table" type of query in access 2013 web app ?

    Hi,

    I have excel file called "Products" which looks something like this


    -----------------------------------
    ItemNo. Description Price
    -----------------------------------
    1 Tennis racket 100$
    2 Tennis ball 10$

    In my access 2013 web app I have table "Products" which contains same data as Excel file mentione above.

    My GOAL is next: if someone adds new row (no 3) in Excel file "Products", then Data-macro is triggered so at the end,
    table "Products" FROM 2013 Access Web app is updated with new row, like shown bellow.

    -----------------------------------
    ItemNo. Description Price
    -----------------------------------
    1 Tennis racket 100$
    2 Tennis ball 10$
    3 Tennis shirt 110$

    My QUESTION is next: is it perhaps possible, when Excel file is updated with new row, that table named "Products" is automatically updated
    with that new row using data-macro or similar?

    Thank in advance for prompt reply and best regards.
    Adi

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Yes, I have used a similar process to handle MLS data imports to access. It involved using a Dictionary Object and comparing the fields in the excel file against the fields returned for the table. if the dictionary found a new field, then it would fire off an alter table statement. The issue you may run into is where fields are dropped and then added multiple times. You can only add soo many fields afaik.

    Surely there are better ways to do it now, but should still be possible.

  3. #3
    adi2011 is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    19
    Quote Originally Posted by Perceptus View Post
    Yes, I have used a similar process to handle MLS data imports to access. It involved using a Dictionary Object and comparing the fields in the excel file against the fields returned for the table. if the dictionary found a new field, then it would fire off an alter table statement. The issue you may run into is where fields are dropped and then added multiple times. You can only add soo many fields afaik.

    Surely there are better ways to do it now, but should still be possible.
    Hi Perceptus,

    thank you for prompt reply!

    I understand the basics of concept you have explained but is there any other (simpler) way to achieve this? Is it possible to simply from Access 2013 web app to link excel table or am I only restricted to import?

    Thanks.
    Adi

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Yes. That could work. You may need to refresh the linked table if you add columns.

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

Similar Threads

  1. Replies: 3
    Last Post: 01-06-2016, 01:18 PM
  2. Replies: 6
    Last Post: 12-22-2015, 09:49 PM
  3. Replies: 14
    Last Post: 12-18-2015, 02:04 PM
  4. Replies: 4
    Last Post: 12-18-2015, 11:43 AM
  5. Replies: 3
    Last Post: 12-06-2014, 03:59 AM

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