merging recordsets

[HotMadras]

I have a customer who wants to be able to upload updates to his database as access files. These access files will contain a table called parts which contains the records to be merged with the existing "main" database (which also has a table called parts, into which these new (or updated) parts should go).

I have tried to create a subroutine to merge the two recordsets, so that if the data in the new parts table matches that in the old, the old data gets overwritten, otherwise the data should be added to the old table. Unfortunately I've come up against a brick wall in the form of one of ASPs lovely incomprehensible, and often wildly inaccurate, error messages. I can't figure out where I've gone wrong and I've a feeling my changes to the code are just making things worse at this stage. Below is the sub which, supposedly, merges the recordsets. I know it's probably totally wrong, but it's all I've got. Some of the lines are quite long and may get wrapped in peculiar places.

private sub mergeDB (newdb, olddb)
Dim dbc
dim dbc2
Dim strConn
dim strConn2
dim rs
dim rs2
strConn = "Driver={Microsoft Access Driver (*.mdb)};DBQ="& olddb
strConn2 = "Driver={Microsoft Access Driver (*.mdb)};DBQ="& newdb
Set dbc = Server.CreateObject("ADODB.Connection&quot
set dbc2 =server.createobject("adodb.connection&quot
dbc.open strConn
dbc2.open strConn2
strSQL2="SELECT * FROM parts"
set rs2=server.createobject("adodb.recordset&quot
rs2.open strSQL2, dbc2, adOpenStatic, adLockReadOnly, adCmdText
while not rs2.eof
strSQL="select * from parts where partno='" & rs2("partno&quot & "'"
set rs=server.createobject("adodb.recordset&quot
rs.open strSQL, dbc, adOpenKeyset, adLockOptimistic, adCmdText
while not rs.eof
for each i in rs.fields
if rs2.fields(i)<>&quot;&quot; then rs.fields(i)=rs2.fields(i)
next
rs.movenext
wend
if rs.eof then
rs.addnew
for each i in rs.fields
if rs2.fields(i)<>&quot;&quot; then
rs.fields(i)=rs2.fields(i)
else
rs.fields(i)=&quot;&quot;
end if
next
rs.update
end if
rs.close
set rs=nothing
rs2.movenext
wend
rs2.close
set rs2=nothing
dbc.close
set dbc=nothing
dbc2.close
set dbc2=nothing

End sub

The error I get when running the above code is:

ADODB.Recordset error '800a0bb9'

The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another.

/indespension/Loader.asp, line 248

Line 248 contains the code &quot;rs2.open strSQL2, dbc2, adOpenStatic, adLockReadOnly, adCmdText&quot;, which I can't see any problem with.

If anyone can a) see what I've done wrong or b) offer a different solution to this problem I'd love to hear about it.

Thanks

 

[Tarwn]

A slightly slower (processing time wise) way to do this that is very similar is:
Select from one table
Do until EOF
Select from other table for that record only
if other.EOF
insert record
end if
Loop

This will take much longer, but in my opinion your customer really shouldn't mind as what you are really doing is attempting to import and append database on top of each other with each upload, which is not a fun process (as I am sure you have found )
-Tarwn

 

[HotMadras]

Thanks but your solution stops a little short of what I was trying to achieve. I'm pretty sure I can deal with the case where a new record is to be added, however I also have to deal with the case where a record is to be updated (that is the record already existed in the old database and it is to be replaced with new data from the new database). Any ideas?

 

[Tarwn]

This is the easiest way to do it:

Select from new table
Do until EOF
Select from existing table for that record only
if existing.EOF
insert record
else
update existing from new
end if
Loop

Your looping through each row again, inserting the info it's not in the existing db, or updating if the info already exists. The bad thing about doing it this way is you may be updating rows that won't change (ie are the same in both tables). This does take extra time, but might be easier than doing another if check, dpeending on the number of fields in your table. If you would like to add the if check you should change the else to an elseif followed by equivalence checks on every single fields in the two tables.

Now that I typed this I just realized that most of what I have written you have actually done in your code, I must apologize I was really tired last night.

Referring back to your already complete code (that I would run just fine if I were IIS, but we all know how that goes)
Check your keys.
The error is an error based on argument types.

Out of range: they are constants that are used in several places, this shouldn't be the error.
Wrong Type: they are constants that are used in several places, this shouldn't be the error.
Conflict: Possible, try changing one key at a time until the problem disappears, if it does not we'll go from there, but I am willing to bet it is a conflict

Your opening the first stmt as adCmdText, try changing this to adCmdTable since you are in effect just opening parts, then instead of using sql just put the table name in:

rs2.open "parts", dbc2, adOpenStatic, adLockReadOnly, adCmdTable

See if you get differant results with that. If the keynames are ok and this line is still causing an error the possibility will be either the rs2 object or the dbc2 object. I doubt it's one of these due to the error message, but thats IIS always giving us easy to understand errors.
-Tarwn

 

[HotMadras]

Thanks, I managed to fix this yesterday. The problem was due to me (stupidly) trying to update the key field (an autonumber). Once I altered the for loop to loop through elements 1 through 8, rather than all of them as before, everything worked fine. There were some other changes too, but I think most of them were fairly incidental, at that stage I was trying anything I could think of!!

Thanks for your reply!