How to make an ADO Connection Public

[April15Hater]

Hi guys!

I seem to be having trouble with ADO connections. I want the connection to work on a public level so all of my functions and subs can use the same connection, without having to keep redefining and opening it the same connection (I also get an authentication error whenever I try this). What is the proper way to publicly define an ADO connection so that any other sub or function that I decide to call, can reference that single ADO connection?

Thanks,

Joe

 

[pbaldy]

You'd need to create it as a public variable in a standard module.

 

[April15Hater]

Wow...that was really easy...Thanks Paul!

 

[pbaldy]

No problem Joe. I use a public variable and public open/close functions in a number of apps.

 

[April15Hater]

Funny thing is that I use public variables all the time, but they are strings. I guess it just never dawned on me that I could use it for an ADODB.Connection as well. Plus I never had to work with a connection before, I always used currentproject.connection. Now I'm pulling stuff into excel; so besides learning a new 'dialect', the connection adds another layer of difficulty.

 

[LPurvis]

If I might make a suggestion...

There are those who dislike public (global) variables.
(The argument cited that they don't recover from unhandles errors, apart from not being entirely accurate - they don't recover from the code ending, isn't a big consideration personally - especially given a lot of runtime installations).
I think they're fine and dandy used in moderation and with good reason (as opposed to lazy coding ;-).
However with public object variables I feel a little differently.
If you refer to a public scalar variable, say a string, and it's not been initialised yet (we'll not refer to the code ending pulava)... you can handle that "Whoa - the string is zero length - don't show it in a control or whatever"... no big deal.

In this, near perfect example, if a connection hasn't been established or has been dropped, then using it directly will simply cause whatever method you're using it in to fail - and you drop into that procedure's error handling.
You can check the connection before attempting to use it of course.
But that, IMNSHO, makes for tiresome code.

Instead of
rst.Open "SELECT * FROM Table", pcnn, adOpenKeyset, adLockOptimistic

you have
If pcnn Is Nothing Then
Call MyConnectionOpeningProcedure
End If
rst.Open "SELECT * FROM tblName WHERE ID = 1", pcnn, adOpenKeyset, adLockOptimistic

every single time you're going to use it??
Snore!!!! Zzzzzzzzzz.

However...
If instead of making your variable public, you leave it at module level and povide access to it through dedicated functions (or even a single function with dedicated parameter options if you're so inclined) then you can handle all that within the call.

There's equivalent DAO examples obviously (for example at UA here) but your ADO version could be something as simple as

Private mcnn as ADODB.Connection
 
Function fGetConn() As ADODB.Connection
On Error Resume Next
 
    If mcnn Is Nothing Or mcnn.Status = 0 Then
        Set mcnn = New ADODB.Connection
        mcnn.Open fConnectionStr 'Which returns whatever connection string you use
    End If
    Set fGetConn = mcnn
 
End Function
 
Sub CloseConn()
 
    mcnn.Close
    Set mcnn = Nothing
 
End Sub

You can can then either set your local objects to this public function
e.g.

Dim cnn as ADODB.Connection
Dim rst As ADODB.Recordset
 
Set cnn = fGetConn
Set rst = cnn.Execute("SELECT * FROM tblName WHERE ID = 1")

or you can just use the thing directly (it is a valid, persisted, object that's returned from the function - i.e. it's always the same object).
e.g.

Dim rst As ADODB.Recordset
Set rst = fGetConn.Execute("SELECT * FROM tblName WHERE ID = 1")

It's effectively a public object to be used - but is a function and hence self checking/healing.
You can take this a stage further and derive a whole class around it and make sure that this class is persisted and if not instantiate a new one in the same way - and offer a range of methods from that class.
For example - there's a class for establishing a connection here which could be persisted in a similar way. (That class has an option for automatically closing the connection when the public object drops out of scope - which is a potentially useful piece of cleanup).

Cheers!

 

[April15Hater]

Wow, that is really helpful! I actually kinda structured it like that just without the error handling and I made the function public:

Public cnProductionDatabase As ADODB.Connection

Public Function OpenADOConnection()
Set cnProductionDatabase = New ADODB.Connection
'DEFINE LOCATION OF THE DATABASE
dbpath = "J:\interfaces\Source Code\CCE Services Inc - Full Front End.mdb"
'Open the DB connection
cnProductionDatabase.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source=" & dbpath & "; Jet OLEDB:System database=J:\Security\SHAREWORKGROUP.MDW; " & _
    "user ID = jsolomon; password = ***;"
End Function

With that in mind, I do have everything running out of the same module. However, I'll warn you that I'm not 100% up to speed on how modules, classes, etc. exactly work. My knowledge there is limited to knowing that public variables are still recognized between functions. Do you know of any good resources where I can find out the uses of each?

Thanks!

Joe

 

[LPurvis]

I don't know of any single definitive online resource that explains everything.
Just bits you'll gather from here and there.
A good book should see you right.

The code you have isn't really at all what I was suggesting though.
Yes you use a public function to initiate the connection - but that's not what I was getting at (you'd require what you have as a bare minimum - you need to establish the connection somehow).

