Solved

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

Posted on 2007-11-28
8
718 Views
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

     .MoveFirst

     Do Until .EOF

        cmbTest.AddItem !StreetType & ";" & !Abbreviation & ";" & !SortSequence

        .MoveNext

     Loop

End With

 

Me.cmbTest.SetFocus

  

recset.Close

Set recset = Nothing
 

Exit_Open:

    Exit Sub
 

Err_Open:

    If Err.Number = 3021 Then

        MsgBox "No records found"

    Else

        MsgBox Err.Number & " " & Err.Description

    End If

    

    'Resume Exit_Open
 

End Sub
 

'---------------------------------------

'Subroutine

'---------------------------------------

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

Else

    MsgBox "You must supply at least 2 paramters, SP Name and Reply Type", vbCritical

    GoTo End_Function

End If
 

GoTo End_Function
 

HandleErr:

   MsgBox Err.Description

   iCount = -1
 

End_Function:

End Function

Open in new window

0
Comment
Question by:CABHugh
8 Comments
 
LVL 84

Accepted Solution

by:
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: http://support.microsoft.com/kb/281998
0
 

Author Comment

by:CABHugh
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.

Roy

0
 
LVL 4

Expert Comment

by:inox
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


0
 
LVL 27

Expert Comment

by:MikeToole
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?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:CABHugh
ID: 20365058
Mike,

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........

Regards

Roy
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 20365212
Roy
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)
0
 

Author Comment

by:CABHugh
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.

Roy
0
 

Author Closing Comment

by:CABHugh
ID: 31411390
Many Thanks.

0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now