ADO conn to two diff. backend db

[Debbie]

Hello,
I have A2003 using a front end and back end configuration which works just
fine. However, I have a need to pull data from another back end database.
Essentially, the tables are linked from 2 seperate back end databases.

In a procedure, I run two different queries. One goes against one backend
database#1 and the other goes against backend database#2.

The query against backend#1 works fine. When a run a query against
backend#2, I get a message saying the table from the backend#2 can't be
found. A sample of my code is below. :

Dim OConn As ADODB.Connection
Dim SQL As String
Dim SQLCmd As ADODB.Command
Dim MasterOConn As ADODB.Connection

Set OConn = New ADODB.Connection
Set OConn = OpenConn()
Set MasterOConn = New ADODB.Connection
Set MasterOConn = OpenConnMaster()

Set SQLCmd = New ADODB.Command
SQLCmd.ActiveConnection = OConn
.....

SQL = "INSERT INTO LogConsultation ......
SQLCmd.CommandText = SQL
SQLCmd.Execute
SQL = ""

****The query below produces the error saying Consulation1 could not be
found.
'Refresh the rows
SQL = "INSERT INTO Consultation ...
SQL = SQL & " SELECT Consultation1.ClientID, ....
SQL = SQL & " FROM Consultation1 WHERE ClientId = " &
rstClientList!ClientID

SQLCmd.ActiveConnection = MasterOConn
SQLCmd.CommandText = SQL
SQLCmd.Execute
SQL = ""

I am using the same connection and the same SQL command with the 2nd query
as with the first. Is this allowed? I checked the connection statement and
it is fine. Does anyone have any ideas? Thanks so much.
Debbie

 

[Alex Dybenko]

Hi,
suggest that you create 2 ado connections for each backend and then run
execute method for corresponding connection

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Debbie]

Alex,
Thanks for your reply. I believe that's what I did, whether I did it right
is another story. 'MasterOConn' is another function which creates another
connection. My thought was, can I use the same SQLCmd command function I've
defined for both connections? I did try defining two different command
objects but I got the same error. Any ideas? Thanks,
Debbie

 

[Alex Dybenko]

Hi Debbie,
try to debug your code, and look at .Connectionstring property of each
connection object - will it show the right path to your backend?


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Debbie]

Alex,
Yes I did and it does show the right path. I did some more troubleshooting
today and I discovered that I cannot have more than one connection OPEN at
the same time. Because I was using a table from 2 different database at the
same time, I had two connections open. Access wouldn't allow it. If I had
one open, closed it and then opened the other, then it would be ok. The only
problem is I need both open at the same time so I will have to design another
way to accomplish this. I have a good idea of how to do it, it will just be
alot more work! Thanks very much for your help and quick answers. I do
appreciate it!
Debbie

 

[RoyVidar]

Alex,
Yes I did and it does show the right path. I did some more
troubleshooting today and I discovered that I cannot have more than
one connection OPEN at the same time. Because I was using a table
from 2 different database at the same time, I had two connections
open. Access wouldn't allow it. If I had one open, closed it and
then opened the other, then it would be ok. The only problem is I
need both open at the same time so I will have to design another way
to accomplish this. I have a good idea of how to do it, it will just
be alot more work! Thanks very much for your help and quick
answers. I do appreciate it! Debbie

I don't understand what you are saying here.

In your initial post, you say that the tables in be1/be2 are linked.
If that is the case, you don't need any separate connections to those,
but can use currentproject.connection, as they should be present as
linked tables within your current database.

If you need to open separate connections to each of them, then just
do so - I don't know of any limitations of such. At least not with
only two connections.

Since you are using dynamic SQL, there's no need to use a command
object. I usually only use command objects when I wish to utilize
the parameters collection to resolve parameters.

When I use the command object, I will usually use the Set statement
when assigning .ActiveConnection

With cmd
Set .ActiveConnection = cn
....

Just for the fun of it, create three copies of a simple test database,
all containing the same table - in the below sample, there's the table
table1 containing the fields id (Autonumber) and test (test), the
extra databases are named test1.mdb and test2.mdb, and resides in the
same directory as the current database.

Then from one of them, try the following. If this runs, then you are
allowed to have two separate connections open.

Sub testmoreconnections()

Dim cn As ADODB.Connection
Dim cn1 As ADODB.Connection
Dim cn2 As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSql As String
Dim strCn As String

' instantiating and opening connections
Set cn = CurrentProject.Connection

strCn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=" & CurrentProject.Path & "\test"

Set cn1 = New ADODB.Connection
cn1.ConnectionString = strCn & "1.mdb"
cn1.Open

Set cn2 = New ADODB.Connection
cn2.ConnectionString = strCn & "2.mdb"
cn2.Open

' fetch one record from each and print to immediate pane
strSql = "SELECT * FROM table1 WHERE id = 1"

Set rs = cn.Execute(strSql, adCmdText)
Debug.Print "Current database"
Debug.Print rs.GetString

Set rs = cn1.Execute(strSql, adCmdText)
Debug.Print
Debug.Print "test1.mdb"
Debug.Print rs.GetString

Set rs = cn2.Execute(strSql, adCmdText)
Debug.Print
Debug.Print "test2.mdb"
Debug.Print rs.GetString

' update the test field in all tables
strSql = "update table1 SET test = 'blah' " & _
"WHERE id = 1"

cn.Execute strSql, , adCmdText + adExecuteNoRecords
cn1.Execute strSql, , adCmdText + adExecuteNoRecords
cn2.Execute strSql, , adCmdText + adExecuteNoRecords

' fetch one record from each and print to immediate pane
strSql = "SELECT * FROM table1 WHERE id = 1"

Set rs = cn.Execute(strSql, adCmdText)
Debug.Print "Current database"
Debug.Print rs.GetString

Set rs = cn1.Execute(strSql, adCmdText)
Debug.Print
Debug.Print "test1.mdb"
Debug.Print rs.GetString

Set rs = cn2.Execute(strSql, adCmdText)
Debug.Print
Debug.Print "test2.mdb"
Debug.Print rs.GetString

End Sub