working with multiple dbs

[Thai]

hello, I am using ADO to connect to two databases and I have a question. I have two connections, one for each database. Each database has two identical table templates.. is there a way to insert into table1 of database1 the records from table1 of database2 where field1 of database2 does not match field1 of database1? please help if you can, thanks!

Thai

[Mongo]

What database are you using? (Access2000, MS SQL Server, etc.)

[Thai]

I am using MS Access 97 db. Thanks

[Thai]

Cool thanks Mongo, I will try that. What is the easiest way to link a table with ADO for an ms access 97 db? Also you used jet 4.0, but I use ms access 97, will that work? Thanks again!

Thai

[Mongo]

Thai,

My initial sql_str value was bass-ackwards.
I've corrected it, please ensure you have
the corrected version.

The easiest way to link your tables is to
build the link in your db1 to table1 in db2.
( from the Access menu: File|External Data )
Then you can play with the table through
your ADO connection, as if it were in db1.

I apologize if you need to tweak the actual
connection defs, there are several versions
to skin the cat with; but this should help
to get you started.

[Thai]

Oh, one more thing. There will be situations where 3 fields will make up the unique ID, so if field1, field2, and field3 of table 2 are in field1, field2, and field3 of table1, do not append. how would you modify the sql statement to handle that? I really appreciate all the help!

Thai

[Thai]

hey Mongo, I plugged your code in and it seemed ok til it got to:

tbl1of2.Properties("Jet OLEDB:Create Link") = True

I got runtime error 3265.

Thanks,
Thai

[Mongo]

Well, if the overall intent is still to only
insert records into db1.table1 where db2.table1.field1
do not exist in db1.table1.field1, then the SQL
is good to go.

Otherwise, if you intend only to insert records
where fields 1-3 do not match, then add:

...WHERE NOT EXISTS(SELECT * FROM table1 AS b " & _ 
    "WHERE b.field1 = a.field1 AND b.field2 = a.field2 " & _
    "AND b.field3 = a.field3);"

[Thai]

Looks nice it worked but one small thing.. When it appends the linked table to the cat, then runs the sql execute, it fails. if i wait like 5 seconds and tell it to try again, it works. i guess the table isn't linking fast enough. is there a fix to kill time or a loop to check if the table is there yet? thanks

thai

[Mongo]

Sorry, I'm responding out of sequence.

You can run the gamut of installing ADO 4.0,
but it will be easier to build a link in Access
and ignore the gee-whiz code.

[Thai]

The code is working great and its what I need, I've learned quite a lot from it. The only problem I have is how do I tell the program to wait til the APPEND of the link table is DONE before executing the SQL? Thank you SO much!

Thai

[billyo]

I once faced a situation similar to yours, Thai. But I'm not down with ADO (that is, I don't know a thing about it). For what it's worth, this is what I did. Using the DAO, I loaded the values of each of the table's primary keys into two separate arrays. Then I compared each of the array's element's against each other using a couple of nested for/next loops. If one of the elements wasn't in the other array, then it would add that record. It sounds like you're having success with Mongo's method, but if you're interested in trying another approach I could show you my code.

[Thai]

hey billyo, thanks for the reply. I'd like to see your code just for learning purposes. I'll try it out and compare speed/efficiency. The code Mongo showed me works good but its very slow when dealing with thousands of records. How fast is yours? Thanks again for the response.

Thai

[billyo]

Well, here it is. Not quite so savvy as Mongo's code, but it worked for me. In my case, the database only had about 160 records, so of course it seemed fast to me.

Dim db1 As Database
Dim db2 As Database
Dim rs1 As Recordset
Dim rs2 As Recordset

Set db1 = OpenDatabase(Path_to_DB1)
Set db2 = OpenDatabase(Path_to_DB2)
Set rs1 = db1.OpenRecordset("tbl_1")
Set rs2 = db2.OpenRecordset("tbl_2")
'this is where the tables' primary keys go into an array
i = 1
Do Until rs1.EOF = True
rst1(i) = rs1.Fields!resortnmb
i = i + 1
rs1.MoveNext
Loop
j = 1
Do Until rs2.EOF = True
rst2(j) = rs2.Fields!resortnmb
j = j + 1
rs2.MoveNext
Loop
'this way you can compare the databases without shuffling through them themselves.
'now comes the mechanical comparison
For k = 1 To i - 1 ' this will compare each of the elements in the first array
'against all the elements in the second array.
'got the element from the first array, so get all the ones from the second
    For l = 1 To j - 1 'this nested loop is indexed to the second array
        If rst1(k) = rst2(l) Then 'if an element in the first array equals any of the elements
            Exit For                 'then no need to continue so exit the inner loop and
        End If                      'get the next element from the first array by allowing the
    Next                            'outer loop to advance one.
'now here's where we get tricky.  If it exits the inner loop because it found a match
'then l will be less than j, because it left the inner loop before it got the last element in the
'second array.  but, if the inner loop runs all the way through it's upper index, then it didn't
'find the rst1 element in the set of rst2 elements so
If l = j Then
rs1.Index = "PrimaryKey"
rs1.Seek "=", rst1(k)

With rs2
.MoveFirst
.AddNew
!resortnmb = rs1.Fields!resortnmb
!etc,etc=rs1.Fields!etc,etc
.Update
End With
End If
Next
rs1.Close
rs2.Close
db1.Close
db2.Close

Now that I look at it, it doesn't seem all that hot, but it works. And as it is, the flow only goes oneway, but it wouldn't be too hard to make it flow both ways.

BTW, I learned VB from a talking paper clip so if I've abused any of the vocabulary with my commentary then let me know.

[Thai]

Thanks billyo, that is an interesting approach. I figured out why Mongo's way took a century to run, it was my fault.. the tables had no indexes (doh!). After adding tables to the index, a 10k record table compared to another 10k record table took under 2 seconds. Thanks again for the help both of you! One last question, I figured out how to ADD an index with sql, but when i tired "DROP INDEX Table1.IndexName it did not work, but that is what my book says to do. Wierd. Any input? Also is there a way to do it using the ADOX.Catalog object Mongo was using? Thanks!

Thai