What I was discussing is not making the connection object itself public.
The function does all the work and handling.

Have another read, see if it makes sense and if not shout out.

Cheers.

 

[Banana]

Yeah, the key is in those lines:

Static cnn As ADODB.Connection

If cnn Is Nothing Then
   'Create a new connection
End If

This way, the function behaves almost exactly like this line:

Dim cnn As New ADODB.Connection

Except for the fact that you get to specify the details of the connection before you call.

And as a matter of personal opinion, I usually put it in a property procedures rather than function. Syntactically, there is no difference between either two. It's simply because I mentally associate properties and functions differently and it's a aid to myself to reinforce the distinction. That is, properties are something that "belongs" to the project and is characteristic & essential to the project's working correctly. Functions, to me, performs calculations or operations upon particulars and give it back. But that's just my personal preference and organization; none will be worse for wear if it's a function or property.

Anyway, I'm rambling now, but the core point is this: You need to have a permanent variable (either a private, module level variable or a static procedure-level variable) that will be self-checking, which is why we are doing a If ... Is Nothing Then ... Create It. It's certainly cheaper than creating new object everytime we need it and even in event of an error that blows away the object reference, the Nothing will evalulate to true and create it, thus repairing it just in time.

 

[LPurvis]

I assume when you say "Yeah, the key is in those lines" you're then talking about part of your own procedure Banana?

I'm don't know if what Banana has said has helped clarify things for you yet Joe, he's essentially describing a different implementation of a similar practice. But still not what you already had.

Banana, you're talking about using a class which describes/maintains your connection?
(Refering to property procedures). i.e. it's a property of what?
You're maintaining a Static object within some such procedure.
So do you include an alternative means of entry to that procedure (say by an optional parameter) to close that connection and destroy it?

The Function, for me, isn't about what it returns - but that it returns a value or object.
Even if using a class - I'd likely use a function to return the class object which in turn handled the connection.
i.e.
fReturnPublicClassObject.ClassMethod "Parameter"
to allow self healing at that level too (without setting up of a public class object).

Not completely sure which scenario you were describing.
Hence I can't be sure of explaining it to the OP.

Cheers.

 

[Banana]

Actually, I thought my implementation was same as what you proposed... Essentially a function in a standard module:

Public Function MyConn() As ADODB.Connection

Static cnn As ADODB.Connection

If cnn Is Nothing Then
   Set cnn = New ADODB.Connection
   <set up other stuff...>
End If

Set MyConn = cnn

End Function

As for closing the connection, you can do this:

Private Sub foo()

Dim c As ADODB.Connection

Set c = MyConn()

c.Close

Set c = Nothing

End Sub

Like the New keyword, this test will fail everytime:

?MyConn() Is Nothing

It doesn't actually dispose of the cnn reference, though the cnn is now closed. As long as all code all over the project point to the same cnn variable, it really doesn't matter any more whether we've got one too many connection because it's still the same reference. In my case, I want connection to be always available whenever I call it, without worrying about instantiating it or not, and because all my code point to the same procedure, there's no duplicates being instantiated, so there's always either 0 or 1 connection in an application's lifetime.

As for the class/property, no. It's just a property procedure inside a standard module. E.g. It's a property of that module or more generally, a property of the project itself. And it should be pointed out that property, like functions, can return a object or primitive data types. As I asserted before, there is really no syntactic difference between a property procedure and function, with one minor exception of being able to separate the action of getting and modifying the value. In that context, Property Get is read-only, just much like a Function, except when either are passing objects, which are just reference, so we're actually accessing the same object (e.g. the 'c' connection object is referencing the exact same connection as 'cnn' is referencing, even though the 'cnn' is well out of scope). Thus we can invoke close method and all references to the same connection will be now closed.

The class does offer a means to destroy the variable with the Class_Terminate event, but in such case, I usually think it's more appropriate for where multiple ADODB connections are needed, and that's not so common.

