Reference to adodb.connection (or objects in general)

[ffyring]

Hi,

I am writing a VBA program that access several different databases. I
am now trying to write a module where I handle all database access. My
first problem is how to open the databases. My idea was to have a
function return a reference to a connection object, and open it if
necessary. Here is an excerpt of my code:

---------------------
dim db1 as adodb.connection, db2 as adodb.connection

function open_database(db as integer) as adodb.connection
dim dsn as string
dim conn as adodb.connection

select case db
case 1
set conn = db1
dsn = "DSN1"
case 2
set conn = db2
dsn = db2
end select

'create new database object if necessary
if conn is nothing then set conn = new adodb.connection
'open the database if necessary
if conn.state = adstateclosed then conn.open

set open_database = conn
end function

---------------------------
I would hope that "set conn=db1" would create a reference to db1, but
it seems to create a copy of the object: the db1 object is not the
same as the conn, so I end up creating and opening a new connection on
every call of my function.

Any ideas how to rewrite so I keep the database open?

//Fredrik

 

[George Nicholson]

*Air Code*:

Case 1
If db1 is Nothing Then
'Create new connection
set conn = db1
dsn = "DSN1"
Else
' Use existing connection
Set open_database = db1
Exit Function
End If

HTH,