Return ADO recordset as a function

[bradyboyy88]

So I was wondering if you are able to pass a recordset as a function and if you assign this returned recordset as a value then when variable type is that record set. Such as:

UsernameRecordset = DatabaseMethods.SQLQueryDatabase(SQLQueryCode, DatabaseDirectory, 0)

I did Dim UsernameRecordset As Variant but that draws a mismatch error. And Dim UsernameRecordset As ADODB.Recordset draws an error as well.

The code for the function is :

Public Function SQLQueryDatabase(SQLQuery As String, StrDBPath As String, EngineType As Integer, Optional ByVal FieldNumber As Integer)




    'Declare Variables
    Dim oConn        As Object
    Dim oRs          As Object
    Dim sConn        As String
   
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
    
    End If
    
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
   
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    
    'Connect to the database
    oConn.Open sConn
    
    'Define SQL String
    'SQLQuery = "SELECT * FROM Username_Table"
    
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Return single value or null if it doesnt exists
    If oRs.EOF And oRs.BOF Then


        SQLQueryDatabase = Null
        
    Else
        
         If FieldNumber = "" Then
            
            SQLQueryDatabase = oRs
            
         Else
         
            SQLQueryDatabase = oRs(FieldNumber).Value
         
         End If
        
    
    End If


    'Close Connection
    oConn.Close
    
    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing




End Function

 

[bradyboyy88]

Instead of Variant have you tried using Object?

Okay I am dumbing it down to the code below. Getting the object variable or block variable not set.

Sub TestingRecordset()


'Declare Variable Values
DatabaseDirectory = "G:\blah blah blah.accdb"


Dim ActiveRecordset As Object
Set ActiveRecordset = CreateObject("ADODB.Recordset")


'Find the last login date for the Username
SQLQueryCode = "Select * FROM USER_ACCOUNTS WHERE USERNAME='" & Username & "' AND PASSWORD='" & Password & "'"
ActiveRecordset = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode, DatabaseDirectory, 0)


End Sub

and the function

'Return a query as a recordset
Public Function SQLQueryDatabaseRecordset(SQLQuery As String, StrDBPath As String, EngineType As Integer) As Object

    'Declare Variables
    Dim oConn        As Object
    Dim oRs          As Object
    Dim sConn        As String
   
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
    
    End If
    
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
   
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    
    'Connect to the database
    oConn.Open sConn
       
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Return recordset
    SQLQueryDatabaseRecordset = oRs
        
    'Close Connection
    oConn.Close
    
    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing


End Function

 

[Norie]

You need Set here,

ActiveRecordset = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode, DatabaseDirectory, 0)

and here.

  'Return recordset
    SQLQueryDatabaseRecordset = oRs

 

[bradyboyy88]

You need Set here,

ActiveRecordset = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode, DatabaseDirectory, 0)

and here.

  'Return recordset
    SQLQueryDatabaseRecordset = oRs

That works and no longer draws an error running the test !! So reading a value from the recordset should look like this right ?

MsgBox ActiveRecordset.Fields(1).Value

It draws a error stating "Item cannot be found in the collection corresponding to the requested name or ordinal" when i add that message box to try and read form ActiveRecordset

 

[Norie]

How many fields are in the recordset when you run that code?

 

[bradyboyy88]

How many fields are in the recordset when you run that code?

Should be 6 since i did select * so it should have pulled all fields.

 

[Norie]

You should not have this.

Set ActiveRecordset = CreateObject("ADODB.Recordset")

That's just kind of creating an 'empty' recordset.

Also, you should be using Set here as the function returns an Object.

ActiveRecordset = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode, DatabaseDirectory, 0)

 

[bradyboyy88]

You should not have this.

Set ActiveRecordset = CreateObject("ADODB.Recordset")

That's just kind of creating an 'empty' recordset.

Also, you should be using Set here as the function returns an Object.

ActiveRecordset = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode, DatabaseDirectory, 0)

So I have updated the code accordingly and added the set and removed that duplicate empty recordset you mentioned. Still getting the same error though.

 

[bradyboyy88]

You should not have this.

Set ActiveRecordset = CreateObject("ADODB.Recordset")

That's just kind of creating an 'empty' recordset.

Also, you should be using Set here as the function returns an Object.

ActiveRecordset = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode, DatabaseDirectory, 0)

Any chance this could be a result of me closing the recordset in my query function I made? I have read similar issues

 

[V_Malkoti]

I think its because you've closed the database connection in the method which creates and returns recordset object. Without an active open connection, recordset cannot access database.

 

[bradyboyy88]

I think its because you've closed the database connection in the method which creates and returns recordset object. Without an active open connection, recordset cannot access database.

So despite I set my function to return it as an object to another object variable, closing the connection removes the data in that recordset? Anyway around that?