Access Database Design and Excel Import

Posted on 2011-04-21
Medium Priority
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
  • 3
  • 2
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 1500 total points
ID: 35442998
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

ID: 35443025
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1500 total points
ID: 35443072
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 35443091
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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1500 total points
ID: 35443106
basically, the import will keep on adding records to the table.
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 500 total points
ID: 35443108
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 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