+ Reply to Thread
Results 1 to 4 of 4

Appending to Access from Excel

  1. #1
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Appending to Access from Excel

    The attached application appends data from a MS-Excel Table to an MS-Access Database. The Control Panel sheet is used to configure the download. On the Control Panel sheet, you tell the program the directory in which the database is located, the database name, the Excel Table name and the Access Table name. You then provide of list of Excel Table header names and corresponding Access Table field names.

    Clicking on the Make XML button on the spreadsheet does a validation check to make sure that the database, tables and fields exist and creates an XML instruction file in the same directory with the application. Creation of the XML file is a one-time exercise repeated only if table names, field names, database names or locations are changed.

    The attached file contains instructions and a sample database.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Appending to Access from Excel

    I did some experimentation with my coworkers who have different versions of Excel. It turns out that this application will not work if you do not have MS-Access also installed BUT it does not have to be a fully-paid license. You can download the run-time only (free) license from Microsoft and it will work.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Appending to Access from Excel

    Here is version 1.1. This version does a data check against the Excel table to assure that the data types are correct prior to import. Still working on the next version: detect potential duplicate values. Ideally I would like this to happen before import but at least I would like to trap the error, warn the user that the data was not imported and exit in a non-crashed state.
    Attached Files Attached Files

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Appending to Access from Excel

    Here is version 1.2. I added an extra level of Kevlar. In addition to pre-screening data, I also monitor for errors during import.

    My primary concern was with detecting duplicate record errors. Initially, I wanted to trap this error and keep on processing. Instead I decided to trap ANY error and stop processing immediately. It is an easier thing to do, it limits the damage and just seems like a smarter thing to do.
    Attached Files Attached Files

+ 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: 12-05-2012, 03:56 AM
  2. Appending Data in Linked Tables in Access
    By nickelcell1 in forum Access Tables & Databases
    Replies: 1
    Last Post: 04-03-2011, 08:19 PM
  3. Appending data into an Access db from Excel
    By W300 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2008, 04:18 PM
  4. Appending to an access table
    By ceemo in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-25-2006, 03:57 PM
  5. [SOLVED] Appending to Access table
    By Pete in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-02-2005, 06:20 PM
  6. [SOLVED] Appending to Access table
    By Gary L Brown in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-02-2005, 06:20 PM
  7. [SOLVED] Appending to Access table
    By Pete in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-02-2005, 06:17 PM
  8. Appending excel worksheets
    By Rbuzard in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 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