Can you optimize this module? 1

[padinka]

This function takes a several records and appends them together in a single string separated by commas.


Function Attached_Cases(myTicketId As Long) As String


Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
Dim fld As Field
Dim myLen As Long


Set dbs = CurrentDb()

strSQL = "SELECT dbo_SW_CASE.swCaseId FROM dbo_SW_CASE INNER JOIN dbo_SW_TICKET ON dbo_SW_CASE.dsTicketId = dbo_SW_TICKET.swTicketId WHERE dbo_SW_TICKET.swTicketId = " & myTicketId & ""

Set rst = dbs.OpenRecordset(strSQL)

Set fld = rst!swCaseId


While (Not (rst.EOF))
Attached_Cases = Attached_Cases & fld & ", "
rst.MoveNext
Wend

myLen = Len(Attached_Cases) - 2

'get rid of the last comma
Attached_Cases = Left(Attached_Cases, myLen)

' Close the recordset and the database.
rst.Close
dbs.Close
End Function


Trisha
padinka@yahoo.com

 

[jimmythegeek]

I see you have "dbo" as the prefix to your tables. Are you using attached tables to a SQL Server database? If so, you can speed up the process by either using a Pass-through query, or ADO. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[MichaelRed]

The "fld" var needs to be 'interperted on each execution of the loop, so embedding the bare reference may help a bit.
'________________________________
'Set fld = rst!swCaseId


While (Not (rst.EOF))
Attached_Cases = Attached_Cases & rst!swCaseId& ", "
rst.MoveNext
'__________________________________________________

Depending on HOW you use the resuts, It could be better for the remainder of the process if you place the results in an array
'________________________________________________________

Function Attached_Cases(myTicketId As Long) As Variant

    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL As String
    Dim fld As Field
    Dim myLen As Long
    Dim tmpArray() As Variant

    ReDim tmpArray(0)
     
    Set dbs = CurrentDb()
    
    strSQL = "SELECT dbo_SW_CASE.swCaseId "
    strSQL = strSQL & "FROM dbo_SW_CASE "
    strSQL = strSQL & "INNER JOIN dbo_SW_TICKET "
    strSQL = strSQL & "ON dbo_SW_CASE.dsTicketId = dbo_SW_TICKET.swTicketId "
    strSQL = strSQL & "WHERE dbo_SW_TICKET.swTicketId = " & myTicketId & ""
    
    Set rst = dbs.OpenRecordset(strSQL)
  
    While (Not (rst.EOF))
        tmpArray(UBound(tmpArray)) = rst!swCaseId
        ReDim Preserve tmpArray(UBound(tmpArray) + 1)
        rst.MoveNext
    Wend

    Attached_Cases = tmpArray
    
    ' Close the recordset and the database.
    rst.Close
    dbs.Close

End Function

'_________________________________________________________


Assuming that yor query is invarriant (e.g. STATIC), you will achieve some efficiency by making it a "saved" query and reference it by name. Ms. Access will 're-compile' the query each time this function is instantantiated, to check for syntax and references. If it is saved as a "Compiled Query", most of this overhead is removed.
Wend

Function Attached_Cases(myTicketId As Long) As Variant

    Dim dbs As Database
    Dim rst As Recordset
    Dim strSQL As String
    Dim fld As Field
    Dim myLen As Long
    Dim tmpArray() As Variant

    ReDim tmpArray(0)
     
    Set dbs = CurrentDb()
    
'    strSQL = "SELECT dbo_SW_CASE.swCaseId "
'    strSQL = strSQL & "FROM dbo_SW_CASE "
'    strSQL = strSQL & "INNER JOIN dbo_SW_TICKET "
'    strSQL = strSQL & "ON dbo_SW_CASE.dsTicketId = dbo_SW_TICKET.swTicketId "
'    strSQL = strSQL & "WHERE dbo_SW_TICKET.swTicketId = " & myTicketId & ""
    
    Set rst = dbs.OpenRecordset("MyQry", dbOpenRecordset)
    'Where "MyQry" is just the query you generate/write the SQL string for,
    'saved as (OBVIOUSLY) "MyQry", or what ever other name is suitable in your app.
  
    While (Not (rst.EOF))
        tmpArray(UBound(tmpArray)) = rst!swCaseId
        ReDim Preserve tmpArray(UBound(tmpArray) + 1)
        rst.MoveNext
    Wend

    Attached_Cases = tmpArray
    
    ' Close the recordset and the database.
    rst.Close
    dbs.Close

End Function

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[MichaelRed]

OOps, Sorry,

I forgot to mention. In both of the complete examples, the function "returns" an array to the calling function, so the callling function needs to define a variable as VARIANT, and set the function to this variable.

e.g.:

[tab]Dim MyAray as Variant
[tab]MyAray = Attached_Cases(myTicketId)

On return, you can test the UBound of the varaible (e.g. MyAray) to get the number of elements in the array (rember it is/will be ZERO Based!)



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over