how to dbFailOnError for DoCmd.TransferSpreadsheet

hey guys,

sometimes when i do a DoCmd.TransferSpreadsheet a few records fail to import due to conversion errors and there is a table created showing which records were not imported.

how do i cancel the entire import if there is a single error at all?

i need an all or nothing at all process. i don't see a dbFailOnError for transferspreadsheet. how do i raise an error so i can put this in a transaction?

thanks guys!!
Who is Participating?
mbizupConnect With a Mentor Commented:
-->  i don't see a dbFailOnError for transferspreadsheet.

There is none...

--> i cancel the entire import if there is a single error at all?

And you really can't.

<< table created showing which records were not imported>>

However, you can possibly check for the existence of this table and delete any imported data if any errors are reported.  The idea is to make it appear as though the import didn't happen... by deleting records and/or tables and also deleting the error log table (you can do this either before or after the import).
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
how do i cancel the entire import if there is a single error at all?
...just to be clear, you would need a message box to alert of the failure, ...else, how would you know if all the records were processed...?

<put this in a transaction>
...Not sure what this means...?

You can use the Data Validation feature in Excel... This insures that invalid data cannot be entered.
You can do this for entire columns too.
Thus minimizing the chance of this happening...

Dale FyeConnect With a Mentor Commented:
You can also use a staging table, so that you are not importing data from Excel directly into your production table.  That way, you don't need to figure out which records that were appended to the production table came from the incomplete Excel import.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

developingprogrammerAuthor Commented:
thanks mbizup! getting your confirmation is very reassuring!! = ))

yup i think checking for the tables is the best way of getting notified if an error occurred. i must take note of the error table name now haha = )

hi Jeff! thanks for helping me out!! = ))

yup i agree with you that having a message box to alert of the failure is critical in informing the user. perhaps i can join what mbizup taught me about the import error table and your error message - the import error table triggers a message so the user will know.

hrmm i want to kinda like put the transferspreadsheet method IN a transaction.

so something like
transaction being
transaction commit

on error transaction roll back

don't know if that's possible. don't think it is cause there's no automatic raising of error when an import fails.

yup i think your point on data validation is very good, but the thing is that i'm getting spreadsheets from other team which i don't have control over at the moment (and maybe for posterity too ha) so i'm trying to see what's the best way of doing things to "harden" the whole import process.

currently what i've done is inserted a first row sample data with the appropriate excel formatting and relevant values so that when Access imports it in it picks up the right data type and all's ok. and i would use Text even for a field like CaseNumber cause sometimes CaseNumber could have an odd alpha (can't control it when users are manually keying stuff into the spreadsheet ha)

thanks so so much guys for your time and help again!! = ))
developingprogrammerAuthor Commented:
thanks fyed! i just saw your comment! = )

ha you'll be surprised but... that's what i'm already doing!! = ))

for me i call it a roll forward table cause everything goes there and then rolls forward. i design the mechanism as a roll back at first but i thought roll forward is more fail safe and won't corrupt the data = )

yup so combine the staging table / roll forward table together with mbizup's error import table trigger and Jeff's error message to the user. i've got the whole suite now!! haha thanks so much guys!! = ))
Dale FyeCommented:
The other advantage of using a "staging table" is that you can actually run some error checks against various fields to make sure they have valid entries and flag those records when have invalid entries.

You can provide a mechanism that allows the users to fix the invalid entries or allow them to import only those records which pass the validity checks.  Takes a little more work, but if you have users who are using Excel spreadsheets that don't have validity checks, it is easy to enter a text value in an otherwise numeric field, which will cause some of those import problems.
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
I always use a "staging table".  I avoid ever directly importing to production/live tables. I want to avoid and data corruptions. As fyed pointed out, using a "staging table" gives you the chance to be sure the data is correct before importing.

 When data entry is done on a form you can/should use validation code to insure the data goes in clean. When importing, you will need to use a "staging table" that can/should be validated before importing.
developingprogrammerAuthor Commented:
hi fyed and TheHighTechCoach!!

essentially this is my import workflow

    Call ImportFactorySetup(intAutomationID)
    Call rawSource_To_rawClean
    Call rawClean_To_dbUnfiltered
    Call dbUnfiltered_To_dbFiltered
    Call dbFiltered_To_dbRollForward
    Call dbRollForward_To_dbDestination
    Call MsgBox("The import process is successfully complete!", vbInformation, "Import Success")

Open in new window

i'm so glad to hear yall are of the same opinion! essentially i have TWO staging tables haha = P one for the raw import, the other one for the rollforward - and if the user finds the data ok, then the rollforward will go into the production / live tables. why use 2 staging tables?

well the rollforward table is supposed to have every thing AS PER how it will be appended to the live tables already. all the calculated fields and any manipulations will be done and inserted into the second staging table and then the second staging table will be presented to the user (they can't edit it of course haha) and when the user okays it it will simple just be inserted into the production table.

hrmm i did think of doing away with the second staging table but then i remembered that there are some things which are hard to do with one query, and stacked queries could have complications in terms of maintainability for my business logic, so the 2nd staging table was the way i chose in the end though it did entail more work - but i've set the process down already and all the reuseable code so it's relatively easy and seamless to add new imports for this = ) REUSEABLE CODE WOO HOO!! haha = ))

thanks so much for your advice again fyed and TheHighTechCoach!! = ))
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.

All Courses

From novice to tech pro — start learning today.