?
Solved

Loading excel data to access

Posted on 2013-12-13
3
Medium Priority
?
552 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
[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
  • 2
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

752 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