Solved

Importing from Excel to Access 2007

Posted on 2013-01-15
4
473 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
4 Comments
 
LVL 28

Accepted Solution

by:
omgang earned 300 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, it worked out well doing it the way you suggested.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

772 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

13 Experts available now in Live!

Get 1:1 Help Now