transferspreadsheet fails within transaction

Are you saying that you are recieving that error with just those three lines of code?

If so, then 'something else' is tying up your table.

These three lines work without issue for me:

DBEngine.Workspaces(0).BeginTrans
Call DoCmd.TransferSpreadsheet(acImport, , "tblTest", "C:\Users\myname\Desktop\testfile.xls", True)
DBEngine.Workspaces(0).CommitTrans

Select allOpen in new window

hi mbizup!

thanks for that! i didn't think other things were affecting the code but as per your suggestion i retried it and i realised that the cause of the problem is this specifically

    DBEngine.Workspaces(0).BeginTrans
            Call CurrentDb.Execute("DELETE * FROM LOCALImport_Activity_tbl;", dbFailOnError)
            
            Call DoCmd.TransferSpreadsheet(acImport, acSpreadsheetTypeExcel9, "LOCALImport_Activity_tbl", strActivitiesImportFile, True)
    DBEngine.Workspaces(0).CommitTrans

Select allOpen in new window

hrmm... i'm just trying to think why would it cause a problem? i really can't understand it. works perfectly if i don't put transactions

whao mbizup and Pat, your explanation is really great and i really, really enjoy learning from you all!! it's so enlightening = )

i think what Pat says and what you're saying is quite similar with some slight differences (kindly feel free to correct me if i'm wrong)

Pat is saying the problem is a transaction in a transaction.
mbizup is saying that there is a modification to a transaction-locked table

hrmm i think mbizup to prove that your postulate is correct, i will have to try and run an action query against the table to see if it fails or not. at the moment i'm kinda time strapped and i guess understanding the effect for this case doesn't really change the outcome that much however i'm definitely bearing this in mind - the locking of tables for transactions.

Pat i think what you're saying makes sense and the point about the GUI i think is a really really good one. i didn't think of interpreting the GUI's "undo" as putting the action query in a transcation - but now i do = )

ok guys, your 2 postulates are definitely enlightening enough for me as a business application programmer - if i were in academia / research then i'll go deeper - but i'm learning to embrace my constraints and focus on getting the app up and running! thanks for spending the time to share mbizup and Pat! = ))

<guys>

gals  

(saying that with a light spirit intended -- we're a bit of a minority on technical forums, and it is very nice to see Pat as a newcomer here.)

haha yes i've seen other experts address you as Miriam but without consent i still address you as mbizup for respect of the anonymity the nick provides! = PP

what i didn't know that Pat is a gal as well! i thought Patrick = ) great to be taught by the gals of EE!! = ))

Thanks Miriam.  Have we met somewhere else?

Pat - definitely through the MVP community, possibly elsewhere.