Returning a valid ADODB.Connection Object

[buggseywork]

Hi;

I have a VBA macro that has to access the database at different times while it is doing its work. Every time the connection is requested the login screen will be shown in order for the user to log in and validate the connection. This is the way the AS/400 client access driver operates as far as I know. What I would like to do is to connect to the database and use this connection every time I want to do some work with the database thus eliminating the need to connect every time. Is this possible? Anyone have any suggestions on this?

Here is the connection function I have written and it does produce a valid connection (Pending a good log in of course) but I cannot use it in the Sub it is called from for some reason. I get Object required error. I know I must be using the wrong syntax or something... shouldn't a reference to that object be passed back to the callling sub?

HELP!!!!
here is the connection function I have written

Function GetCon(DataSource As String)

On Error GoTo ErrorHandler

Dim oConn As ADODB.Connection

Dim sConn As String

sConn = "Provider='IBMDA400';Data Source='" & DataSource & "';"

Set oConn = New ADODB.Connection

oConn.Open sConn

' this just checks the sate of the connection...
If GetState(oConn.State) = "adStateClosed" Then
ValidCon = Null
GoTo EndCon
End If

EndCon:

GetCon = oConn

Exit Function ' Exit Sub to avoid error handler.

' Close the connection.
' oConn.Close
' Set oConn = Nothing

ErrorHandler: ' Error-handling routine.
Msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox Msg, vbInformation, "Error"
Resume Next ' Resume execution at same line that caused the error.
End Function

any help wouldbe appreciated...
best regards;
Bugs

 

[PHV]

Have you tried to put this line:
Dim oConn As ADODB.Connection
outside the function body ?
i.e make oConn global.

Hope This Help
PH.

 

[buggseywork]

Nope... I didn't actually... you know efficiency and all that jazz

I didn't wanted to resort to making the thing global but perhaps that is what I will have to do...

thank you for your reply

Bugs

 

[Nelviticus]

You haven't declared a return type for your function - the first line should be:

Function GetCon(DataSource As String) As ADODB.Connection

Does that help?

Nelviticus

 

[buggseywork]

Thank You for your reply...

I get an error now that says "Object Variable or with Block variable not set"

I get this error in the GetCon function on this line
(in the debugger)

GetCon = oConn

Of course, this is the where the function name is assigned the connection object.

I think the syntax you have given me is correct and I admit that I am not that familiar with the syntax of passing objects in functions by reference in VBA or VB.

I have searched high and low and have found nothing on it that has any meat to it.. just your basic passing of integers and what not...

Just FYI...
Here is how I call the function... anything wrong with this?

Dim NewCon As ADODB.Connection

NewCon = GetCon("DSNNAME&quot

Thanks in advance for any help or reference links you may be able to provide...

Regards;
Bugs

 

[Nelviticus]

It should be

[COLOR=red]Set[/color][COLOR=blue] NewCon = GetCon("DSNNAME")[/color]

Nelviticus