Closing two Recordset issue

[Thinh]

The code below is to compare two tables and see if there is any differences. one of the table act as a historian and the other as an update table.
The historian will record any changes plus any new records that hasnt been stored in the database. I use two two ado recordset with one connection since they are located in the same database. For some weird reason i cant close the rst recordset. Everythings seems to be working fine but i dont want anything leaving open and if you have a better way to do this you feedback will be most helpful. It works but is it best practice?

Private Sub Command0_Click()
Dim mycon As ADODB.Connection
Set mycon = CurrentProject.Connection
Dim rs As New ADODB.Recordset
Dim rst As New ADODB.Recordset
Dim closedate As Date
Dim Deliverydate As Date
Dim TES As String
Dim Opp As String
Dim Opp_Desc As String
Dim win_prob As Integer
Dim Team As String
Dim Forecast As String
Dim PropID As String
Dim Orig As String
Dim Dest As String
Dim Trev As Currency
Dim exist As Boolean
rs.ActiveConnection = mycon
rst.ActiveConnection = mycon
rst.Open "Forecast", , adOpenDynamic, adOpenStatic
rs.Open "[temp forecast]", , adOpenDynamic, adOpenStatic
rs.MoveFirst
rst.MoveFirst
While Not rs.EOF
exist = False
While Not rst.EOF
If rs.Fields("Oppty ID").Value = rst.Fields("Oppty ID").Value Then
closedate = Nz(rst.Fields("close date").Value)
Deliverydate = Nz(rst.Fields("Delivery date").Value)
TES = Nz(rst.Fields("Oppty ID").Value)
Opp = Nz(rst.Fields("Opportunity").Value)
Opp_Desc = Nz(rst.Fields("Opportunity Description").Value)
win_prob = Nz(rst.Fields("Win Probability").Value)
Team = Nz(rst.Fields("Primary Team Member").Value)
Forecast = Nz(rst.Fields("Forecast Type").Value)
PropID = Nz(rst.Fields("Quote Number").Value)
Orig = Nz(rst.Fields("Origination Region").Value)
Dest = Nz(rst.Fields("Project Destination Region").Value)
Trev = Nz(rst.Fields("Total Revenue").Value)
exist = True
End If
rst.MoveNext
Wend
rst.MoveFirst
If exist = False Or rs.Fields("close date").Value <> closedate Or rs.Fields("Delivery date").Value <> Deliverydate Or rs.Fields("Win Probability").Value <> win_prob Or rs.Fields("Forecast type").Value <> Forecast Or rs.Fields("Total Revenue").Value <> Trev Then
rst.AddNew
rst.Fields("close date").Value = rs.Fields("close date").Value
rst.Fields("Delivery date").Value = rs.Fields("Delivery date").Value
rst.Fields("Oppty ID").Value = rs.Fields("Oppty ID").Value
rst.Fields("Opportunity").Value = rs.Fields("Opportunity").Value
rst.Fields("Opportunity Description").Value = rs.Fields("Opportunity Description").Value
rst.Fields("Win Probability").Value = rs.Fields("Win Probability").Value
rst.Fields("Primary Team Member").Value = rs.Fields("Primary Team Member").Value
rst.Fields("Forecast Type").Value = rs.Fields("Forecast Type").Value
rst.Fields("Quote Number").Value = rs.Fields("Quote Number").Value
rst.Fields("Origination Region").Value = rs.Fields("Origination Region").Value
rst.Fields("Project Destination Region").Value = rs.Fields("Project Destination Region").Value
rst.Fields("Total Revenue").Value = rs.Fields("Total Revenue").Value
rst.Update
End If
rs.MoveNext
Wend
'rst.Close <---- Not working

rs.Close
Set rst = Nothing
Set rs = Nothing
Set mycon = Nothing
End Sub

 

[RoyVidar]

Before doing any analysis, could you please tell us what you mean by "close not working"? Does it throw an error, if so - which, or how is it that you determine that it doesn't work?

A couple of minor things - when using Jet, you won't get dynamic cursor. When specifying dynamic, you'll get static or keyset. The locktype you're specifying, is adLockOptimistic, which you specify by using adOpenStatic (both constants give 3). You shouldn't use implicit instantiation unless you really know what you're doing, be explicit in stead!

dim rs as ADODB.Recordset
set rs = New ADODB.Recordset ' explicit!

Set rs.ActiveConnection = mycon
rs.open "Forecast", , adOpenKeyset, adLockOptimistic, adCmdTable

