set access form recordset to ADO recordset 1

[clapper62]

I'm trying to set an Access Forms recordset to an ADO recordset containing a list of tables in an Access .mdb database
with the following code

[indent][/indent]Set rst = objConnection.OpenSchema(adSchemaTables)
    rst.MoveLast
    rst.MoveFirst
    Debug.Print rst!TABLE_NAME
    
    Set Forms!frmData.Recordset = rst
    
    rst.Close
    objConnection.Close

I get a Runtime Error 7965 / the object you entered is not a valid recordset property at line

Set Forms!frmData.Recordset = rst

I know the recordset was created ok because the debug.print statement returns a table name from the database
also I have successfully used this same code when I return a list of tables from a MS SQL Server database

 

[lameid]

Forms!frmData.Recordset is a property not an object... Try it without the Set.

 

[clapper62]

thank you for the reply

Ok I tried

Forms!frmData.Recordset = rst

and I got
Runtime error 91
object variable or with block variable not set

I feel I should say again that

Set Forms!frmData.Recordset = rst

works if I connect to a MS SQL Server

 

[lameid]

Curious, I never noticed that but then again I usually don't set the recordset. But Runtime error 91 usually means you need a set and don't have one.

Set rst = objConnection.OpenSchema(adSchemaTables)

I did not look closely at that line. So that should return a table and view list.

It looks good syntax wise. I am wondering if it does not like that recordset for some reason. The only thing I noticed that seemed odd in the description is that it is read only which I would not expect to be a problem but maybe it is. Perhaps there is another way to get the table list from the Database Engine you are connecting to that might work out better? I would have to look them up but SQL Server for example has some built in functionality for this (I want to say system views but my recollection is poor on the subject).

 

[clapper62]

I have to think that there must be a difference between a ADO recordset retrieved from a SQL Server database and one retrieved from an Access database but I have no explanation as to why this would be. The only difference in the code is the connection string.

In the interest of being thorough I'm posting the entire Procedure

Private Sub cmdListTables_Click()
    Dim intLoop As Integer
    Dim strConnectionString As String
    Dim objConnection As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim lngTwipsPerInch As Long
    lngTwipsPerInch = 1440
    
    strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Interface.mdb;Persist Security Info=False;"
    
    Set objConnection = CreateObject("ADODB.Connection")
    
    objConnection.ConnectionString = strConnectionString
    objConnection.Open
    Set rst = objConnection.OpenSchema(adSchemaTables)
    rst.MoveLast
    rst.MoveFirst
    
    Forms.frmData.Controls("LABEL0").Caption = "Table Name"
    Forms.frmData.Controls("LABEL0").Visible = True
    
    Forms.frmData.Controls("LABEL1").Caption = "Table Type"
    Forms.frmData.Controls("LABEL1").Visible = True
    
    Forms.frmData.Controls("TboxField0").Width = 3 * lngTwipsPerInch
    
    Forms.frmData.Controls("TboxField0").ControlSource = "TABLE_NAME"
    Forms.frmData.Controls("TboxField0").Visible = True
    
    Forms.frmData.Controls("TboxField1").Left = 3.1 * lngTwipsPerInch
    Forms.frmData.Controls("TboxField1").ControlSource = "TABLE_TYPE"
    Forms.frmData.Controls("TboxField1").Visible = True
    
    rst.MoveLast
    rst.MoveFirst
    Debug.Print rst!TABLE_NAME
    
    Set Forms!frmData.Recordset = rst
    'Forms!frmData.Recordset = rst
    rst.Close
    objConnection.Close
    
    
End Sub

 

[MajP]

You are correct. The ADO recordset has a whole lot of properties:

https://msdn.microsoft.com/en-us/library/windows/desktop/ms675061(v=vs.85).aspx

A lot of these have default settings, and are influenced by the type of connection. The two common culprits that give this error is the cursortype and cursorposition. So eventhough you return a valid recordset, it is not valid for binding to a form. And thus the "object you entered is not a valid recordset property"

An example would be if you return a forward only recordset. You would not be able to navigate it. So you get that exact error. You can demo as shown below.

Private Sub Form_Load()
   Dim cn As ADODB.Connection
   Dim rs As New ADODB.Recordset
   On Error GoTo errlbl
   Set cn = CurrentProject.Connection
   With rs
       .ActiveConnection = cn
      .Source = "SELECT * FROM tblkey"
      .LockType = adLockOptimistic
      .CursorType = adOpenForwardOnly
      'If you uncomment the above line you will throw the following error:
      '7965 The object you entered is not a valid Recordset property. Because you cannot use
      'a forward only recordset. Which makes sense for a form. This is the default type using the openschema  
       .CursorType = adOpenKeyset
      .Open
   End With
  Set Me.Recordset = rs
  Exit Sub
