Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

Loading excel data to access

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
Seamus2626
Asked:
Seamus2626
  • 2
1 Solution
 
mbizupCommented:
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
 
Seamus2626Author Commented:
Yep, they are some good options, ive just went with linked tables now!
0
 
mbizupCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now