Solved

Loading excel data to access

Posted on 2013-12-13
3
535 Views
Last Modified: 2013-12-13
Hi,

Im loading excel data which has come from various data sources into an access database. The data types are not consistent, which means i keep getting paste errors on trying to paste into one master access table.

Is there any way i can avoid this error or am i doing something wrong?

Many thanks
0
Comment
Question by:Seamus2626
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39716367
A very common approach is to import your raw data into a 'staging' table first, without attempting to format the initial import in any way.

You can use the TransferSpreadsheet command for this in VBA.

Once you do that, you can use VBA to read the initial import from the staging table and cleanup/format the data as needed to fit it's destination table, using INSERT and/or UPDATE queries or recordset code to add it to the needed tables.
0
 

Author Closing Comment

by:Seamus2626
ID: 39716370
Yep, they are some good options, ive just went with linked tables now!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39716395
Funny - I was just about to type up that you can also link the spreadsheet and use it in the same way as a staging table.

FWIW, I prefer the import option -- largely personal preference, but I find that to be a more flexible approach.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

733 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