errlbl:
   MsgBox Err.Number & " " & Err.Description
   Debug.Print Err.Number & " " & Err.Description
End Sub

There is a little bit of the discussion here:

https://support.microsoft.com/en-us/kb/281998

I am not sure which property/s are affected. I likely has to do with the cursor position or cursor type.

 

[clapper62]

Thanks for the info MajP

after looking through the links you provided I still don't have a solution
when I created a recordset with a SELECT statement from a table in the database I was able to bind that recordset to the form but I still can't bind an OpenSchema(adSchemaTables) recordset. I tried all of the different cursortypes

A work around solution was to crate a temporary table and populate it's records with the recordset data and creating a form based on that temporary table

 

[lameid]

If you wanted to keep looking...

I would loop the properties of each recordset and log them to a table and then compare them for what is different paying particular attention to what MajP identified as the properties that likely make a difference (cursortype and cursorposition).

The two recordsets to compare would be the SQL and Access versions of the same code where the SQL works and the Access doesn't.

 

[clapper62]

in an attempt to follow through on lameid suggestion
I used the following code

Dim i As Integer
    With rst
        For i = 0 To (.Properties.Count - 1)
            Debug.Print i & " " & .Properties(i).Name & ", " & Properties(i)
        Next i
    End With

which produced the following results (which honestly I lack the skill to know if it contains any useful information)

Database Type Microsoft SQL Server
0 IAccessor,
1 IChapteredRowset,
2 IColumnsInfo, False
3 IColumnsRowset, False
4 IConnectionPointContainer, 1
5 IConvertType, 0
6 ILockBytes, 0
7 IRowset, True
8 IDBAsynchStatus, True
9 IParentRowset, False
10 IRowsetChange, False
11 IRowsetExactScroll, True
12 IRowsetFind, 0
13 IRowsetIdentity, (none)
14 IRowsetInfo, False
15 IRowsetLocate, 2
16 IRowsetRefresh, 0
17 IRowsetResynch, 31680
18 IRowsetScroll, False
19 IRowsetUpdate, True
20 IRowsetView, True
21 IRowsetIndex, 2
22 ISequentialStream, True
23 IStorage, True
24 IStream,
25 ISupportErrorInfo, False
26 Preserve on Abort, 3
27 Access Order, True
28 Append-Only Rowset, True
29 Blocking Storage Objects, True
30 Use Bookmarks, True
31 Skip Deleted Bookmarks, 3
32 Bookmark Type, False
33 Cache Deferred Columns, 0
34 Fetch Backwards, 0
35 Hold Rows, True
36 Scroll Backwards, 0
37 Change Inserted Rows, 0
38 Column Privileges, True
39 Command Time Out, False
40 Preserve on Commit, 0
41 Defer Column, 24
42 Delay Storage Object Updates, 24
43 Private1, False
44 Filter Operations, 0
45 Find Operations, 0
46 Hidden Columns, True
47 Immobile Rows,
48 Literal Bookmarks, False
49 Literal Row Identity, False
50 Maximum Open Rows,
51 Maximum Pending Rows, False
52 Maximum Rows, True
53 Column Writable, False
54 Memory Usage, False
55 Notification Granularity, True
56 Notification Phases, True
57 Column Set Notification, True
58 Row Delete Notification, True
59 Row First Change Notification,
60 Row Insert Notification,
61 Row Resynchronization Notification,
62 Rowset Release Notification,
63 Rowset Fetch Position Change Notification,
64 Row Undo Change Notification,
65 Row Undo Delete Notification,
66 Row Undo Insert Notification,
67 Row Update Notification,
68 Bookmarks Ordered,
69 Others' Inserts Visible,
70 Others' Changes Visible,
71 Own Inserts Visible,
72 Own Changes Visible,
73 Quick Restart,
74 Reentrant Events,
75 Remove Deleted Rows,
76 Report Multiple Changes,
77 Return Pending Inserts,
78 Row Privileges,
79 Asynchronous Rowset Processing,
80 Row Threading Model,
81 Server Cursor,
82 Strong Row Identity,
83 Objects Transacted,
84 Unique Rows,
85 Updatability,
86 Batch Size,
87 Update Criteria,
88 Background Fetch Size,
89 Initial Fetch Size,
90 Background thread Priority,
91 Cache Child Rows,
92 Maintain Change Status,
93 Auto Recalc,
94 Unique Table,
95 Unique Schema,
96 Unique Catalog,
97 Resync Command,
98 Cursor Engine Version,
99 Reshape Name,
100 Update Resync,
101 Release Shape On Disconnect,
102 Bookmarkable,

