Putting two connections to work in 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


'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

 

[vongrunt]

Three fundamental debugging questions:

- what happens?
- what should happen?
- is there any smoke coming outside computer case?

recordset SQLrst, columns 0 and 1: what they contain? Names of target and source table to copy? All tables have identical structure?

This line doesn't do anything useful:

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

Inner loop is commented out. OK, INSERT INTO won't return a recordset to client but... can you briefly explain what this code is supposed to do in general?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[HamotIntern]

You are correct, field one is the name of the table to import, field two is the table to import into. Yes, the source and destinations have the same structure.
IF I could SQL query the source tables, I’d be very happy. However, SQL Server cannot link the tables and its DTS Wizard does not work on this database, which is CareVue, that is an AllBase database. So, I need to create an importing script. A little bit of information about CareVue.
-Some of the tables are quite large (Over 550,000 records)
-I would like to import 26 of the 300 tables
-The tables are not the same (obviously, but I thought it needed to be said)

To summarize, I would like to import 26 tables on a regular basis (every 24 hours or so) from CareVue (AllBase) to a SQL Server, but cannot use the DTS Wizard.

 

[vongrunt]

How about linked server to ODBC data source? As far as I know, MSDASQL provider can do that...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)