Reputation: 15
i wrote that code which should do compare the values of two table and only insert the records that is newly added in the first table and it gives me an error of not finding the Frs
Private Sub MainButt_Click()
Dim mydb As Database
Dim Frs As DAO.Recordset
Dim Srs As DAO.Recordset
Dim testdate As String
Dim strsql As String
Dim secstrsql As String
Dim sqlinsert As String
strsql = "SELECT * FROM Firstable "
secstrsql = "SELECT * FROM secondtable"
Set Frs = CurrentDb.OpenRecordset(strsql)
Set Srs = CurrentDb.OpenRecordset(secstrsql)
sqlinsert = "INSERT INTO Srs ( StransfereDate, transfereName, transfereId, transferAddress ) SELECT Frs.FtransfereDate, Frs.Tname, Frs.TId, Frs.TAddress FROM Frs WHERE NOT EXISTS (SELECT * FROM Srs WHERE ( Frs.FtransfereDate=Srs.StransfereDate AND Srs.transfereName=Frs.Tname AND Srs.transfereId =Frs.TId AND Srs.transferAddress=Frs.TAddress ) )"
DoCmd.SetWarnings False
CurrentDb.Execute sqlinsert
DoCmd.SetWarnings True
End Sub
Upvotes: 0
Views: 48
Reputation: 27644
You can't use VBA recordsets in SQL statements. SQL must refer to tables (or saved queries).
You need something like this:
INSERT INTO secondtable ( ... )
SELECT ... FROM Firstable
WHERE ...
Upvotes: 2