Call 2 different databases

[raptormanad]

Is it possible to call 2 different databases on different machines? Say one local database and one off site through ODBC while using a loop? Something like:

Dim objConn As New ADODB.Connection
objConn.Open "dsn=connone;UID=one;pwd=one;database=one"

SQL = "SELECT * FROM db1.dbo.tblone "
Set RS = objConn.execute

While not RS.eof

Dim objConn As New ADODB.Connection
objConn.Open "dsn=conntwo;UID=two;pwd=two;database=two"

SQL = "UPDATE blah blah blah"

RS.Movenext
Wend
RS.close
Set Rs = Nothing

[vb5prgrmr]

hmmm.... more like...

pseudo code...

adoCn00.open "connection string for db1"
adoCn01.open "connection string for db2"

adoRs00.Open "SELECT * FROM tablename", adoCn00, ...
Do While Not adoRs00.EOF
strSQL = "INSERT INTO tablename(textfieldname, numberfieldname) VALUE('" & adoRs00.Fields("textField") & "'," & adoRs00.Fields("NumberField") & ")"
adoCn01.Execute strSQL
DoEvents ' to keep program responsive
Loop

But some database will allow you to get external data... even from other databases which would be more efficient especially if you are using three machines, ie, two servers and one transaction machine because it would really hit your network hard going from S1 to T1 and then back out to S2 (Server/Transaction/Server), instead of going from S1 to S2. So check the documentation of the DBMS you are using before trying to go this pull/loop/push route...



Good Luck

[si_the_geek]

Thread moved to 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)