Return ADO recordset as a function

B

bradyboyy88

Well-known Member
Joined
Feb 25, 2015
Messages
562
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:

Code: 
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 :

Code: 
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
 
Last edited:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Hi Brady,

this works for me (I simplified your code a bit):

Code: 
Sub testfunctions()


Dim UsernameRecordset As ADODB.Recordset
Set UsernameRecordset = SQLQueryDatabase("BLA")

End Sub

Public Function SQLQueryDatabase(SQLQuery As String) As ADODB.Recordset

    'Declare Variables
    Dim oConn        As Object
    Dim oRs          As Object
    Dim sConn        As String
   
   StrDBPath = "C:\Users\CREATE_DATA.accdb"
   
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;"

    
    '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 XTBL_LOG"
    
    '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


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


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

End Function

Cheers,

Koen
 
Upvote 0
Thank you for that! I guess for a Recordset you need to include the "As ADODB.Recordset" so its impossible for it to return a single value (when optional field is filled in) OR a recordset?
 
Upvote 0
Hi Brady,
I could try to work out a function that gives back either a recordset or one value, but for me the purpose of a function is to give back something fixed, so you don't have to post-process the result to find out what your function gave back. Having said that, I've been messing around a bit but it seems not possible to let your function give back either a recordset or a value, because for the recordset you would have to call the function like:

Set UR1 = SQLQueryDatabase("BLA")
And for the value as:
UR2 = SQLQueryDatabase("BLA", 2)

What you could do is let the optional variant give back a Field:
Set SQLQueryDatabase = oRs.Fields(FieldNumber)

Your function would be:
Public Function SQLQueryDatabase(SQLQuery As String, Optional ByVal FieldNumber As Integer = 0) As Variant

You still would have to figure out what the function returned.

Hope that helps,

Koen
 
Upvote 0
Rijnsent said:
Hi Brady,
I could try to work out a function that gives back either a recordset or one value, but for me the purpose of a function is to give back something fixed, so you don't have to post-process the result to find out what your function gave back. Having said that, I've been messing around a bit but it seems not possible to let your function give back either a recordset or a value, because for the recordset you would have to call the function like:

Set UR1 = SQLQueryDatabase("BLA")
And for the value as:
UR2 = SQLQueryDatabase("BLA", 2)

What you could do is let the optional variant give back a Field:
Set SQLQueryDatabase = oRs.Fields(FieldNumber)

Your function would be:
Public Function SQLQueryDatabase(SQLQuery As String, Optional ByVal FieldNumber As Integer = 0) As Variant

You still would have to figure out what the function returned.

Hope that helps,

Koen
Click to expand...

Very useful information. I think you are right. I may just create too functions , one for a recordset if I dont want to keep repulling the data and one to pull the single value.

I do have one idea which does lose some value since its not a recordset. But is there a clean efficient way to transfer a recordset to a Array? Thanks
 
Last edited:
Upvote 0
Rijnsent said:
Hi Brady,

this works for me (I simplified your code a bit):

Code: 
Sub testfunctions()


Dim UsernameRecordset As ADODB.Recordset
Set UsernameRecordset = SQLQueryDatabase("BLA")

End Sub

Public Function SQLQueryDatabase(SQLQuery As String) As ADODB.Recordset

    'Declare Variables
    Dim oConn        As Object
    Dim oRs          As Object
    Dim sConn        As String
   
   StrDBPath = "C:\Users\CREATE_DATA.accdb"
   
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;"

    
    '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 XTBL_LOG"
    
    '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


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


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

End Function

Cheers,

Koen
Click to expand...

Also, for some reason your method generates an user defined error for me when adding As ADODB.Recordset to the title of the function. It seems logical to just return the recordset and not a single value but those other fields were needed. So I tried the function header like this:

Public Function SQLQueryDatabase(SQLQuery As String, StrDBPath As String, EngineType As Integer) As ADODB.Recordset

But that line draws the error. If I change it to As Variant it works but draws a invalid property assignment error or wrong number of arguements . Which is most likely related to the first part as the error.
 
