comparing 2 recordssets and updating

[pbennison]

Hi i have 2 recordset and i need to compare the two. If a record doesnt existing i need it to be added
I have VBA that works but it seems very slow. Is there a better way of doing it

Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM ExorData")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM MainForm")
Dim TJb_Main, TJb_new
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True
TJb_Main = rs!jobno
If Not (rs2.EOF And rs2.BOF) Then
rs2.MoveFirst
Do Until rs2.EOF = True
TJb_new = rs2!worksorderno
If TJb_Main = TJb_new Then
' duplicate so ignore
Else
' new record so add
DBEngine(0)(0).Execute "INSERT INTO mainform (worksorderno) " & "VALUES(" & TJb_new & " ) ", dbFailOnError
End If

rs.MoveNext
Loop
Else
'blank
End If
rs.MoveNext
Loop
Else
' Recordset is blank so do nothing
End If
rs.Close
Set rs = Nothing

 

[Frothingslosh]

1) Always use the CODE tags when posting code here. It helps make the code a LOT more readable. I'd also suggest indenting your code for the same reason.

2) Instead of nested loops doing record-by-record searches, how about something like this:

Start by making a select query in the query builder:

CRAP - got the tables reversed!  See my reply below!

This would pull all records from MainForm that do not have a corresponding entry in ExorData. Then, still using the query builder, change this into an append query (with the destination table being ExorData) and complete it by entering the source and destination fields. (I can't write that from here without the table setups, as you have to enumerate each field receiving data.)

 

[jdraw]

Here is your code in code tags and indented

Sub YouDontHaveASubStatement()

    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM ExorData")
    Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM MainForm")
    Dim TJb_Main, TJb_new
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst
        Do Until rs.EOF = True
            TJb_Main = rs!jobno
            If Not (rs2.EOF And rs2.BOF) Then
                rs2.MoveFirst
                Do Until rs2.EOF = True
                    TJb_new = rs2!worksorderno
                    If TJb_Main = TJb_new Then
                        ' duplicate so ignore
                    Else
                        ' new record so add
                        DBEngine(0)(0).Execute "INSERT INTO mainform (worksorderno) " & "VALUES(" & TJb_new & " ) ", dbFailOnError
                    End If

                    rs.MoveNext
                Loop
            Else
                'blank
            End If
            rs.MoveNext
        Loop
    Else
        ' Recordset is blank so do nothing
    End If
    rs.Close
    Set rs = Nothing

End Sub

 

[pbennison]

Froth
Yes sorry about the code.
Can you please ellaborate ?
What i am building is something that will be used by others so i am using macros and buttons for ease.
The other spreadsheet is imported into a table and i then need to have the user press a button and anything that is not in the mainform would need to be uploaded into the main form
Does that make sense ?

Your help is really appreciated

 

[pbennison]

In case you want to help with the code the tables are
mainform and exordata
fields are
worksorderno -> jobno
worksorderlineno -> lineno
defect -> defectno

 

[Frothingslosh]

Okay, first off, I had the tables reversed, because I misread the code. Whoops. So it looks like you want to take any entries that exist in ExorData but not in MainForm and add them to MainForm. If that's correct, use this as a starting point in Access's query builder:

"SELECT * FROM ExorData
LEFT JOIN MainForm ON ExorData.jobno = MainForm.worksorderno 
WHERE MainForm.worksorderno  Is Null;"

What this does is select all records from ExorData that do not have corresponding records in MainForm.

Now switch the query to DESIGN view. Change it to an APPEND query, select MainForm as the destination table. Then add each of the three fields in MainForm to the query, and in 'Append To' add the matching field from ExorData.

Save that query. The name doesn't really matter - I'd probably call it something like qryAppendExorToMain, myself.

Now, for the codes, replace the entire quoted section with this:

Public Sub InsertQueryNamehere()
 
    DoCmd.SetWarnings False
    DoCmd.RunSQL("qryAppendExorToMain")
    DoCmd.SetWarnings True
 
End Sub

Obviously, you'd change the names in the code to match whatever you went with, and you'd want to add in some basic error handling just in case the append fails (make sure to turn warnings back on in that case).

Hope this helps!

 

[pbennison]

Froth i really apprecdiate your help and have done everything you have said however when i run it i get
runtime 3129
Invalid SQL statement expected delete, insert, procedure, select or update

Have a done something wrong ?

 

[Frothingslosh]

Oh, crap, RunSQL apparently doesn't like Append. Okay, let's do this. Replace this code I gave above with this:

DoCmd.SetWarnings False
CurrentDB.Execute "qryAppendExorToMain"
DoCmd.SetWarnings True

You may want to leave warnings on so you can test it, as well, but you'll want them to turn off for the operation in production.

 

[pr2-eugin]

Or why not simply use CurrentDB.Execute?

CurrentDB.Execute "qryAppendExorToMain"

Or DoCmd.OpenQuery? The main advantage of these two methods is, it does not need an object declaration.

 

[Frothingslosh]

Good catch. For some reason, I seem to have a mental block when it comes to CurrentDB methods most of the time. (Also with DateDiff - I don't know why I can never remember that one when I need it!)

Side note: I really need to imbibe more caffeine before tackling things like this. I just noticed I told the OP to put the destination fields as the column names and the source feels in the 'append to' slots in the append query!

 

[pbennison]

Froth. I really do appreciate your help but ive ran the code and its adding everything !
It wont let me stop it without breaking it and its current at half a gig !!
Any thoughts ?

 

[jdraw]

Just a heads up

when you use this
CurrentDB.Execute "qryAppendExorToMain",dbFailOnError

It will show an error if one should occur.

 

[Frothingslosh]

Okay, here's what you need to do. Unfortunately, I don't have the time to write a proper subquery, so we're going to use stacked queries.

First, save this query as qryExorDataNotInMainForm:

SELECT ExorData.*
FROM ExorData LEFT JOIN MainForm ON ExorData.jobno=MainForm.worksorderno
WHERE (((MainForm.worksorderno) Is Null));

Now, replace qryAppendExorToMain with this:

INSERT INTO MainForm ( worksorderno, worksorderlineno, defect )
SELECT qryExorDataNotInMainForm.jobno, qryExorDataNotInMainForm.lineno, qryExorDataNotInMainForm.defectno
FROM qryExorDataNotInMainForm;

Then run qryAppendExorToMain via the above method.

That should fix your problem. Sorry about the screwup earlier, and if you have more issues, I'm going to be driving for about the next 1.25 hours. I'll check to see how you're doing once I get home, though.

 

[pbennison]

Froth again thanks for the help but when i put the first query in i get a
"check the subquery syntax and enclose the subquery in parentheses" error ?
I'm really sorry if im doing something silly but i have tried looking it up and im even more confused ?

 

[Frothingslosh]

Did you copy and paste them directly into the query builder? Did I use the correct table and field names? If yes to both, what version of Access are you using? That first one doesn't HAVE a subquery in it. For each of those two, I literally threw it together in the query builder in a test db, ran it to make sure it works, and saved the query.

Edit: Here, I've attached the db I used to build the queries.

 

[pbennison]

Hi just tried that it it still doesnt blooking work !

I have copied your queries into mine and it still doesnt work.
I have attached my database if you wouldnt mind having a look at what ive done wrong

Btw im using access 2010

Again thanks for your help

 

[pbennison]

Froth,

It works. You sir are a genius !!
I reentered all of you the code that you put in but put it in the SQL view rather than an expression builder and it works

Thank you, Thank you, Thank you

 

[Frothingslosh]

Hardly a genius - by the standards here, I'm just edging into intermediate.

Glad I could help, though.