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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

-->  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).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS 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 FyeCommented:
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.
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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 MVPDesigner and DeveloperCommented:
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!! = ))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.