[AccessD] Fastest Way

Max Wanadoo max.wanadoo at gmail.com
Fri Jan 15 09:23:29 CST 2010


VB Help says:

Note   System errors during calls to Windows dynamic-link libraries
(DLL) or Macintosh code resources do not raise exceptions and cannot
be trapped with Visual Basic error trapping. When calling DLL
functions, you should check each return value for success or failure
(according to the API specifications), and in the event of a failure,
check the value in the Err object's LastDLLError property.
LastDLLError always returns zero on the Macintosh.

This will do the trick, BUT it will display the system error as described above.
Function max()
    On Error goto errhandler
    Dim strfile As String
    strfile = "c:\estatement.xls"
    DoCmd.TransferSpreadsheet acImport, , "Max", strfile, True
exithere:
    Exit Function
errhandler:
    Select Case Err.Number
    Case Else
    MsgBox "Error: " & Err.Number
    End Select
    Resume Next
End Function

Max


On 15/01/2010, Heenan, Lambert <Lambert.Heenan at chartisinsurance.com> wrote:
> Well at least in Access 2002 when you do this it is true, the unique data
> does get imported to the destination table, but the error handler does not
> get invoked. Here is some code I just tried out...
>
> Sub Importit()
> 10        On Error GoTo Importit_Error
> 20        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> "MyTestTable", "H:\My documents\Access\Access 2002\test\MyTestData.xls",
> True, "MyTestNamedRange"
> 30        On Error GoTo 0
> 40        Exit Sub
> Importit_Error:
> 50        Debug.Print Err, Err.Description, Erl
> 60        Resume Next
> End Sub
>
> The TransferSpreadsheet action runs, and imports all the unique data. Then
> at the end it issues an error that is not trappable by VBA...
>
> "Microsoft Access was unable to append all the data to the table.
>
> The contents of fields in 0 record(s) were deleted, and 409 record(s) were
> lost due to key violations." etc. etc.
>
> Does anyone know how to trap the JET error? (I assume JET is issuing the
> error message.)
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
> Sent: Friday, January 15, 2010 2:45 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Fastest Way
>
> I don't understand what you mean by brute force or findfirst.
>
> Just create  the unique index on the 5 fields on your existing table.  You
> only need  do this  once.
>
> Then import using transfer method with on error routine in place.
>
> If errors out on 3024 (or whatever the dupe record err No. is - this is air
> code)
> Then just resume next
>
> That's it.  No findfirst  or anything.  The transfer method will import each
> record. If a  dupe exists it will go to the error routine where it will be
> told to resume the input.
>
>
> Max
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> Sent: 14 January 2010 23:56
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Fastest Way
>
> That was my first thought - brute force - time is not that critical.  And it
> would only be a couple of lines of code additional in the current import
> routine - just a .FindFirst.  If later it turns out that the time is
> unacceptable, I could bail to the other approach.  Still thinking.... But
> with the brute force method I could quickly set up a test case.  I already
> put start time, end time, and elapsed time test boxes on the form.
>
> Rocky
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
> Sent: Thursday, January 14, 2010 1:36 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Fastest Way
>
> It's a done deal.  If you want speed and efficiency,  then go  my way.
>
> Unique index on relevant fields.  Let access handle the dupes.  One read-One
> input-Finito.
>
> Max
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
> Sent: 14 January 2010 21:22
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Fastest Way
>
> I think we'd all be interested to know what method you eventually use, and
> how the performance is.
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> Sent: Thursday, January 14, 2010 1:33 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Fastest Way
>
> Actually don't want to delete and import - just bypass the incoming records
> that are already in the table.
>
> R
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



More information about the AccessD mailing list