Ok, here are the rules about scope and lifetime. If you declare a variable inside a procedure, you can only see it in the procedure and it is gone when the procedure ends. In the case of the above code, that means that your cmd and prm variables will only be visible in xxx, and the instances will be destroyed when the sub ends.
If you declare a variable in the general declarations section, it will be visible throughout the code window that it's declared in if you make it private (use dim or private), and visible throughout the application if you make it public.
Personally, I would declare a connection in the ThisWorkbook code window, as public. That way, you can always reference it. I would then instantiate it in Workbook_Open (or call SQL_Login from there), and also set the properties there and all. I wouldn't open it until I needed it, though. Keep in mind that open connections are expensive in terms of resources used. So:
Code:
'In the ThisWorkBook code window
Public cnn As ADODB.Connection
Public Sub Workbook_Open()
SQL_Login
End Sub
Private Sub SQL_Login()
'If you're only going to call this from the same code window, it doesn't need to be public
Set cnn = New ADODB.Connection
With cnn
.Provider = ("SQLOLEDB")
.Properties("Data Source") = "dbserver"
.Properties("Initial Catalog") = "database"
.Properties("Prompt") = adPromptComplete
.Properties("Persist Security Info") = True
'.Open 'Don't open it here
End With
End Sub
'In whichever place you need to work with data
Private sub xxx
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
' Set cnn = New ADODB.Connection 'Already done
Set cmd = New ADODB.Command
cnn.Open 'Don't need a connectionstring here
Set cmd.ActiveConnection = cnn
end sub
Make your procedures private unless you need to call them from a different code window from the one they are in. Now, if you want to do this as a function, here's what you do:
Code:
'Note that I have removed the cnn declaration.
'Put this function in the ThisWorkbook window.
Public Function GetConnection() as ADODB.Connection
Dim cnn as ADODB.Connection
Set cnn = New ADODB.Connection
With cnn
.Provider = ("SQLOLEDB")
.Properties("Data Source") = "dbserver"
.Properties("Initial Catalog") = "database"
.Properties("Prompt") = adPromptComplete
.Properties("Persist Security Info") = True
End With
Set GetConnection = cnn
End Sub
'And this sub wherever.
Private sub xxx
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim cn as ADODB.Connection
Set cn = GetConnection()
cn.Open
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = cn
end sub
This last gives you one point control over how you define your connection. Note that in this last, your connection object will only live for the duration of xxx.
By the way, it's generally considered best practice to avoid setting properties on ADO objects when you can avoid it. The more state an object is carrying, the more overhead it represents. So, most of the ADO commands support the properties, but also support "statelessness", where the state of the object is limited to the method call needing it. So, for example:
Code:
'instantiate cn as a connection
cn.open "Provider=SQLOLEDB;Data Source=mySource;Initial Catalog=myDb;Persist Security Info=True"
Now, I don't know about the Prompt property, if you can do it this way, so I left it out.
Same point with the recordset object. The Open method is heavily overloaded, meaning (in this case) that a lot of the arguments are optional. The syntax is
Code:
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
Note that every argument but Options can be set as a property of the recordset. It's generally better, then, not to do that, but to pass values to the open method:
Code:
mhRs.Open "select * from millionaires", cn, adOpenStatic, adLockBatchOptimistic
Set cn = Nothing
The above creates a disconnected recordset, by the way.
HTH
Bob
