Excel VBA and Microsoft SQL Code 1

[benaround2]

I was using the following code on a MS Excel 2003 spreadsheet to do initial logon to Microsoft SQL off a user form command box:

Public Sub SQL_Login()
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
.Open
End With
End Sub

I need code to be able to use this setup connection in later SQL queries without a relogin.

Any help is greatly appreciated.

 

[BobRodes]

The problem is that your cnn variable is only in scope when your sub is running. You need to declare your variable in the appropriate General Declarations section of your code. If for some reason you can't do this (let's say that you're going to make an ActiveX DLL with SQL_Login as a method of it), then you can turn your sub into a function and return your cnn connection as its return value.

HTH

Bob

 

[benaround2]

Bob, can you give a quicke example of what you mean?

I understand that you probably want code like :
Public cnn As ADODB.Connection
Public Function SQL_Login()
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
End With
End Function
public sub xxx
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
cnn.Open ?????
Set cmd.ActiveConnection = ????
end sub

Thanks again for your help

 

[BobRodes]

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:

'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:

'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:

'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

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:

mhRs.Open "select * from millionaires", cn, adOpenStatic, adLockBatchOptimistic
Set cn = Nothing

The above creates a disconnected recordset, by the way.

HTH

Bob

 

[benaround2]

I cannot thank you enough

 

[rmikesmith]

A small clarification regarding variable scope: Declaring a variable Public in the ThisWorkbook class module doesn't actually create a public, or global variable. It cannot be accessed by code outside of ThisWorkbook, including that in a standard code module or userform module. Attempting to reference such a variable will give a "variable not defined" compiler error. The same is true for declaring variables in a userform module. To create a real global variable, declare it using the Public keyword in a standard code module.

Regards,
Mike

 

[TonyJollans]

A slight further clarification on this ..

A public variable declared in the ThisWorkbook class module becomes a (public) property of the Workbook and can be accessed from anywhere provided it is qualified. The same is true of public variables declared in UserForms ..

[blue][tt] ThisWorkbook.MyPublicVariable[/tt][/blue] or ..
[blue][tt] Workbooks(1).MyPublicVariable[/tt][/blue] (assuming the Workbook is the first) etc.

Similarly ..

[blue][tt] UserForm1.MyPublicVariable[/tt][/blue] is good.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[rmikesmith]

Thanks Tony,

I'm sure I knew that at some point!


Regards,
Mike

 

[BobRodes]

Thanks for those clarifications guys, I'm more of a VB person than a VBA. And you are most welcome Ben, in fact I needed a bit of help killing some time on Friday afternoon and your question fit the bill exactly.

Bob