Upvote 0
If you are getting an error when you add As ADODB.Recordset to the function header you should also get the same error here.
Code: 
Dim UsernameRecordset As ADODB.Recordset
Are you?
 
Upvote 0
Norie said:
If you are getting an error when you add As ADODB.Recordset to the function header you should also get the same error here.
Code: 
Dim UsernameRecordset As ADODB.Recordset
Are you?
Click to expand...

So if I change the function to As Variant then I do draw an error on that variable declaration as well. If I change that variable declaration to Variant as well but then use

Code: 
Set UsernameRecordset = CreateObject("ADODB.Recordset")

Then the error becomes wrong number of arguments or invalid property assignment on my function when run in my subroutine. So I can step through the code and it slowly takes me through where the function is called and lets me go all the way through the function and then get hit with that error right at "end function".

So here is how my code is situated:

Module called global_variables which is where i declare

Code: 
Public UsernameRecordset As Variant

Then sheet called login_screen
Code: 
Public Sub Login_Submit_Button_Click()


'Declare Variable Values
DatabaseDirectory = "G:\asdfasdf.accdb"
Set UsernameRecordset = CreateObject("ADODB.Recordset")


'Captures username from the login page
Username = Sheets("Login_Screen").Username_Login.Text
Password = Sheets("Login_Screen").Password_Login.Text


'Tests to see if the login record for username and password actually exists and if not will execute login popup error
SQLQueryCode = "Select * FROM USER_ACCOUNTS WHERE USERNAME='" & Username & "' AND PASSWORD='" & Password & "'"
UsernameRecordset = DatabaseMethods.SQLQueryDatabase(SQLQueryCode, DatabaseDirectory, 0)


If IsNull(UsernameRecordset) Then
    
    'Username and pass not found so it initalizes userform to shot error message
    Login_Popup_Error.Show
    
Else
    
    'There is a match so user is now logged in and the page initializing begins.
    MsgBox "You just logged in"


    'Initialize all Admin Page data
    Call AdminPageInitialize
    
    'Load to the Admin Page
    ExcelGUIUpdates.DynamicRows ("Admin_1")
    
End If


End Sub

Then using some of the comments above I have made the code only return recordset according to posts above as my method is not really practical anyhow:
Code: 
Public Function SQLQueryDatabase(SQLQuery As String, StrDBPath As String, EngineType As Integer) As Variant




    '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
         
        SQLQueryDatabase = oRs
          
    End If
 
    'Close Connection
    oConn.Close
    
    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing




End Function
 
Last edited:
Upvote 0
Instead of Variant have you tried using Object?
 
Upvote 0
Norie said:
Instead of Variant have you tried using Object?
Click to expand...

Yea it did not seem to work either. Drawing the invalid number of arguements error again despite I have adjusted that accordingly.

I dont even care about having the complicated method originally of using by val to maybe return a single value, all i really want now is to pass the entire recordset as a function :(. So simplifying the above code is deinitely feasible but just need to get down passing this type of object. I do not see how the original answer to my question worked as that code draws an error right onto the function from the beginning. Can you test his to see if that works for you? Maybe I need to reference something that I am missing?

I am treating the Access DB similar to a webserver and deploying an application which everyone is using to read and write to the database. So getting these tools down as functions would be great as I already have the subroutine for writing to the DB down pat.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
delete a record in Access based on a reference in Excel Field
Replies
2
Views
334
gmazza76
G
T
  • Solved
Problem with date format when reading from workbook via SQL / ADODB
Replies
7
Views
454
Thebatfink
T
C
  • Question
ole db CopyFromRecordset skipping cell values
Replies
0
Views
396
Clif McIrvin
C
D
  • Solved
Adodb Method 'Open' of object '_Recordset' failed
Replies
4
Views
768
Micron
M
G
  • Solved
VBA connect to Ms Access using ADODB.Recordset with non-alphanumeric table name
Replies
2
Views
816
gifariz
G

Forum statistics

Threads
1,216,975
Messages
6,133,827
Members
449,835
Latest member
vietoonet

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top