[AccessD] Fastest Way

Rocky,

Try   this.
Because we use currentbd.execut  it wont throw  the error , just ignores it.

VERY fast.

Max




Function max2()
On Error GoTo errhandler
Dim sql As String, rst1 As DAO.Recordset, rst2 As DAO.Recordset, dbs As
DAO.Database, fld As Field, strFld As String

Set dbs = CurrentDb

' if you want  to create a temp table on the fly, do this.
sql = "Drop table maxtemp"
CurrentDb.Execute (sql)
sql = "create table maxtemp (IncomeDesc text, SumOfAmount double)"
CurrentDb.Execute (sql)
' else do this
sql = "Delete * from maxtemp"
CurrentDb.Execute (sql)

' read data into temp table
DoCmd.TransferSpreadsheet acImport, , "maxtemp", "c:\Max.xls", True

Set rst1 = dbs.OpenRecordset("Select * from maxtemp")
' do your error checks in the maxtemp  table
Set rst1 = Nothing

' now add to main table
sql = "INSERT INTO [max]SELECT * FROM maxtemp;"

CurrentDb.Execute (sql)


exithere:
Exit Function
errhandler:
Select Case Err.Number

Case Else
MsgBox Err.Number & vbCrLf & Err.Description
End Select

End Function




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: 15 January 2010 16:21
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Fastest Way

I like it!  But I still have to import to a temp table because there is
other error checking that must be done on a record in addition to checking
for dupes. 

R