DataBase - Transferspreadsheet error trapping

Asked By goaljohnbill

12-Nov-09 08:34 PM

Sorry if this is somwhere else in here but a day of searching in every
combination of keyword I could think of got me nothing.

I am using excel 2003 and access 2003 with some 2000 file format DBs

In access I would like to trap errors on import of excel worksheets so
that I can automatically run code to allow users to fix the excel
file. When I run test code of an import with error handling I still
get the default msg and the coded error handling is skipped.

I found the posts that say for error handling in action queries
replace
docmd.openquery "queryname"
with
db.execute "queryname"

to skip the access error msg and be able to use your own code. Is
there a comparable workaround for docmd.transferspreadsheet?


thanks in advance

13-Nov-09 12:31 AM

THe proper syntax is:
db.execute "queryname", dbFailOnError

Have you tried attached Excel files? That in combination with an
Append query is an alternative to Docmd.Transferspreadsheet. But the
latter will throw a runtime error as well when needed.

What kinds of errors are you looking for?

Show us some code, stripped to the bare essentials.

-Tom.
Microsoft Access MVP

13-Nov-09 06:58 PM

This code is at least the idea of what I think I would like to do. The
goal is to not have the default msg box (or the users choices it
includes) when the import file isnt correct and start the users on the
path to fixing the file automatically.



Function ImportTest()
On Error GoTo ImportTest_Err

DoCmd.TransferSpreadsheet acImport, 8, "Table1", "MyPath
\MyFile.xls", True, ""


ImportTest_Exit:
Exit Function

ImportTest_Err:
If Err.Number = 3022 Then
MsgBox "Dup V value fix in excel by running Macro8"
' I know there is automation I can use here but I would
like to get the error handling running first
AppActivate "Microsoft Excel"
Else
MsgBox "Unknown error contact Tech"
End If
Resume ImportTest_Exit

End Function

When I step through this code I get the the default msg box and answer
no (because i dont want incomplete data put in) that sends me to the
else of the IF.

The databases I would apply this to are 6-8 years old and have alot of
macro type automation that I am working on changing over to VBA to get
these types of functionality as I learn more programming

When you say attached you are refering to linking correct? That might
be a better answer anyway.

thanks
john