Just my $0.02. (Sorry, I know it's worthless- can you blame me for USD's shrinking purchasing power? )

 

[April15Hater]

I had a feeling I was missing something there, and I'm not 100% I quite have it. So from what I read, it keeps the connection contained inside of a Function therefore any error is handled within that function. So by adding the error handling lines of code, I can keep the connection all contained within one function.

I really haven't addressed error handling to much which may be why I didn't catch it the first time around. I just don't know a whole heck of a lot about it and I haven't really researched it much because I don't quite understand why one would want to resume next on an error rather than stop the code and know that an error exists.

 

[Banana]

Just so we're not conflating two themes going on here-

One issue we have to deal with is the fact that whenever we have an unhandled error; regardless of error's origin and cause, it has potential to blow away the connection object.

There may be other cases where the object get de-referenced, but I'm not exactly too clear *exactly* what the cases are. Maybe LPurvis knows more, but that's the problem the evaluation "Is Nothing" is trying to address. In such event where we don't know that our object has been dereferenced due to an error that had nothing to do with the connection, the function repairs the object just in time so we don't get unexpected error saying there's no such connection object.

With the "Resume Next" thing inside function, it's dealing specifically with the errors created by the action of instantiating a connection object. To be honest, I'm not 100% sure why one would resume next for this context. I would definitely do a Resume Next upon a function where I was closing the connection, because I really don't care if it didn't succeed in that context.

However, if you want, you could just put in a standard error handling with a special messagebox notifying there was a problem with connecting or something to that effect. In my last project, I believe I made it so that if the application couldn't connect to the backend at all, it'd display a messagebox and immediately quit, the reasoning being that if it can't connect, it's useless at the moment. (Mind you, I had a backdoor for the developer- else I'd never get far trying to fix it!)

 

[LPurvis]

Behind on posts a bit.
Will try to catch up.

Banana, yes I'm familiar with the distinction and implementations of Properties.
I agree there are uses for Properties (not just at custom class level where they are, naturally, hugely important).
But not needing to set the connection object - a returning function is just fine for returning the object. If it were required, I agree a property would likely make for neater coding than a distinctly named sub for assigning it.

It's primarily the use of the static object that I'd rather avoid.
(Not that I never use them, though generally it's for convenient, transportable example code - e.g. for forum posting

Otherwise no, your code doesn't particularly differ in concept.
It's all about that self healing and single location.


Fundamentally, the example I suggested is air code based on real concepts (as I'm sure yours is) - not based on actual full procedures of mine (attempting to demonstrate the core concept only rather than full live procedures).
None the less it was written with reason to my apparent madness.
The Resume Next error handling was to allow for failing due to checking

If mcnn Is Nothing Or mcnn.Status = 0 Then

If the connection object is indeed nothing - then mcnn.Status will raise a runtime (object variable) error.
We could handle that of course and resume into the procedure to carry out the opening (but then this is aircode... :-s)
That explains the Resume Next use hopefully.

Equally we could handle this by splitting out into separate checks including

If mcnn Is Not Nothing Then
If mcnn.Status = 0 Then
...

Again - I didn't go down this route, wanting to keep this demonstration as direct as possible for readers.


And on to reasons for failure.
Dropping of the connection object - that's got to be comparatively unlikely.
Obviously stopped code (i.e. ended, as in after an unhandled error) will kill the object be it module level or in procedure static - but that's hopefully a very unlikely event in an application (if not impossible, for example in a runtime install).

Other factors could come in to play, but for me - far more likely is the potential that the connection simply closes.
The Connection object itself will remain a valid connection object until some VBA issue causes otherwise (or we issue a Set mcnn = Nothing).
However server or network problems could easily see the open connection dropped - in which case a check such as
If cnn Is Nothing Then
will happily pass the test as the object variable is typed as a connection and has been instantiated, but which just happens to be closed - exactly as it is after
Set mcnn = New ADODB.Connection
but before
mcnn.Open ....

Hence my check for the State of the connection to determine if it's still open.
Is this foolproof either? Gawd no. Is anything?
Is it more comprehensive? How can it not be.
But what's offered is deliberately conceptual.

Anyway - I suspect Mike wants some more walking through the concept.
Since he seems to be following "I can keep the connection all contained within one function" then I'll leave you to it.

 

[Banana]

Hey, LPurvis,

Thanks for the exposition on Resume Next and the problem of ADO connection object being closed and thus needing to check its state. I actually didn't think about the possibility that it could get closed.