Share ADO Connection between 2 Server Databases

[Jimgarry]

Hi, thanks in advance:
Share a Connection name between two databases.

Hi, thanks in advance. I want to have 2 connections to 2 different sql servers (2 different) computers.

I have a table on the first server that tells me where the database I need to access is. I have created 2 connections one to each sql server. I have in a table the location and server of each database I need to use. I have attempted to point my program to the correct connection by an if statement

Option Explicit
Public cnV3 As New ADODB.Connection
Public cnV4 As New ADODB.Connection
Public cnNew As New ADODB.Connection

Public sub fillInvoiceList(strClient as string, strServer as string)

Dim cmdObj As New ADODB.Command
cnNew = ""
If Trim(UCase(strServer)) = "ECISQLV4" Then
cnNew = cnV4
Else
cnNew = cnV3
End If

When I run this the cnNew connection = the connection for the correct server.

This is some of the SQL I am attempting to run:

Strsql = “strSql = "select rowid, code, companycd, invoi …. from [" & strClient & "].dbo.utl_invoices where rowid > ('" & lngLastInvoice & "') and invoiceDate between ('" & dat_StartDT & "') and ('" & dat_EndDT & "') order by rowid "

With RSExt
If .State Then .Close
.CursorLocation = adUseClient
.Open strSql, cnNew, adOpenStatic, adLockBatchOptimistic
.ActiveConnection = Nothin
End With

The error this produces is
Run-time Error ‘3709’;
The connection cannot be use to perform this operation. It is either closed or invalid in this context.

When I change the cnNew to be straight cnv4 it works just fine. I need to have this connection go between cnv3 and cnv4 depending on the client .

Any suggestions ?

Thanks again
Jim

 

[nicsin]

In order to use the ADO connection you have to open it. To do so, you have to use a connection string, which you don't include in your post. Instead of creating 3 connection objects, I would check for the client and create the appropriate connection once. Example for ODBC:

Dim connectionString as String
Dim con as ADODB.Connection
set con = New ADODB.Connection
connectionString = ""
If Trim(UCase(strServer)) = "ECISQLV4" Then
    connectionString = "DSN=mySystemDSN4;" & _ 
                       "Uid=myUsername4;" & _ 
                       "Pwd=myPassword4"
Else
    connectionString = "DSN=mySystemDSN3;" & _ 
                       "Uid=myUsername3;" & _ 
                       "Pwd=myPassword3"
End If

con.Open connectionString

strSql = "select..."

etc...

 

[Jimgarry]

Sorry for not getting back sooner, got on other projects

Im using a modified version of what you posted. Thanks for your assistance

Jim