Access Database Design and Excel Import

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?
Who is Participating?
Rey Obrero (Capricorn1)Commented:
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

rdracer58Author Commented:
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?
Rey Obrero (Capricorn1)Commented:
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
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

rdracer58Author Commented:
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?
Rey Obrero (Capricorn1)Commented:
basically, the import will keep on adding records to the table.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.