Check Record Existance in a table

[cenderawasih]

Hi,
I need to copy data from one table into another table and manage to do this if the destination table is blanks.When the destination table is not blank anymore(contains the first time copied data from the main table), then I want to add new data in the main table into the destination table, my coding gives me errors.

What the codes should do is to check for data in the main table and the data in the destination table, the matching data should be left alone, but when it found an unmatch data in the main table with the destination table (meaning the data exist in the main table but not yet copied/didn't exist in the destination table) then these data (in the main table) should be copied/add to the destination table...

Here is my code:
Private Sub Form_Load()

Dim cnn As New ADODB.Connection
Dim rstFPT1JP00 As New ADODB.Recordset
Dim rstTemp As New ADODB.Recordset
Dim strFPTIJP00 As String
Dim strTemp As String

Dim strCriteria As String

Set cnn = CurrentProject.Connection
strFPT1JP00 = "SELECT PTMCU,PTDIV,PTDFS,PTDTS,PTABNO,PTNAME,PTSTS,PTDFM,PTDTM,PTDCO FROM SRPRJTRAK_FPT1JP00"
strTemp = "SELECT tempPTMCU,tempPTDFS,tempPTDTS,tempPTDFM,tempPTDTM,tempPTDCO FROM TblTempDate"
strCriteria = tempPTMCU = PTMCU

'OPEN MAIN TABLE
With rstFPT1JP00
Set .ActiveConnection = cnn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open strFPT1JP00
.MoveFirst

'OPEN DESTINATION TABLE
With rstTemp
Set .ActiveConnection = cnn
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.Open strTemp

'THIS PART ALSO DIDN'T WORK, IT SEEMS THAT WHETHER THE DESTINATION TABLE IS BLANK(HAVE NO VALUE) OR NOT BLANK (HAVE VALUE) THE RECORDCOUNT WITH ALWAYS EQUAL TO -1. THE CODES BELOW SHOULD RUN IF THE SYSTEM FOUND THAT THE RECORDCOUNT FOR DESTINATION TABLE=0 (DESTINATION TABLE IS BLANKS)

If rstTemp.RecordCount = 0 Then

Do Until rstFPT1JP00.EOF

rstTemp.AddNew

rstTemp.Fields("tempPTMCU&quot = rstFPT1JP00.Fields
("PTMCU&quot.Value

rstFPT1JP00.MoveNext
Loop


'WHEN THE DESTINATION TABLE IS NOT BLANK, THEN THIS CODE WILL BE RAN. ANYWAY THE FIND COMMAND AND THE NOMATCH COMMAND GIVES ME THIS ERROR MSG:"method or data member not found".

Else

rstTemp.FindFirst strCriteria
If rstTemp.NoMatch = False Then
rstTemp.MoveLast

Do Until rstFPT1JP00.EOF

rstTemp.AddNew

rstTemp.Fields("tempPTMCU&quot = rstFPT1JP00.Fields
("PTMCU&quot.Value

rstFPT1JP00.MoveNext
Loop
End If
End If
End With
End With
rstFPT1JP00.Close
Me.Requery
End Sub

FOR UR INFORMATION I HAVE TICK THE REFERENCE FOR DAO OBJECT.

CAN ANYONE HELP ME WITH THIS ONE?

 

[BNPMike]

I need to copy data from one table into another table and manage to do this if the destination table is blanks.When the destination table is not blank anymore(contains the first time copied data from the main table), then I want to add new data in the main table into the destination table

This sounds like a very simple thing to do in SQL. Why are you programming it yourself in VBA?

 

[Edski]

.MoveFirst will give an error if there are no records.

You need to trap this possibility before issuing the .MoveFirst command. Eg,

If rstFPT1JP00.EOF Then 
   ...
Else
   .MoveFirst
   ...
End If

 

[Edski]

'THIS PART ALSO DIDN'T WORK, IT SEEMS THAT WHETHER THE DESTINATION TABLE IS BLANK(HAVE NO VALUE) OR NOT BLANK (HAVE VALUE) THE RECORDCOUNT WITH ALWAYS EQUAL TO -1. THE CODES BELOW SHOULD RUN IF THE SYSTEM FOUND THAT THE RECORDCOUNT FOR DESTINATION TABLE=0 (DESTINATION TABLE IS BLANKS)

You need to issue the .MoveLast command to populate the recordset so that .RecordCount gives the correct number.

 

[cenderawasih]

Thanks Edski and BNPMike

 

[Edski]

cenderawasih,
Your criteria string:

strCriteria = tempPTMCU = PTMCU

isn't returning a string, but a boolean (True or False). You need to put quotes around the expression.
Good Luck.

 

[GHolden]

Hi,

FYI:

If you have two identical tables and you want to copy all records from table2 (tblTwo) into table1 (tblOne) where records do not exist already in table2 you can do it with one line of SQL like this (the common key in this example is ID.

INSERT INTO tblOne SELECT * FROM tblTwo
WHERE tblTwo.ID NOT IN (SELECT tblOne.ID FROM tblOne);





There are two ways to write error-free programs; only the third one works.

 

[OhioSteve]

It is rare to intentially put data in two places simultaneously. However if you must do that then GHolden's suggest is the way to go, no doubt.

 

[cenderawasih]

Hi GHolden,

i'm getting a sintax error from the SQL.This is how i code it:

Dim strsql as string

'****sintax error here*****
strsql=INSERT INTO TBLTEMP SELECT * FROM TBLMAIN WHERE_
TBLMAIN.ID NOT IN (SELECT TBLTEMP.ID FROM TBLTEMP);
'**************************

DOCMD.RUNSQL "strsql"

 

[Edski]

cenderawasih ,

Dim strSQL as string

strSQL = "INSERT INTO TBLTEMP SELECT * FROM TBLMAIN WHERE 
TBLMAIN.ID NOT IN (SELECT TBLTEMP.ID FROM TBLTEMP);"

DoCmd.Execute strSQL

 

[Edski]

Doh,

Dim strSQL as string

strSQL = "INSERT INTO TBLTEMP SELECT * FROM TBLMAIN WHERE 
TBLMAIN.ID NOT IN (SELECT TBLTEMP.ID FROM TBLTEMP);"

CurrentDb.Execute strSQL

 

[JGillespie]

if you don't want to bother with code then as long as the ID field is set to primary key in the destination table a simple append query from the source to destination will do what you want.

just need to set docmd.setwarnings = false before running append and docmd.setwarnings = true when finished to suppress errors about records not being appended due to key violations

Jamie Gillespie
j-gillespie@s-cheshire.ac.uk