Declare database connection variables as global

[anniebai]

In my Access database project, SQL server as backend, there're several functions have this repeated piece of code. I wonder if it's OK to declare the two variables (one for ADODB command, another is for RecordSet) as global? will that cause any potential problems? Thanks very much.

Dim cmd As ADODB.Command, rst As ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection

[anniebai]

And these two lines are redundant everywhere. Is there a better way to work around them? Thanks again.

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection

[ADezii]

@anniebai
Global Variables can be very tricky and problematic. It is very hard to say whether or not you should Declare them as such without seeing the entire code context. What you possibly can do is to Declare a Public Function, and pass to it certain Arguments eliminating some duplication. In the following code segment, you can pass the name of a View to the Function and it will execute it eliminating the need for duplicate Command and Recordset Object Declarations while displaying all Field Names and their Values for each Record. Again, we would have to see the entire code context to see if something such as this is a viable option.

Public Function fTestFunction(strQueryName As String)
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim intFieldCounter As Integer
 
Set cmd = New ADODB.Command
 
With cmd
  .ActiveConnection = CurrentProject.Connection
  .CommandType = adCmdTable
  .CommandText = strQueryName
End With
 
Set rst = cmd.Execute
 
With rst
  Do While Not .EOF
    For intFieldCounter = 0 To .Fields.Count - 1
      Debug.Print .Fields(intFieldCounter).Name & " - " & .Fields(intFieldCounter).Value
    Next
      .MoveNext
      Debug.Print "------------------------------------------------"
  Loop
End With
 
rst.Close
Set rst = Nothing
Set cmd = Nothing
End Function

Sample Call:

Call fTestFunction("Invoices")

[anniebai]

Thanks ADezii. It seems a safe plan. I'll give it a try.

[anniebai]

My code is like below. There're several Subs function similarly, the differences are the stored procedure names, procedure parameters, and the control name (which is to receive the RecordSet)

Private Sub LectureMaintSubFormRequery()
    Dim cmd As ADODB.Command, rst As ADODB.Recordset
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "EXEC dbo.spLectures " & Me.WkOfferingId
    cmd.CommandType = adCmdText
    Set rst = cmd.Execute
    Set Me![frmSubLectureMaint].Form.Recordset = rst
    Set cmd = Nothing
    Set rst = Nothing
End Sub
 

[anniebai]

I tried to create a function like
Public Function ExecuteSP(spName As String) As ADODB.Recordset

But problems are:
1. How to set the return value, which is ADODB.RecordSet, to nothing?
2. Where and how to pass the stored procedures' parameters? the stored procedures have various number and type of parameters.

Thanks very much for any inputs.

[ADezii]

@anniebai

1. How to set the return value, which is ADODB.RecordSet, to nothing?

The way I see it, you have 2 Options here, either create the Recordset and destroy it within the context of the Function:

Dim rst As ADODB.Recordset
 
Set rst = New ADODB.Recordset
 
rst.Open "qryEmployees", CurrentProject.Connection, _
               adOpenStatic, adLockOptimistic
 
'Process the Recordset
  '...Yada,Yada,Yada
 
'Close the Recordset in Memory & claim resources
rst.Close
Set rst = Nothing

OR Create/Set a Recordset = the Return Value of the Function, then deal with it

Dim rst As ADODB.Recordset
Set rst = fFunctionReturningADODBRecordset()
 
'Process the Recordset
  '...Yada,Yada,Yada
 
'Close the Recordset in Memory & claim resources
rst.Close
Set rst = Nothing

2. Where and how to pass the stored procedures' parameters? the stored procedures have various number and type of parameters.

You can pass an Array containing the Parameters to the Function, use the ParamArray Keyword, or my favorite: pass a Comma-Delimited String containing the Parameters to the Function which will then Split the String and place the Parameters into a Variant Array. I made some sample code for you to view below:

Public Function fSomeFunction(dteSomeDate As Date, strParams As String, lngBigNum As Long)
'This Function will accept 3 Parameters: a Date, Delimited String, and a Long
Dim varValues As Variant
Dim intCounter As Integer
 
varValues = Split(strParams, ",")
 
Debug.Print "The Date is: " & dteSomeDate
Debug.Print "A BIG Number is: " & lngBigNum
Debug.Print
 
Debug.Print "=========================================="
Debug.Print "Number of Parameters: " & UBound(varValues) + 1
Debug.Print "=========================================="
 
For intCounter = LBound(varValues) To UBound(varValues)
  Debug.Print "  Parameter #" & Format$(intCounter + 1, "00") & ": " & _
                 varValues(intCounter)
Next
End Function

Sample Call to Function:

Dim dteDate As Date
Dim strParameter1 As String
Dim strParameter2 As String
Dim strParameter3 As String
Dim strAllParameters As String
 
dteDate = Date
 
strParameter1 = "Parameter One"
strParameter2 = "Parameter Two"
strParameter3 = "Parameter Three"
 
strAllParameters = strParameter1 & "," & strParameter2 & "," & strParameter3
 
Call fSomeFunction(dteDate, strAllParameters, 9876599)

OUTPUT:

The Date is: 7/24/2009
A BIG Number is: 9876599
 
==========================================
Number of Parameters: 3
==========================================
  Parameter #01: Parameter One
  Parameter #02: Parameter Two
  Parameter #03: Parameter Three

[anniebai]

Dim rst As ADODB.Recordset
Set rst = fFunctionReturningADODBRecordset()
 
'Process the Recordset
  '...Yada,Yada,Yada
 
'Close the Recordset in Memory & claim resources
rst.Close
Set rst = Nothing

Since fFunctionReturningADODBRecordset() is also a recordset, Do I need to do this at the end of the calling procedure?
Set fFunctionReturningADODBRecordset() = nothing

[anniebai]

You can pass an Array containing the Parameters to the Function, use the ParamArray Keyword, or my favorite: pass a Comma-Delimited String containing the Parameters to the Function which will then Split the String and place the Parameters into a Variant Array. I made some sample code for you to view below

I can see that the example takes more than one string parameters. In the case of more than one integer pars, is ParamArray then the only option? do you have some examples of ParamArray?

Thanks a lot for your kind reply and helps.

[ADezii]

anniebai;3501479]I can see that the example takes more than one string parameters. In the case of more than one integer pars, is ParamArray then the only option? do you have some examples of ParamArray?

Thanks a lot for your kind reply and helps.

http://bytes.com/topic/access/insigh...ing-paramarray