Putting two connections into one statement

[HamotIntern]

This driving me nuts. SQL’s automated DTS does not work with the ODBC driver I am using. So am forced to do it manually. Below is the code. I have the connections working correctly, but when it comes to the actual transfer part, I am at a loss. My coworker’s have tried to help but no luck. I hope someone has an idea or two because to say it nicely, this isn’t much fun.



Option Compare Database
Option Explicit


Private Sub Command0_Click()
On Error GoTo Err_Command0_Click


'SQL
Dim SQLCn
Dim SQLrst
Dim strCriteria

'CareVue
Dim cn
Dim rst
Dim strSQL

'SQL
Set SQLCn = CreateObject("ADODB.Connection")
Set SQLrst = CreateObject("ADODB.Recordset")


'CareVue
Set rst = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")

'SQL
SQLCn.ConnectionString = "Driver={SQL Server}; Server=IMDSQLRPT\TEST; DATABASE=CareVueSQLUpsized; UID=cveditor; PWD=carevue;"
SQLCn.Open

'CareVue
cn.ConnectionString = "DSN=CareVue;UID=ODBC;PWD=ODBC"
cn.Open

'Sets up the SQL Record Set
strCriteria = "Import = True"

'Sets up the Record Set
With SQLrst
.Source = "dbo.bxj_Import"
.ActiveConnection = SQLCn
.CursorType = 1
.Open
End With


'Sets up the CareVue Record Set
With rst
.ActiveConnection = cn
strSQL = "Select * From wteventclass"
End With

'Start outside (SQL) loop
Do While Not SQLrst.EOF
' Read through all the rows in bxj_import

'Start inside (CareVue) loop
strSQL = "INSERT INTO " & SQLrst.Fields(1) & "SELECT * FROM " & SQLrst.Fields(0)
rst.Open strSQL, , 3, 2
'Do While Not rst.EOF
'MsgBox rst("shortlabel")
'rst.MoveNext
'Loop
'End inside (CareVue) loop
SQLrst.Find strCriteria, 1
Loop
'End outside (SQL) loop


' We're finished-close the database objects.
rst.Close
Set rst = Nothing


' We're finished-close the database objects.
SQLrst.Close
Set SQLrst = Nothing