Database Type Microsoft Access (mdb)
0 Preserve on Abort,
1 Blocking Storage Objects,
2 Use Bookmarks, False
3 Skip Deleted Bookmarks, False
4 Bookmark Type, 1
5 Cache Deferred Columns, 0
6 Fetch Backwards, 0
7 Hold Rows, True
8 Scroll Backwards, True
9 Column Privileges, False
10 Preserve on Commit, False
11 Defer Column, True
12 Delay Storage Object Updates, 0
13 Immobile Rows, (none)
14 Literal Bookmarks, False
15 Literal Row Identity, 2
16 Maximum Open Rows, 0
17 Maximum Pending Rows, 31680
18 Maximum Rows, False
19 Column Writable, True
20 Memory Usage, True
21 Notification Phases, 2
22 Bookmarks Ordered, True
23 Others' Inserts Visible, True
24 Others' Changes Visible,
25 Own Inserts Visible, False
26 Own Changes Visible, 3
27 Quick Restart, True
28 Reentrant Events, True
29 Remove Deleted Rows, True
30 Report Multiple Changes, True
31 Row Privileges, 3
32 Row Threading Model, False
33 Objects Transacted, 0
34 Updatability, 0
35 Strong Row Identity, True
36 IAccessor, 0
37 IColumnsInfo, 0
38 IColumnsRowset, True
39 IConnectionPointContainer, False
40 IRowset, 0
41 IRowsetChange, 24
42 IRowsetIdentity, 24
43 IRowsetInfo, False
44 IRowsetLocate, 0
45 IRowsetResynch, 0
46 IRowsetScroll, True
47 IRowsetUpdate,
48 ISupportErrorInfo, False
49 ILockBytes, False
50 ISequentialStream,
51 IStorage, False
52 IStream, True
53 IRowsetIndex, False
54 Column Set Notification, False
55 Row Delete Notification, True
56 Row First Change Notification, True
57 Row Insert Notification, True
58 Row Resynchronization Notification, True
59 Rowset Release Notification,
60 Rowset Fetch Position Change Notification,
61 Row Undo Change Notification,
62 Row Undo Delete Notification,
63 Row Undo Insert Notification,
64 Row Update Notification,
65 Append-Only Rowset,
66 Change Inserted Rows,
67 Return Pending Inserts,
68 IConvertType,
69 Notification Granularity,
70 Access Order,
71 Lock Mode,
72 Server Data on Insert,
73 IRowsetCurrentIndex,
74 Jet OLEDB:Validate Rules On Set,
75 Jet OLEDB:Fat Cursor Cache Size,
76 Jet OLEDB:Enable Fat Cursors,
77 Jet OLEDBartial Bulk Ops,
78 Jet OLEDBass Through Query Connect String,
79 Jet OLEDB:ODBC Pass-Through Statement,
80 Jet OLEDB:Grbit Value,
81 Jet OLEDB:Use Grbit,
82 Jet OLEDB:Stored Query,
83 Jet OLEDB:Locking Granularity,
84 Jet OLEDB:Bulk Transactions,
85 Jet OLEDB:Inconsistent,
86 Jet OLEDBass Through Query Bulk-Op,
87 Bookmarkable,

 

[clapper62]

It appears my thread is dying so I am starting to believe what I want to do isn't possible
anyway just a little more experimenting produced this

Database Type Microsoft Access (mdb)
Absolute Page -1
Absolute Position -1
BOF False
CacheSize 1
CursorLocation 2
CursorType 3
DataMember
EditMode 0
EOF False
Filter 0
Index
LockType 1
MarshalOptions 0
MaxRecords 0
PageCount -1
PageSize 10
RecordCount -1
Sort
Source
State 1
Status 0
StayInSync True


Database Type Microsoft SQL Server
Absolute Page 1
Absolute Position 1
BOF False
CacheSize 1
CursorLocation 2
CursorType 3
DataMember
EditMode 0
EOF False
Filter 0
Index
LockType 1
MarshalOptions 0
MaxRecords 0
PageCount 42
PageSize 10
RecordCount 413
Sort
Source
State 1
Status 0
StayInSync True

There are differences but they are either read only properties or they throw an error when I try to change them
both the .CursorLocation and the .CursorType values are the same between the 2 types of recordsets.
The RecordCount property is interesting though

 

[lameid]

Log the properties in a table and compare them with a query to see differences. If differences are Listed side by side something might jump out to someone. Likely not me to see it but nonetheless easier to read means more people will have the time to look.