Access Database Design and Excel Import

Posted on 2011-04-21
Last Modified: 2012-06-27
I am setting up an Access database to track information originally tracked/stored in Excel spreadsheets - primarily client information and their status on a project (e.g. initial, in progress, verified, completed, etc.).

In some instances, it makes sense to have multiple tables - for example, there may be multiple contacts associated with a single client, in which case it is logical to have a client table and a "Contacts" table.

In our spreadsheet, we currently have one row per client - when I go to import it into my Access database, how do I specify what information is stored in what tables?
Question by:rdracer58
    LVL 119

    Accepted Solution

    this command imports the excel file

    docmd.transferspreadsheet acimport,9, "nameOftable","c:\folder\myexcel.xls", true,

    will import the columns A thru F

    so if your client info is occupying columns A to F use the format above for the Client table, and use a different columns to import for the Contacts table


    Author Comment

    Will that keep all of the information properly related? In other words, if I run the command once for client info and again for contacts, would all of Client A contacts be connected/related to the Client A record and all Client B contacts be connected/related to the Client B record?
    LVL 119

    Assisted Solution

    by:Rey Obrero
    the Answer is No.
    you have to provide a means to create a relationship between the two tables.
    i.e., in the excel file, copy the column that identify the client to a NEW column that you will include when you import the columns for the Contacts table

    Author Comment

    Ok. So when I design the database, can I have my tables already related? Or do I have to import the data into the two separate tables, including a column along the lines of "client id" from each excel import, then relate the tables? What if we want to import data, then update it again at a later time from a newer Excel spreadsheet?
    LVL 119

    Assisted Solution

    by:Rey Obrero
    basically, the import will keep on adding records to the table.
    LVL 21

    Assisted Solution

    by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
    I prefer to import the Excel files into a temp front end database with linked table to the real back end.  I use append queries and/or VBA code to move the data from the temp table; with the excel data to the correct tables.

    TIP: I usually add additional; field(s) to the Excel data to hold the primary key from the master/parent table. This will allow the related data to be appended with the foreign key.

    Splitting your Access database into application and data


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now