Passing ADO objects to functions

[everymn]

This is kind of a noob question but I'm having problems passing
objects to functions. Is this not allowed? I'm getting a type
mismatch error when I call Connect()

Thank You

Option Compare Database
Option Explicit
Dim connection1 As ADODB.Connection

Private Sub Command0_Click()
Set connection1 = New ADODB.Connection
Connect (connection1)
connection1.Close
End Sub

Public Function Connect(ByRef conn As ADODB.Connection)
Dim strConnect$
strConnect$ = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;" &
_
"DATABASE=odbc_insert_test;USER=root;PASSWORD=mypass;OPTION=3;"
conn.Open strConnect$
End Function

 

[Robert Morley]

Your function is fine, it's how you're calling it that's the problem. This
is one of the most common VB6/VBA errors for "noobs", so don't worry, you're
not alone.

You want either:

Call Connect(connection1)

or just

Connect connection1

Both work exactly the same, as far as I know, so use whichever you prefer.
In my experience, the second form seems to be more common, probably because
people don't like typing extra characters.

What happens when you do it the way you did is that VB believes that it
should pass connection1 "by value", instead of passing the object itself.
The "value" of connection1 is it's default property, ConnectionString, so
it's like you had used "Connect connection1.ConnectionString".


Rob

 

[everymn]

Great description thanks. It's a pain going from C languages to VB

 

[Robert Morley]

Yeah, but it's even worse going the other way around in my experience!

 

[Douglas J. Steele]

FWIW as the function does not change conn to 'point' at another object,
I'd expect the declaration to be ByVal.

The line

conn.Open strConnect$

is opening the connection. If you had ByVal, the connection wouldn't be open
in the calling routine, would it?

Realistically, that seems like a very pointless function.

 

[Brendan Reynolds]

My memory is telling me that objects are always passed ByRef. I don't have
documentation to hand, but this quick test seems to indicate that my memory
may be correct ...

Public Sub TestObjByRef(ByRef cm As ADODB.Command)
cm.CommandText = "SELECT * FROM tblTest"
End Sub

Public Sub TestObjByVal(ByVal cm As ADODB.Command)
cm.CommandText = "SELECT * FROM tblTest"
End Sub

Public Sub TestSub()

Dim cm As ADODB.Command

Set cm = New ADODB.Command
TestObjByRef cm
Debug.Print "ByRef: " & cm.CommandText
Set cm = New ADODB.Command
TestObjByVal cm
Debug.Print "ByVal: " & cm.CommandText

End Sub

testsub
ByRef: SELECT * FROM tblTest
ByVal: SELECT * FROM tblTest