Set the RecordSource for a Form to the result of an ADODB  RecordSet

Posted on 2007-11-28
Last Modified: 2013-11-28
We have a subroutine which returns an ADODB RecordSet and we can successfully use this to populate a Combobox
We also need to use similar logic to use the ADODB record set as the RecordSource for a Form.

When we use "Set Me.RecordSource = RecSet" we get  an error message "Invalid Use of Property"

When we use "Me.RecordSource = RecSet" we get  an error message "Type Mismatch"

When we use "Set Me.RecordSet = RecSet" we get  an error message "7965 The object you entered is not a valid recordset property"

When we use "Me.RecordSet = RecSet" we get  an error message "91 Object variable or With block variable not set"

Please can somebody tell me what we are missing what are we missing.

The attached Code snippets show the Subroutine and the Form  logic which works when populating the
Combo Box.

Form Open
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Open
Dim recset As ADODB.Recordset
Set recset = New ADODB.Recordset
strStreetType = "A"
recset.Open cmdStoredProc2("uspShowStreets", strStreetType, adVarWChar)
With recset
     Do Until .EOF
        cmbTest.AddItem !StreetType & ";" & !Abbreviation & ";" & !SortSequence
End With
Set recset = Nothing
    Exit Sub
    If Err.Number = 3021 Then
        MsgBox "No records found"
        MsgBox Err.Number & " " & Err.Description
    End If
    'Resume Exit_Open
End Sub
Public Function cmdStoredProc2(ParamArray avar() As Variant) As ADODB.Command
Dim prm As ADODB.Parameter
Dim lngElements As Long
Dim i As Long
Dim iCount As Integer
On Error GoTo HandleErr
i = 0
lngElements = UBound(avar())
If lngElements > 1 Then
    If Not (lngElements Mod 2) Then
        Set cmdStoredProc2 = New ADODB.Command
        With cmdStoredProc2
            .ActiveConnection = msSQLProvider & msSQLConnection
            .CommandText = CStr(avar(0))
            .CommandType = adCmdStoredProc
                For i = 0 To lngElements / 2 - 1
                    Set prm = .CreateParameter("Param" & _
                    CStr(i + 1), _
                    CInt(avar(2 * i + 2)), adParamInput, _
                    Len(avar(2 * i + 1)), avar(2 * i + 1))
                    .Parameters.Append prm
                Next i
      End With
    End If
    MsgBox "You must supply at least 2 paramters, SP Name and Reply Type", vbCritical
    GoTo End_Function
End If
GoTo End_Function
   MsgBox Err.Description
   iCount = -1
End Function

Open in new window

Question by:CABHugh
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 20364776
This is, apparently, coming from a Server. If that's the case, the following requirements must be met:

1. The underlying ADO recordset must be updateable.
2. The recordset must contain one or more fields that are uniquely indexed, such as a table's primary key.

In specific regard to SQL Server:

• The ADO recordset's connection must use the Microsoft Access 10.0 OLEDB provider as its service provider.
• The ADO recordset's connection must use the Microsoft SQL Server OLEDB provider as its data provider.

Since you're doing this with a Stored Procedure, you might not be able to do this.

Info came from here:

Author Comment

ID: 20364891
Thank you for such a rapid response.

The first column is the primary key (street type).

I am checking out the other factors you suggest.



Expert Comment

ID: 20364931
Form.Recordsource is a string which the form evaluates to a datasource itself.
You can set the Form.Recordset property to a recordset object, but this must be a DAO.Recordset object of appropriate type.
(things may be different on various MS Access versions)

following will work:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM MyTbl", DAO.dbOpenDynaset)
Set rs = CurrentDb.OpenRecordset("MyTbl", DAO.dbOpenSnapshot)
Set Me.Recordset = rs

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 27

Expert Comment

ID: 20364970
The correct assignment to use is
Set Me.Recordset = RecSet
The updateable recordset and unique index restrictions only apply if you want to update data through the form.
What version of Access are you using and what type of Server your ADO recordsets are coming from?

Author Comment

ID: 20365058

Thank you for your input.  We are using ADODB to follow Microsoft's recommendations to move away from DAO.
We have used Set Me.Recordset = RecSet (where the RecSet is from DAODB but we get one of the errors I listed initially.
We are running SQL Server 2005 with Access 2003.
I am sure things should not be this complex........


LVL 27

Expert Comment

ID: 20365212
Can you post the code that sets the connection and opens the recordset you're trying to allocate to the form.
(btw, with the release of Access 2007 I think that the word from MS is that DAO is back in fashion, lol)

Author Comment

ID: 20365226
I would like to thank everybody for their assistance in SOLVING this problem.

The solution turned out to be the requirement to include  a reference to the Data Provider "Microsoft.Access.OLEDB10.0".

Once this was changed, the "Set Me.Recordset = Recset" worked and the data appeared in the form.

Many thanks again.


Author Closing Comment

ID: 31411390
Many Thanks.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question