By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,062 Members | 1,283 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,062 IT Pros & Developers. It's quick & easy.

Declare database connection variables as global

P: 51
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
Jul 23 '09 #1
Share this Question
Share on Google+
9 Replies


P: 51
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
Jul 23 '09 #2

ADezii
Expert 5K+
P: 8,679
@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.
Expand|Select|Wrap|Line Numbers
  1. Public Function fTestFunction(strQueryName As String)
  2. Dim cmd As ADODB.Command
  3. Dim rst As ADODB.Recordset
  4. Dim intFieldCounter As Integer
  5.  
  6. Set cmd = New ADODB.Command
  7.  
  8. With cmd
  9.   .ActiveConnection = CurrentProject.Connection
  10.   .CommandType = adCmdTable
  11.   .CommandText = strQueryName
  12. End With
  13.  
  14. Set rst = cmd.Execute
  15.  
  16. With rst
  17.   Do While Not .EOF
  18.     For intFieldCounter = 0 To .Fields.Count - 1
  19.       Debug.Print .Fields(intFieldCounter).Name & " - " & .Fields(intFieldCounter).Value
  20.     Next
  21.       .MoveNext
  22.       Debug.Print "------------------------------------------------"
  23.   Loop
  24. End With
  25.  
  26. rst.Close
  27. Set rst = Nothing
  28. Set cmd = Nothing
  29. End Function
Sample Call:
Expand|Select|Wrap|Line Numbers
  1. Call fTestFunction("Invoices")
Jul 24 '09 #3

P: 51
Thanks ADezii. It seems a safe plan. I'll give it a try.
Jul 24 '09 #4

P: 51
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)

Expand|Select|Wrap|Line Numbers
  1. Private Sub LectureMaintSubFormRequery()
  2.     Dim cmd As ADODB.Command, rst As ADODB.Recordset
  3.     Set cmd = New ADODB.Command
  4.     cmd.ActiveConnection = CurrentProject.Connection
  5.     cmd.CommandText = "EXEC dbo.spLectures " & Me.WkOfferingId
  6.     cmd.CommandType = adCmdText
  7.     Set rst = cmd.Execute
  8.     Set Me![frmSubLectureMaint].Form.Recordset = rst
  9.     Set cmd = Nothing
  10.     Set rst = Nothing
  11. End Sub
  12.  
Jul 24 '09 #5

P: 51
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.
Jul 24 '09 #6

ADezii
Expert 5K+
P: 8,679
@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:
Expand|Select|Wrap|Line Numbers
  1. Dim rst As ADODB.Recordset
  2.  
  3. Set rst = New ADODB.Recordset
  4.  
  5. rst.Open "qryEmployees", CurrentProject.Connection, _
  6.                adOpenStatic, adLockOptimistic
  7.  
  8. 'Process the Recordset
  9.   '...Yada,Yada,Yada
  10.  
  11. 'Close the Recordset in Memory & claim resources
  12. rst.Close
  13. Set rst = Nothing
OR Create/Set a Recordset = the Return Value of the Function, then deal with it

Expand|Select|Wrap|Line Numbers
  1. Dim rst As ADODB.Recordset
  2. Set rst = fFunctionReturningADODBRecordset()
  3.  
  4. 'Process the Recordset
  5.   '...Yada,Yada,Yada
  6.  
  7. 'Close the Recordset in Memory & claim resources
  8. rst.Close
  9. 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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fSomeFunction(dteSomeDate As Date, strParams As String, lngBigNum As Long)
  2. 'This Function will accept 3 Parameters: a Date, Delimited String, and a Long
  3. Dim varValues As Variant
  4. Dim intCounter As Integer
  5.  
  6. varValues = Split(strParams, ",")
  7.  
  8. Debug.Print "The Date is: " & dteSomeDate
  9. Debug.Print "A BIG Number is: " & lngBigNum
  10. Debug.Print
  11.  
  12. Debug.Print "=========================================="
  13. Debug.Print "Number of Parameters: " & UBound(varValues) + 1
  14. Debug.Print "=========================================="
  15.  
  16. For intCounter = LBound(varValues) To UBound(varValues)
  17.   Debug.Print "  Parameter #" & Format$(intCounter + 1, "00") & ": " & _
  18.                  varValues(intCounter)
  19. Next
  20. End Function
Sample Call to Function:
Expand|Select|Wrap|Line Numbers
  1. Dim dteDate As Date
  2. Dim strParameter1 As String
  3. Dim strParameter2 As String
  4. Dim strParameter3 As String
  5. Dim strAllParameters As String
  6.  
  7. dteDate = Date
  8.  
  9. strParameter1 = "Parameter One"
  10. strParameter2 = "Parameter Two"
  11. strParameter3 = "Parameter Three"
  12.  
  13. strAllParameters = strParameter1 & "," & strParameter2 & "," & strParameter3
  14.  
  15. Call fSomeFunction(dteDate, strAllParameters, 9876599)
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. The Date is: 7/24/2009
  2. A BIG Number is: 9876599
  3.  
  4. ==========================================
  5. Number of Parameters: 3
  6. ==========================================
  7.   Parameter #01: Parameter One
  8.   Parameter #02: Parameter Two
  9.   Parameter #03: Parameter Three
Jul 24 '09 #7

P: 51
Expand|Select|Wrap|Line Numbers
  1. Dim rst As ADODB.Recordset
  2. Set rst = fFunctionReturningADODBRecordset()
  3.  
  4. 'Process the Recordset
  5.   '...Yada,Yada,Yada
  6.  
  7. 'Close the Recordset in Memory & claim resources
  8. rst.Close
  9. 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
Jul 27 '09 #8

P: 51
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.
Jul 27 '09 #9

ADezii
Expert 5K+
P: 8,679
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
Jul 27 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.