[AccessD] Fastest Way

Max Wanadoo max.wanadoo at gmail.com
Fri Jan 15 13:04:21 CST 2010

Try something along these lines.


Function max()
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

' now move to main table
Set rst2 = dbs.OpenRecordset("select * from max")

' ignore errors ie, dupes
On Error Resume Next
Do While Not rst1.EOF
    For Each fld In rst1.Fields
        strFld = fld.Name
        rst2(strFld) = rst1(strFld)
    Next fld

Exit Function
Select Case Err.Number

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

End Function

More information about the AccessD mailing list