Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Importing from Excel to Access 2007

Posted on 2013-01-15
4
Medium Priority
?
544 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 1200 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 400 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
Dale Fye earned 400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

636 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