Add / Update problem

[LJG]

Hi Guys, I do an import of a number of spreadsheets and rather than simply
append the data I want to update or add, anyone help? This is my code:

Sub Mass_Import()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSS As String
Set db = CurrentDb
Set rst = db.OpenRecordset("tblUsers") 'the name of the table
rst.MoveFirst
Do Until rst.EOF
strSS = "C:\database\dataimports\Prospects_" _
& rst.Fields("UserName") & ".xls" 'the name of the field
If Dir(strSS) <> "" Then
DoCmd.TransferSpreadsheet acImport, 8, "tblmamprospects", _
strSS, True, ""
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

TIA



Les

 

[Ron Weiner]

In the past when I have had to do this I would link to or import the
spreadsheets to a scratch table. Then execute an update query to update
records that exist in both tables and finally an append query to add the new
records. Last step is to drop the scratch table.

 

[LJG]

Cheers, thanks for that Ron

Sometimes we should just stand back and think laterality!!

Les

 

[Douglas J Steele]

Actually, it's possible to do the update and append in one step (assuming
that the linked tables and the target table are compatible)

I showed how to do this in my November, 2003 "Access Answers" column in
Pinnacle Publication's "Smart Access"

You can download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html