• Solent Systems
  • Search Engine Optimisation
  • Website Design
  • Website Hosting

    • Returning an ADODB.Recordset from Function

    This entry was posted by Solent Systems Webmaster on Monday, 29 March, 2010

    To return an ADODB.Recordset in Visual Basic Application (VBA), from a function, the Recordset must be disconnected.

    1. Function queryDatabase(strSQL, username, password) As ADODB.Recordset
    2.  
    3.     'Declare our variables
    4.     Dim strConnection, conn, rs
    5.  
    6.     'Initialisation
    7.     Set rs = New ADODB.Recordset
    8.     strConnection = "Provider=sqloledb;Data Source=serverName;Initial Catalog=DbName;User Id=username;Password=password"
    9.     Set conn = New ADODB.Connection
    10.  
    11.     conn.Open strConnection
    12.     rs.CursorLocation = adUseClient
    13.     rs.Open strSQL, conn, ADODB.adOpenForwardOnly, ADODB.adLockBatchOptimistic
    14.  
    15.     'Disconnect the connection
    16.     Set rs.ActiveConnection = Nothing
    17.  
    18.     'Return the recordset
    19.     Set queryDatabase = rs
    20.  
    21.     conn.Close
    22.     Set conn = Nothing
    23. End Function
    24.  
    25. Sub main()
    26.     Dim rs As ADODB.Recordset
    27.     Set rs = queryDatabase("SELECT cols FROM DB", username, password)
    28.  
    29.     'Now we can handle our recordset, returned from the function
    30.     rs.MoveFirst
    31.     While Not rs.EOF
    32.         MsgBox (rs("colName"))
    33.         rs.MoveNext
    34.     Wend
    35.  
    36.     rs.Close
    37.     Set rs = Nothing
    38. End Sub

    Only a disconnected recordset is able to be passed around in your code and handled later.

    Share|
    Tags: , , ,
    Category: Data Interfaces
    You can follow any responses to this entry via RSS.
    You can leave a comment or trackback from your own site.

    Leave a Reply