how to dbFailOnError for DoCmd.TransferSpreadsheet

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
transferspreadsheet
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!! = ))

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!! = ))

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.

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.

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

Select allOpen 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!! = ))