0

I'm trying to hide as much as possible within the ADO sections of code in my Excel-vba routines by making scopes as strict as possible.

Module 1:

Sub control()

Const strConn As String = _
    "PROVIDER=SQLOLEDB.1;" & _
    "PASS******=xxxxxxxxxxxxxxxxx;" & _
    "PERSIST SECURITY INFO=True;" & _
    "USER ID=xxxxxxxxxxxxxxxxx;" & _
    "INITIAL CATALOG=xxxxxxxxxxxxxxxxx;" & _
    "DATA SOURCE=xxxxxxxxxxxxxxxxx;" & _
    "USE PROCEDURE FOR PREPARE=1;" & _
    "AUTO TRANSLATE=True;" & _
    "CONNECT TIMEOUT=0;" & _
    "COMMAND TIMEMOUT=0" & _
    "PACKET SIZE=4096;" & _
    "USE ENCRYPTION FOR DATA=False;" & _
    "TAG WITH COLUMN COLLATION WHEN POSSIBLE=False"

Dim c As ADODB.Connection
Dim r As ADODB.Recordset

Set c = New ADODB.Connection
c.ConnectionTimeout = 0
c.Open strConn

Set r = New ADODB.Recordset
r.ActiveConnection = c

Call someADOproc(r)

End Sub

Module 2:

Sub someADOproc(ByRef ar As Object)

ar.Open _
    "SELECT top 1 Operator " & _
    "FROM   xxxxxxxxx.dbo.xxxxxxxxxxxx "

MsgBox ar.Fields(0).Value

End Sub

The strict scope of the variables in the routine control makes me surprised that ByRef actually works and that the variable is available within the routine someADOproc that is located in a different module? I was expecting this to fail.

ByVal also works - this is as expected as it is passing in a copy of the recordset r ?

What is missing in my understanding of these concepts?

Should I use ByVal or ByRef?

Community
  • 1
  • 1
whytheq
  • 34,466
  • 65
  • 172
  • 267
  • 1
    No, it isn't a copy. http://stackoverflow.com/q/4908692/11683, http://stackoverflow.com/q/20145564/11683, http://stackoverflow.com/q/4383167/11683. The "strict scope" bit is also confusing - there isn't one. I believe in any language you can pass your method-local variable to another method as an argument - because *you* decide what to provide as an argument; no one can steal your private variable unless you permit it. If you don't want the code you're calling to be able to examine recordset's `Connection`, then populate the recordset in advance and clear the connection before passing it. – GSerg Jul 24 '14 at 12:42
  • @GSerg thanks I'll experiment with clearing the connection as well. Although I believe that ByVal is effectively passing a copy - if the original is left unchanged but the new param/arg can be changed independently then surely it is effectively a copy. – whytheq Jul 24 '14 at 13:50
  • The variable that in your procedure holds the reference to the object (`r`) cannot be changed by a `ByVal` callee, but the object itself can. The called code cannot store a different recordset in your `r`, but it can fiddle with your `r`, changing its properties in any way. No recordset copying is happening, copying of the reference to the recordset is. – GSerg Jul 24 '14 at 14:33
  • @GSerg In this context I do not need to pass anything back from `someADOproc` and I do not need to change the records in `r` so which should I use `ByRef` or `ByVal`? Should I just use `ByVal` for the reason that `r` does not need to change ? – whytheq Jul 24 '14 at 14:45
  • You only use `byref` when you want your local variable reassigned with a different value by the code you're calling. In all other cases you use `byval`. – GSerg Jul 24 '14 at 14:49
  • @GSerg Ok - so I'm guessing if it was the case that I wanted the local variable to be reassigned values by `someADOproc` then this procs signature would need to be `someADOproc(ByRef r As ADODB.Recordset)` ? – whytheq Jul 24 '14 at 14:56
  • @GSerg I disagree with your statement "No recordset copying is happening, copying of the reference to the recordset is". If we use ByVal then a copy of the variable r is used in the callee procedure ... if it is just a copy of the "reference" then it will be pointing at the same object instance which is seemingly what ByRef does. MSDN article here: (http://msdn.microsoft.com/en-us/library/vstudio/ddck1z30(v=vs.100).aspx)[http://msdn.microsoft.com/en-us/library/vstudio/ddck1z30(v=vs.100).aspx] quote:"In this case, Visual Basic copies the entire data contents of the argument" – whytheq Jul 26 '14 at 11:24
  • Yes, it is just a copy of the reference, and yes, it will be pointing to the same object with `ByVal`. With `ByRef` it will be pointing to the same object too, but with the added ability of affecting your local `r` variable. The behaviour you describe regarding copying happens in C++ - but not in VB6, or VBA, or VB.NET, or C#. – GSerg Jul 26 '14 at 11:29
  • @GSerg please read this MSDN article http://msdn.microsoft.com/en-us/library/vstudio/ddck1z30(v=vs.100).aspx – whytheq Jul 26 '14 at 11:31
  • also this article tests and over very large iterations `ByRef` is faster so suggesting that a user should always use `ByVal` is not necessarily accurate.http://www.jpsoftwaretech.com/which-is-faster-byval-or-byref/ – whytheq Jul 26 '14 at 11:34
  • No, you read [this MSDN article](http://msdn.microsoft.com/en-us/library/vstudio/t63sy5hs(v=vs.100).aspx) please :) The copying you are talking about happens to value types only. A recordset is a reference type. – GSerg Jul 26 '14 at 11:35
  • @GSerg that article is talking about data types not the use of ByRef and ByVal – whytheq Jul 26 '14 at 11:38
  • Okay. Please read [this answer](http://stackoverflow.com/a/4383206/11683), and please run [this program](http://pastebin.com/rPbLaxgx). Other than that, I can't really help you anymore at this point. – GSerg Jul 26 '14 at 11:46
  • @GSerg nice reference - appreciated. I've not really explored the real meanings of ByVal and ByRef before - I think this has been an important bit of reading. – whytheq Jul 26 '14 at 12:27
  • 1
    Just remember, if you clear a recordset's connection, you've *disconnected it* and will no longer be able to use it to write back to the database. – RubberDuck Jul 27 '14 at 17:11

0 Answers0