For debugging, you might want to check the following properties of rst

rst.state (0-closed, 1-open, 2-connecting, 4-executing, 8-fetching)
rst.editmode (if rst.EditMode <> adEditNone, you might want to rst.CancelUpdate, or try updating again?)

It does seem this process is a bit convoluted. Could it possibly be achieved by running a query or two? If so, that would be preferrable (in my opinion) over recordset approaches.

BTW - the closing and releasing, though it's often considered good praxis, is usually more important when working with DAO, which I'm told need to be released in the correct order, while ADO doesn't suffer the same, and one should be able to rely on the objects being released when they go out of scope.

(and please, when posting code, could you take the time to include it in code tags, so that it's easier to see indentations, "the flow of the code"...? (click the vBcode link at the bottom left to check out how - or select the text and use the button marked # in the reply panel))

 

[Moniker]

Wow, that's a convoluted way to do a comparison and update. There's a much simpler way using a single query. Assuming both tables have the same structure (the "Historian" and "Update" tables) and both have a primary key (it looks like you're using "Oppty ID" as the key), you can just LEFT JOIN on the Primary Key from Update into Historian where Historian's Primary Key is NULL.

See the attached for an example of this. To see this in action, go to t_Updates in the sample DB and add a new record (say, TestID=5, and TestName="Test5"). Then, go to the q_Update query and run it. The table t_Historian will be appended with just your one new addition.

It's a lot cleaner this way and much easier to maintain.

~Moniker

 

[Thinh]

Thanks for the fast response

the reason i doing this is to be able to track the changes. I want to be see the difference but also able to track changes as well. The update file will be imported monthly and compare to the historian any changes will be added to the historian and new records that doesnt appear in historian will be added for future reference. I need to look at 5 different fields as the changes occured there. The logic is now is if any of the field changes then that records get to copy into the historian. The error that i get when include rst.close is that its out of context. I only want to track the recent change.

I am a new to VBA, the code that i have come from the book access for dummies with my modification.

Oh yeah now that i have the changes what is the easiest way to sort and show the user what records that has change. one idea is to have color next to each records. green for no change and red for changes. This one of the idea i have but not sure how i go about program. Is it possible to do that in the details section?

Thanks for the response

 

[Thinh]

Psedo code

Here is the psedo code:

read first record in update table
compare that record with records in historian.
if it exist in the historian compare all the fields that the change can occured.
if a change occured in any of the fields then copy it into a variable then go the
next record.
if another instance exist in historian then overwrite the the variable with current record historian.
if the records couldnt be found in the historian then copy it to historian.
then go the next update record.

Hopefully that will make it easier to see what i am trying to do

 

[Thinh]

Code indent to show the flow

Private Sub Command0_Click()
Dim mycon As ADODB.Connection
Set mycon = CurrentProject.Connection
Dim rs As New ADODB.Recordset
Dim rst As New ADODB.Recordset
Dim closedate As Date
Dim Deliverydate As Date
Dim TES As String
Dim Opp As String
Dim Opp_Desc As String
Dim win_prob As Integer
Dim Team As String
Dim Forecast As String
Dim PropID As String
Dim Orig As String
Dim Dest As String
Dim Trev As Currency
Dim exist As Boolean
rs.ActiveConnection = mycon
rst.ActiveConnection = mycon
rst.Open "Forecast", , adOpenDynamic, adOpenStatic
rs.Open "[temp forecast]", , adOpenDynamic, adOpenStatic
rs.MoveFirst
rst.MoveFirst
While Not rs.EOF

exist = False
While Not rst.EOF

If rs.Fields("Oppty ID").Value = rst.Fields("Oppty ID").Value Then
closedate = Nz(rst.Fields("close date").Value)
Deliverydate = Nz(rst.Fields("Delivery date").Value)
TES = Nz(rst.Fields("Oppty ID").Value)
Opp = Nz(rst.Fields("Opportunity").Value)
Opp_Desc = Nz(rst.Fields("Opportunity Description").Value)
win_prob = Nz(rst.Fields("Win Probability").Value)
Team = Nz(rst.Fields("Primary Team Member").Value)
Forecast = Nz(rst.Fields("Forecast Type").Value)
PropID = Nz(rst.Fields("Quote Number").Value)
Orig = Nz(rst.Fields("Origination Region").Value)
Dest = Nz(rst.Fields("Project Destination Region").Value)
Trev = Nz(rst.Fields("Total Revenue").Value)
exist = True
End If
rst.MoveNext​

Wend
rst.MoveFirst​

If exist = False Or rs.Fields("close date").Value <> closedate Or rs.Fields("Delivery date").Value <> Deliverydate Or rs.Fields("Win Probability").Value <> win_prob Or rs.Fields("Forecast type").Value <> Forecast Or rs.Fields("Total Revenue").Value <> Trev Then
rst.AddNew
rst.Fields("close date").Value = rs.Fields("close date").Value
rst.Fields("Delivery date").Value = rs.Fields("Delivery date").Value
rst.Fields("Oppty ID").Value = rs.Fields("Oppty ID").Value
rst.Fields("Opportunity").Value = rs.Fields("Opportunity").Value
rst.Fields("Opportunity Description").Value = rs.Fields("Opportunity Description").Value
rst.Fields("Win Probability").Value = rs.Fields("Win Probability").Value
rst.Fields("Primary Team Member").Value = rs.Fields("Primary Team Member").Value
rst.Fields("Forecast Type").Value = rs.Fields("Forecast Type").Value
rst.Fields("Quote Number").Value = rs.Fields("Quote Number").Value
rst.Fields("Origination Region").Value = rs.Fields("Origination Region").Value
rst.Fields("Project Destination Region").Value = rs.Fields("Project Destination Region").Value
rst.Fields("Total Revenue").Value = rs.Fields("Total Revenue").Value
rst.Update
End If
rs.MoveNext​

Wend
#rst.Close <-- out of context

rs.Close
Set rst = Nothing
Set rs = Nothing
Set mycon = Nothing
End Sub

 

[RoyVidar]

So, if I understand correct, if there is a row in the update table where the id field, and there's a difference in one or more of five fields between this and the history table, or there is a row in the update table which doesn't exist in the history table, it should be appended?

Your structure is way to complicated for me to work with. Lets assume two much smaller tables with the following structure

tblUpdate
id - PK (Autonumber)
fieldA - Text
fieldB - Text
fieldC - Number - Long Integer

tblHistory
NewID - PK (Autonumber)
id - Number - Long Integer
fieldA - Text
fieldB - Text
fieldC - Number - Long Integer

The first suggestion might work if none of the fields can be Null

Sub JustTestingUpdate()

    Dim cn As ADODB.Connection
    Dim strSql As String
    
    Set cn = CurrentProject.Connection
    
    strSql = "INSERT INTO tblHistory ( id, fieldA, fieldB, fieldC ) " & _
            "SELECT u.id, u.fieldA, u.fieldB, u.fieldC " & _
            "FROM tblUpdate u LEFT JOIN tblHistory h ON " & _
            "(u.id = h.id) AND (u.fieldA = h.fieldA) AND " & _
            "(u.fieldB = h.fieldB) AND (u.fieldC = h.fieldC) " & _
            "WHERE h.id Is Null"
            
    cn.Execute strSql, , adCmdText + adExecuteNoRecords
    
End Sub

if, say fieldA and fieldB could be Null, then perhaps

Sub JustTestingUpdate2()
    
    Dim cn As ADODB.Connection
    Dim strSql As String
    
    Set cn = CurrentProject.Connection
    
    strSql = "INSERT INTO tblHistory ( id, fieldA, fieldB, fieldC ) " & _
            "SELECT u.id, u.fieldA, u.fieldB, u.fieldC " & _
            "FROM tblUpdate u LEFT JOIN tblHistory h ON " & _
            "(u.id = h.id) AND (NZ(u.fieldA, '0') = NZ(h.fieldA, '0')) " & _ 
            "AND (NZ(u.fieldB, '0') = NZ(h.fieldB, '0')) AND " & _
            "(u.fieldC = h.fieldC) " & _
            "WHERE h.id Is Null"
    
    cn.Execute strSql, , adCmdText + adExecuteNoRecords

End Sub

Note - this is air code - not much testing etc, and you'd need to adapt it yourself. Test and see if this gives you what you need.

 

[Thinh]

Your assumption is correct

Thanks for the fast response
and for codes

let me check out the codes and see if it will works. One more question between sql and recordset which is a better approach? i mean when it comes to resources and speed. I have a programming background that why i choose to use recordset.

 

[RoyVidar]

Using queries will in most cases prove more effective, and is usually recommended over recordset approaches.

smallish (air) code to create test tables/data

dim cn as adodb.connection
set cn = currentproject.connection
cn.execute "CREATE TABLE tblUpdate (" & _
    "id Int Identity PRIMARY KEY, " & _
    "fieldA varchar(30), fieldB varchar(30), " & _
    "fieldC Int)", , adcmdtext+adexecutenorecords
cn.execute "CREATE TABLE tblHistory (" & _
    "NewID Int Identity PRIMARY KEY, id Int, " & _
    "fieldA varchar(30), fieldB varchar(30), " & _
    "fieldC Int)", , adcmdtext+adexecutenorecords
cn.execute "INSERT INTO tblUpdate ( fieldA, fieldB, fieldC) " & _
    "Values ('blah', 'blah', 10)", , adcmdtext+adexecutenorecords
cn.execute "INSERT INTO tblUpdate ( fieldA, fieldB, fieldC) " & _
    "Values ('more blah', 'blah', 10)", , adcmdtext+adexecutenorecords
cn.execute "INSERT INTO tblUpdate ( fieldA, fieldB, fieldC) " & _
    "Values ('blah', 'more blah', 10)", , adcmdtext+adexecutenorecords

 

[Moniker]

You'll find that the query approach, at least in this case, is much more maintainable over time. VBA is great for when you need to use code, but if Access already has a built-in function to do something (such as comparing two tables), use the built-in functionality over a custom solution. If you have to go back to the code in six months, seeing the query will be a lot clearer than seeing 80 lines of code.

~Moniker

 

[Thinh]

Hope this picture allow you to see what i really want to accomplish

Table

Update ____________________________Historian
ID Model Desc ______________________ID Model Desc
1 __ 2 __ House ______________________1 __ 2 __ House
2 __ 4 __ Car ________________________2 __ 3 __ Car
3 __ 5 __ Boat _______________________3 __ 5 __ Airplane
4 __ 6 __ Helicopter___________________3 __ 5 __ Jetski

New Historian should look like this
Historian
ID Model Desc
1 __ 2 __ House
2 __ 3 __ car
2 __ 4 __ car <--------- Changed Model
3 __ 5 __ airplane
3 __ 5 __ Jetski
3 __ 5 __ Boat <--------- Changed Desc
4 __ 6 __ Helicopter <--------- New Records

Hopefully this help you guys figure out what is the easiest way to approach this problem

The code that gave me roy will take care of the new records but it wont take care of the changes that was made to the existing records because of the where criteria "historian id is null"

The Update records only track changes that are made by the recent records meaning if there is more than one change in the historian it should only compare the update with the most recent change. like the exemple above
historian
ID __ Model __ Desc
3 __ 5 __ Airplane
3 __ 5 __ Jetski

The update records should only compare to the jetski record

Does that shred any light ?

Thanks for everyone response. I really appreciate the help

My young grasshopper

 

[Moniker]

Update ____________________________Historian
ID Model Desc ______________________ID Model Desc
1 __ 2 __ House ______________________1 __ 2 __ House
2 __ 4 __ Car ________________________2 __ 3 __ Car
3 __ 5 __ Boat _______________________3 __ 5 __ Airplane
4 __ 6 __ Helicopter___________________3 __ 5 __ Jetski

New Historian should look like this
Historian
ID Model Desc
1 __ 2 __ House
2 __ 3 __ car
2 __ 4 __ car <--------- Changed Model
3 __ 5 __ airplane
3 __ 5 __ Jetski
3 __ 5 __ Boat <--------- Changed Desc
4 __ 6 __ Helicopter <--------- New Records

Having a model like that is just asking for data normalization issues. Why would you want three entries with ID=3 and Model=5? There's no way to distinguish except by the description, and even then, how do you know which one you want to use? I would highly recommend against that design, unless you throw in a third key such a date. Otherwise, there's no way to tell which is history and which is the update.

~Moniker

 

[Thinh]

There will be a date field which will be the updated field

The reason for this is to be able to see the changes. The historian acts as a log.
Once i have the table i can just group them and count the occurance if there is more than one occurance than i know a change has occured for that specific oppty id. You will be able to tell which order by update date but also the order they are in the table. The most recent record will always be the one that is further down the list. This way it is easy to track the changes, you only need to query on the oppty id and you will get all the changes that was made to that oppty id. There will be a update date include iin all of them but the order in the table will tell you which records is the recent.

Hope my logic makes some sense

 

[RoyVidar]

Be VERY carefull about relying on the order of tables! (Rather DON'T rely on the order of tables, as the likelihood it will sooner or later barf is significant! Use queries if specific order is needed!)

If you have an update date, why is this exercise necessary? I've usually identified history tables with the primary key of the main table, and the updated date, then you could use the following to find the changes:

SELECT u.ID, u.UpdatedDate, u.Model, u.Descr
FROM tblUpdate u LEFT JOIN tblHistory h ON
    (u.UpdatedDate = h.UpdatedDate) AND 
    (u.ID = h.ID)
WHERE h.ID Is Null

I e, this should give you each record in tblUpdate which does not have a corresponding entry entry in tblHistory. Stuff it into an append query, and you'll be able to append them (be carefull with Nulls).

Else, the approach I outlined should give you exactly the result you said you wished, see attached, though, should you change something back to an existing value, and a record where all the joined fields exists in both tables, then it wont add. The quey in the attached db won't open in design view, due to the NZ usage in the joins, but you'll be able to run it, and view the SQL. Here's the update code, which is assuming Descr can be Null, but Model not

Sub JustTestingUpdate()
    
    Dim cn                  As ADODB.Connection
    Dim strSql              As String
    
    Set cn = CurrentProject.Connection
    
    strSql = "INSERT INTO tblHistory ( ID, Model, Descr ) " & _
            "SELECT u.ID, u.Model, u.Descr " & _
            "FROM tblUpdate u LEFT JOIN tblHistory h ON " & _
            "(u.ID = h.ID) AND (NZ(u.Model, 0) = NZ(h.Model, 0)) AND " & _
            "(NZ(u.Descr, '0') = NZ(h.Descr, '0'))  " & _
            "WHERE h.ID Is Null"
    
    cn.Execute strSql, , adCmdText + adExecuteNoRecords

End Sub

 

[Thinh]

The update table is actually imported list from a different system

The reason for this logic is the the update list is actually data that is imported from a different system. the update data will be collect once a month and therefore they will not have the same update date on them. The only thing that doesnt change between update and historian is the oppty id. I see how your logic works but what happend if i want to put a twist to it. I want to be able to tell if it is a change records from a new records. the code you have given wont be able to tell the difference from a new or change record.

 

[Thinh]

i tested the code roy and seems to what you tell me

Can you explain in detail how it works i thought. You can explain in in terms of math or set series theory i know union and intersection. The only thing that i am concern about is if there are two records in there that has same id but change multiple times. I dont see why the code would add multiple lines for them. let me elaborate on that.

History
ID -- Model -- Desc
1 -- 2 -- Car
1 -- 2 -- House

Update
ID -- Model -- Desc
1 -- 2 -- Boat

With the logic of the code it would produce the following results.

New History
ID -- Model -- Desc
1 -- 2 -- Car
1 -- 2 -- Boat
1 -- 2 -- House
1 -- 2 -- Boat

I dont see how this is not happing with the code

 

[RoyVidar]

"I want to be able to tell if it is a change records from a new records. the code you have given wont be able to tell the difference from a new or change record." - that is true, but I can't find that in the initial question

"I dont see how this is not happing with the code"

Hm - first, think of an ordinary join between the two tables on the fields ID Model and Descr (I've added an "r" to avoid reserved word). In your last sample, that would produce zero records, as there are no records in both tables where ID, Model and Descr are the same.

Right? A join on more than one field, would require that every field on which the join is performed for one record in the history table, would need an excact match in the update table?

I e

History
ID -- Model -- Desc
1 -- 2 -- Car
1 -- 2 -- House

Update
ID -- Model -- Desc
1 -- 2 -- Boat

Bu-huu - no match

History
ID -- Model -- Desc
1 -- 2 -- Car
1 -- 2 -- House

Update
ID -- Model -- Desc
1 -- 2 -- HOuse

Ah - a match


Now, do a Left Join.

SELECT u.*, h.*
FROM tblUpdate u LEFT JOIN tblHistory h ON 
    (u.ID = h.ID) AND (NZ(u.Model, 0) = NZ(h.Model, 0)) AND 
    (NZ(u.Descr, '0') = NZ(h.Descr, '0'))

This should give you every record in the update table, and the corresponding (equal records) in the history table, should such exist. If a corresponding record in the history table does not exist, then every field for that row from the history table, will be Null. In your last sample, there is no corresponding records in the history table, so the above query should "show":

3 2 Boat Null Null Null

Which means that if you stuff the above select into an append query, that row, and that row only, will be appended to the history table.

But, to be sure not to update any records that has a corresponding record alredy in the history table, I will disallow them, by requiring that only records where the history tables ID Is Null is supposed to be shown.

...
WHERE h.ID Is Null

I'm sorry, I don't think I'm able to explain any better...

 

[Thinh]

thanks for the education. i think i start to get it

let me see if i got idea correctly

i think the only confusion i have right now is the left join. Let me see if i got it right.

i cant see the immediate step, but i think i kind of get it.
if there is no match then it will pad null to the historia however this step is now shown right?


Update ___________ History
ID Model Desc -------ID Model Desc
1 2 car ----------Null Null Null <-------- Not match
1 1 house --------Null Null Null <-------- Not match
2 1 Truck --------2 1 Truck
2 2 Boat ---------2 2 Boat

This is the process of a left join right?
if this is the case then i understand where history.id is null comes in

This is so much cleaner than what i had. I need suggestion on how to allow users to see the changes. the method that i use is to groups of id that has count greater than one. This will works for changes but wont work for new records. I need a way to tell which records was changed and new records that was added. I have some clue with the recordsset but with the code you gave me i have no direction to approach this. i want to use the update table as main table that the user will interact with. i been thinking add extra column that exist in import spec a status field that tells if the records was change or if it was a new record. with a recordset i could determine what kind of write it was if it was a new record or change write but with the code that you gave me it seems like new and change records are threated the same way. what i want to do is this.

This contiuous section the change form

Status------------- ID Model Desc
NC(no change green) 1 3 car
M(modified red)----- 2 4 house
NR(New Record Blue) 3 5 boat

status field is a textbox because i can use conditional formatting to show the color based what text is display in that field. i can line the changes next to each other by looking up that id but i also want to show where the changes occured, what field changes so the user can easier see it. this is a small sample but the real deal might have more than just 3 fields.

thanks for in advance, You been great roy, i learn a great deal of this

 

[RoyVidar]

Well, you can see the immediate step. Both in the sample DB, you could check out the query there, or you could copy/paste my above select, which gives you exactly that

An ordinary join gives you only the rows in each table where the joined fields are equal. Outer joins (which Jet doesn't support fully, but partially through RIGHT JOIN/LEFT JOIN) allows you to see all records from one table, and only those records from the other table where joined fields are equel (add two tables to the query grid, then right click the relationship between them, and enter "Join Properties" to see/test the interface for it).

To satisfy the "New" vs "Changed", should be quite simple, well just have to execute two separate queries. One joining only on the ID, the next one - I'm not sure, but I think I'd used the one we've worked with.

Short sample, where I've included a field "Status" in tblHistory, and stuff either "Changed" or "Added" into it during the process

Sub JustTestingUpdate()
    
    Dim cn                  As ADODB.Connection
    Dim strSql              As String
    Dim lngChanged          As Long
    Dim lngAdded            As Long
    
    Set cn = CurrentProject.Connection
    
    strSql = "INSERT INTO tblHistory ( ID, Model, Descr, Status ) " & _
            "SELECT u.ID, u.Model, u.Descr, 'Added' " & _
            "FROM tblUpdate u LEFT JOIN tblHistory h ON " & _
            "(u.ID = h.ID) " & _
            "WHERE h.ID Is Null"

    cn.Execute strSql, lngAdded, adCmdText + adExecuteNoRecords

    strSql = "INSERT INTO tblHistory ( ID, Model, Descr, Status ) " & _
            "SELECT u.ID, u.Model, u.Descr, 'Changed' " & _
            "FROM tblUpdate u LEFT JOIN tblHistory h ON " & _
            "(u.ID = h.ID) AND (NZ(u.Model, 0) = NZ(h.Model, 0)) AND " & _
            "(NZ(u.Descr, '0') = NZ(h.Descr, '0'))  " & _
            "WHERE h.ID Is Null"
    
    cn.Execute strSql, lngChanged, adCmdText + adExecuteNoRecords
    
    MsgBox "Added " & lngAdded & " Changed " & lngChanged
    
End Sub

Note that I haven't tested all possible permutations, but are suggesting these as a means of reducing the amount of recordset operations, which has a tendency to both overcomplicate things, be more convoluted than necessary and slower/less reliable.

 

[Thinh]

Thanks for the help.

Looks like this will solve most of the problem i have but i have one concern wont the second section get new record aswell ?

because that is no difference from the code that you gave me earlier