Opening many public connection objects (using different databases), is it okay?

[dee-u]

It is recommended to use one public connection but what if one has to connect to different databases at once? Is it okay to use two public connection objects? We separated the database for our transaction logs hence I need to have a connection object for it also...

Thanks!

[kaffenils]

It is recommended to use one public connection but what if one has to connect to different databases at once? Is it okay to use two public connection objects? We separated the database for our transaction logs hence I need to have a connection object for it also...

Thanks!

Are the databases on the same server? Is it SQL server by the way?
If they are on the same server you only need one connection object, and you don't take up more resources on the server than necessary.
You can refer to the database objects by using a three-part name, database.owner.objectname, or you can change the database context with the USE function.

[dee-u]

Sadly it's only for Access...

But could you give me details on your suggestion? Don't know how to use "Use" if its applicable with Access...

[kaffenils]

Sadly it's only for Access...

But could you give me details on your suggestion? Don't know how to use "Use" if its applicable with Access...

If it is Access then I think the only solution is to use multiple connection objects. Does Access have transaction logs?

The USE function is a SQL Server command, so it doesn't work for you.

[dee-u]

AFAIK Access doesn't have transaction logs... Thanks, I'll still wait for the recommendation of other experts...

[kaffenils]

AFAIK Access doesn't have transaction logs

Thats what I though, but you wrote this

We separated the database for our transaction logs

in the first post.

[dee-u]

Sorry, its a Transaction Log for our application, not related to the one in SQL Server...

[pnish]

I don't think there's too much of a problem having multiple connections open. We have a similar situation where the data must come from several different databases. Some of our apps can have up to 5 different connections open and it doesn't appear to cause any problems. I guess it all depends how many concurrent users there are.

One way around the problem would be to link the tables so they all appear in the same database, that way you can use a single connection.

[dee-u]

Thanks fellow VBFer's, I think I have no choice but use more than one public connection...

I wouldn't mark this thread as resolved yet, recommendations and suggestion are still welcome...

[si_the_geek]

You can use a single connection, and just specify the database within Access SQL statements, eg:

SELECT table1.field1 FROM [c:\path\database.mdb].table1

(the syntax may not be quite right here!)

However an extra connection object would be more efficient in this case, as you are basically connecting to one database file and then re-routing to another - a direct link would be faster.

If you are trying to create cross-database queries tho, using a variation of the above method will obviously be more efficient than joining two recordsets yourself.