Solved

Importing from Excel to Access 2007

Posted on 2013-01-15
4
522 Views
Last Modified: 2013-01-15
I have a Access 2007 DB that I am having an issue importing an Excel sheet.

The main issue is that the Excel sheets top header row doesnt the Access table field names.

If I manualy select all rows and columns from the Excel sheet and paste them into the Access table it works fine but I have a macro button that I want to use to import. I get errors when I try to use my macro because the field names dont match. when I change the Excel field names to match the Access table field names the macro works fine.

So, I get this excel import once a week. Is there a way to fix the macro so that I can import the excel data without having to always change the field names?
0
Comment
Question by:CMILLER
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 300 total points
ID: 38778558
You can have your macro import the Excel data into a new/blank table then run an insert/append query to insert all the records from the new/blank table into the target table in Access.  To create the query, first you must import the Excel data into a new table.  Then create an insert query and match up the Excel table fields with the Access table fields.  Once you have this insert/append query working you simply modify your macro to run the import first and then the insert/append query second.

OM Gang
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 100 total points
ID: 38778564
An approach I generally take when importing data is to import the Excel file into a temporary "staging table"  and then run Update or Insert queries and or code to get the imported data into the required format in my actual Access tables.
0
 
LVL 48

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 38778591
I agree with mbizup, and always use a staging table.

The problem with importing Excel data is that Excel generally allows users to put any value they want in a cell, so in a column that should all contain numbers, the user can inadvertently insert an string value, which will screw up your import.

I generally link the Excel table to the Access FE, then import the data from Excel into my Access staging table.  Generally, every column in my staging table will be of a Text or Memo datatype.  This will ensure that any value entered in Excel will import into the staging table.  I then run through a series of data validity checks and tag rows in the staging table that contain invalid data.

I then present the list of invalid rows to the user to fix, if they choose not to fix the row, it remains in the staging table.

When they choose to import the valid rows, I run a query that explicitly types the values in each column to the correct datatype for the field in the destination table.
0
 

Author Comment

by:CMILLER
ID: 38779290
Thanks, it worked out well doing it the way you suggested.
0

Featured Post

Technology Partners: 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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

689 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