Solved

Import Excel Spreadsheet into Access via VBA without Error table

Posted on 2010-08-23
3
528 Views
Last Modified: 2013-11-27
I wrote code in VBA to import an excel spreadsheet into Access. Now because it's formatted in a weird way in excel, i'll have to clean the imported table. I get a table with the errors of the imported data. Is there a way to eliminate the error table from being created? I have no use for it

' IMPORT UNUM EXCEL SPREADSHEET AND NAME IT tblUNUMIntermittentLeaveSummaryReportTemp
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblUNUMIntermittentLeaveSummaryReportTemp", "C:\Documents and Settings\T94HGEY\Desktop\eLeave\UNUM\Intermittent_Leave_Summary_Report(1).xls"
0
Comment
Question by:flyguy80
[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
3 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33505241
flyguy80,To avoid the errors, you should load first into a "staging" table in which all of the columns use the text data type.  You can then use queries and/or code to perform your data clean up and move the validated data to your permanent table(s).Patrick
0
 
LVL 14

Accepted Solution

by:
ldunscombe earned 500 total points
ID: 33506928
What I have found is that using the transfer spreadsheet method to import excel data into a new access table will cause the new table to be created with the field types matching the first row of data that in in the spreadsheet.
So say for arguments sake cell A1 in your spreadsheet is a date, then the first field in the new table will be set as a date field.
Then If Cell A2 in your spreadsheet is text, a data type conversion error will occur because access is trying to insert text into a field it has determined should be a date based on the first row of data and an import errors table will be created.
To avoid this you can
A: Create a blank table in Access with the appropriate data types for the data that is to be imported and import to that table
Or B: Ensure that the first row of data in your spreadsheet is of a type that can accept all the data in that column.
For example
If Cell B1 contains 1234 then that will be determined to be numeric and any text in column B below row 1 will generate an error
However if Cell B1 contains ABC then the corresponding field in access will be set as Text Data type into which 1234 can also exist without error.
Hope this helps
Leigh
 
0
 
LVL 34

Expert Comment

by:Norie
ID: 33512055
Wouldn't/couldn't the error table actually help you?

Either with cleaning things up or identifying if there is a problem with the import.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

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…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

628 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