Solved

Importing from Excel to Access 2007

Posted on 2013-01-15
4
511 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 47

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 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