Solved

Loading excel data to access

Posted on 2013-12-13
3
521 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

828 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