[AccessD] Fastest Way

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


Try something along these lines.

Max




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
rst1.MoveFirst
Do While Not rst1.EOF
    rst2.AddNew
    For Each fld In rst1.Fields
        strFld = fld.Name
        rst2(strFld) = rst1(strFld)
    Next fld
    rst2.Update
    rst1.MoveNext
Loop

exithere:
Exit Function
errhandler:
Select Case Err.Number

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

End Function






More information about the AccessD mailing list