Passing a Connection as a Paremeter 1

[ndalli]

Hi,

I am trying to pass a connection as a parameter to a function. The connection is for an MSACCESS database.

I am getting an error stating that the connection string is either empty or invalid (although the connection works fine when used normally i.e. not passed as a parameter).

Does anyone know if it is possible to send a connection string as a parameter?

Thanks in advance

 

[tsh73]

ndalli,

You just pass a string - and that definitelly could be done.
So there is smth in your code. Post a portion so people could give you some advice.

 

[ndalli]

The code is the following (part of it actually ... )

Dim connDatabase AS New ADODB.Connection
Dim strCnn AS String
Dim strExtractedValue as String


Private Sub Form_Load()
strCnn = ".... a normal connection string"

connDatabase.open strCnn


'The following code is in a separate DLL
strExtractedValue = ExtractValues(connDatabase, KeyFieldValue)
End Sub


===== The following is the function ExtractValues in the DLL
Public Function ExtractValues(ConnectionToOpen as Connection, KeyFieldValue AS String)
Dim connDatabase2 AS New ADODB.Connection
dim rsRecordSet AS New ADODB.Recordset
Dim strSQLStatement AS String

Set connDatabase2 = New Connection

connDatabase2 = ConnectionToOpen
strSQLStatement = "SELECT UniqueNo FROM Table WHERE KeyField = '" & KeyFieldValue & "';"

rsRecordSet.Open strSQLStatement, connDatabase2, adOpenKeyset
THIS IS WHERE I GET THE ERROR

End Function

 

[CCLINT]

er, where and how is the dll located?

You should/will need to pass the recordset object as well, or create the connection in the dll.
The recordset and connection objects must be created on the same thread...

 

[CCLINT]

Best is to create and open the connection and objects in the ActiveX exe, and then disconnect the recordset object from the connection, close the connection and then pass the recordset back.

 

[ndalli]

I was trying to avoid the opening and closing of connections, since I would be needing to call the function for a number of times in the application and would surely effect the performance.

 

[CCLINT]

Opening and closing the connection the way mentioned will actually have better performance/less overhead if the connected data is being marshalled.

If the dll is in-process and located on the client, then of course, go ahead and use one connection.

 

[ndalli]

Yes the dll is "in-process and located on the client".

May I then please ask you from where can I get information on how to create "marshalled connected data". (As you may see I'm quite new to this stuff)

Many thanks

 

[CCLINT]

Ok. I looked closer at where the error may be (note the lines commented out, new lines, and the set statement):

Public Function ExtractValues(ConnectionToOpen as Connection, KeyFieldValue AS String)
'Dim connDatabase2 AS New ADODB.Connection
Dim connDatabase2 AS ADODB.Connection
dim rsRecordSet AS New ADODB.Recordset
Dim strSQLStatement AS String

'Set connDatabase2 = New Connection

Set connDatabase2 = ConnectionToOpen

strSQLStatement = "SELECT UniqueNo FROM Table WHERE KeyField = '" & KeyFieldValue & "';"

rsRecordSet.Open strSQLStatement, connDatabase2, adOpenKeyset


Or just:

Public Function ExtractValues(ConnectionToOpen as Connection, KeyFieldValue AS String)
'Dim connDatabase2 AS New ADODB.Connection
dim rsRecordSet AS New ADODB.Recordset
Dim strSQLStatement AS String

'Set connDatabase2 = New Connection

'Set connDatabase2 = ConnectionToOpen

strSQLStatement = "SELECT UniqueNo FROM Table WHERE KeyField = '" & KeyFieldValue & "';"

rsRecordSet.Open strSQLStatement, ConnectionToOpen, adOpenKeyset

 

[ndalli]

Thanks CCLINT, that's the solution