Enumerating ADO Recordset Properties

[Lyn]

Hi,
This question may seem a bit academic...

To learn more about Access VBA, I have been enumerating the properties of
various form controls. This was mostly successful and I have learned a lot
from the process.

It occurred to me that I could also enumerate the properties of the ADO
Recordset in similar fashion, expecting to get back known properties such as
AbsolutePosition, BOF, EOF, Filter, Sort, etc. I inserted the following
into an existing piece of open recordset code:

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

This was successful to the extent that I got back a list of some 92
properties. But none of the expected properties was in the list. In fact I
have never heard of any of them, nor are they in the Access Help file. Here
is an extract of the list (the number on the left is the index):

0 Preserve on Abort, False

1 Blocking Storage Objects, True

2 Use Bookmarks, True

3 Skip Deleted Bookmarks, False

4 Bookmark Type, 1

5 Cache Deferred Columns, False

6 Fetch Backwards, True

7 Hold Rows, True

....

36 IAccessor, True

37 IColumnsInfo, True

38 IColumnsRowset, True

39 IConnectionPointContainer, True

40 IRowset, True

41 IRowsetChange, False

....

87 Jet OLEDB:Locking Granularity, 2

88 Jet OLEDB:Bulk Transactions, 0

89 Jet OLEDB:Inconsistent, True

90 Jet OLEDB:Pass Through Query Bulk-Op, False

91 Bookmarkable, True
To me this looks like I have found some sort of internal property list,
possibly between Access and the database provider (Jet in this case) -- a
list that normal developers would not access.

Can anyone confirm what this list is? Is there a way to enumerate the
"normal" ADO Recordset property list? If so, how?

A related question: Many controls (eg, TextBox, ComboBox) have the
properties .Value (default property) and .OldValue. However, these were not
returned in my enumerations. Are these properties outside the enumerated
lists? Or am I not doing this right? (I used the same code snippet above,
substituting the name of the form control under test for RS.)

Thanks in advance.

--
Cheers,
Lyn.

[DFS]

Lyn wrote:

Hi,
This question may seem a bit academic...

To learn more about Access VBA, I have been enumerating the
properties of various form controls. This was mostly successful and
I have learned a lot from the process.

It occurred to me that I could also enumerate the properties of the
ADO Recordset in similar fashion, expecting to get back known
properties such as AbsolutePosition, BOF, EOF, Filter, Sort, etc. I
inserted the following into an existing piece of open recordset code:

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

This was successful to the extent that I got back a list of some 92
properties. But none of the expected properties was in the list. In
fact I have never heard of any of them, nor are they in the Access
Help file. Here is an extract of the list (the number on the left is
the index):

0 Preserve on Abort, False

1 Blocking Storage Objects, True

2 Use Bookmarks, True

3 Skip Deleted Bookmarks, False

4 Bookmark Type, 1

5 Cache Deferred Columns, False

6 Fetch Backwards, True

7 Hold Rows, True

...

36 IAccessor, True

37 IColumnsInfo, True

38 IColumnsRowset, True

39 IConnectionPointContainer, True

40 IRowset, True

41 IRowsetChange, False

...

87 Jet OLEDB:Locking Granularity, 2

88 Jet OLEDB:Bulk Transactions, 0

89 Jet OLEDB:Inconsistent, True

90 Jet OLEDB:Pass Through Query Bulk-Op, False

91 Bookmarkable, True
To me this looks like I have found some sort of internal property
list, possibly between Access and the database provider (Jet in this
case) -- a list that normal developers would not access.

Can anyone confirm what this list is? Is there a way to enumerate the
"normal" ADO Recordset property list? If so, how?

A related question: Many controls (eg, TextBox, ComboBox) have the
properties .Value (default property) and .OldValue. However, these
were not returned in my enumerations. Are these properties outside
the enumerated lists? Or am I not doing this right? (I used the
same code snippet above, substituting the name of the form control
under test for RS.)

Thanks in advance.

Do you have SQL Server installed on your machine? Those are properties the
OLE DB Provider for SQL Server adds to some ADO objects.

http://msdn.microsoft.com/library/de...prg04_1ub7.asp

Will you do me a favor and check your References, and tell me which one, if
any, has OLE DB in it, and the path to the file

Thanks

[Lyn]

"DFS" <no****@DFS.com> wrote in message news:f_*****************@fe07.lga...

Do you have SQL Server installed on your machine? Those are properties
the
OLE DB Provider for SQL Server adds to some ADO objects.

http://msdn.microsoft.com/library/de...prg04_1ub7.asp

Will you do me a favor and check your References, and tell me which one,
if
any, has OLE DB in it, and the path to the file

Thanks

DFS,
Thanks for your response.

I don't have SQL Server installed. None of the listed OLE DB References is
checked as Available. The References I have are as they came installed in
Office (except MouseWheel which I added). These are:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Forms 2.0 Object Library
MouseWheel

Looking at the list, I note that there are several later versions of
Microsoft ActiveX Data Objects Library (up to 2.8) which are unchecked.
Should I have the latest version checked instead of (or as well as) version
2.1?

There are three (unchecked) References which include "OLE DB". These are:

Microsoft OLE DB provider for OLAP Services connection dialog 8.0
C:\Program Files\Common Files\SYSTEM\OLE DB\MSOLUI80.DLL
Microsoft OLE DB Service Component 1.0 Type Library
C:\Program Files\Common Files\System\Ole DB\oledb32.dll
OLE DB Errors Type Library
C:\Program Files\Common Files\System\Ole DB\oledb32.dll

Is this all OK?

--
Cheers,
Lyn.

[DFS]

Lyn wrote:

"DFS" <no****@DFS.com> wrote in message
news:f_*****************@fe07.lga...

Do you have SQL Server installed on your machine? Those are
properties the
OLE DB Provider for SQL Server adds to some ADO objects.

http://msdn.microsoft.com/library/de...prg04_1ub7.asp
Will you do me a favor and check your References, and tell me which
one, if
any, has OLE DB in it, and the path to the file

Thanks
DFS,
Thanks for your response.

I don't have SQL Server installed. None of the listed OLE DB
References is checked as Available. The References I have are as
they came installed in Office (except MouseWheel which I added).
These are:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Forms 2.0 Object Library
MouseWheel

Looking at the list, I note that there are several later versions of
Microsoft ActiveX Data Objects Library (up to 2.8) which are
unchecked. Should I have the latest version checked instead of (or as
well as) version
2.1?

I rarely use ADO, so I can't say, but I would imagine 2.8 has some goodies
not found in 2.1. If you're going to be developing and distributing client
systems in Access, you might need to be careful about which library
references you choose. There's a current cdma thread on just this subject,
matter of fact.

Those are the MS data access components MDAC
http://www.microsoft.com/downloads/d...DisplayLang=en

FYI, all those ActiveX library references point to C:\Program Files\Common
Files\System\ado.
There are three (unchecked) References which include "OLE DB". These
are:

Microsoft OLE DB provider for OLAP Services connection dialog 8.0
C:\Program Files\Common Files\SYSTEM\OLE DB\MSOLUI80.DLL
Microsoft OLE DB Service Component 1.0 Type Library
C:\Program Files\Common Files\System\Ole DB\oledb32.dll
OLE DB Errors Type Library
C:\Program Files\Common Files\System\Ole DB\oledb32.dll

Is this all OK?

Yes. You don't have anything to worry about.

I was trying to find out how you got those OLE DB properties available. I
found the .dll file C:\Program Files\Common Files\System\Ole
DB\sqloledb.dll, but couldn't add a reference to it in my VBA project. And
I have SQL Server installed. But I built my system, and as a rule I choose
custom installs and choose the minimum I need, so I most likely never
installed the OLE DB provider. I may go back and do that, 'cause some of
those properties you listed look handy.

Probably whoever installed your Office system just chose everything.

Thanks for the comprehensive response.

[Steve Jorgensen]

VB/VBA does not have any direct support for a Properties collection of a
generic object that contains the accessible properties of the object. Objects
that do have a Properties collection implement it themselves.

Often, a class will include in its properties collection, both some of the
items that are also available as "." properties of the class, and also some
properties that are determined at run-time. In the case of an ADO recordset,
the Properties collection may include properties that the specific ADO
provider makes available, but that the ADO library has no direct knowledge of.
These are mostly not undocumented, but documented for the provider, not for
ADO.

To see the standard properties (not Properties collection members) of an
object, simply go the the object browser by pressing F2. Some items are
normally hidden, so if you want to see those items as well, right-click in the
object browser, and select Show Hidden Members.

On Sat, 26 Feb 2005 15:20:54 +1100, "Lyn" <lh******@ihug.com.au> wrote:

Hi,
This question may seem a bit academic...

To learn more about Access VBA, I have been enumerating the properties of
various form controls. This was mostly successful and I have learned a lot
from the process.

It occurred to me that I could also enumerate the properties of the ADO
Recordset in similar fashion, expecting to get back known properties such as
AbsolutePosition, BOF, EOF, Filter, Sort, etc. I inserted the following
into an existing piece of open recordset code:

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

This was successful to the extent that I got back a list of some 92
properties. But none of the expected properties was in the list. In fact I
have never heard of any of them, nor are they in the Access Help file. Here
is an extract of the list (the number on the left is the index):

0 Preserve on Abort, False

1 Blocking Storage Objects, True

2 Use Bookmarks, True

3 Skip Deleted Bookmarks, False

4 Bookmark Type, 1

5 Cache Deferred Columns, False

6 Fetch Backwards, True

7 Hold Rows, True

...

36 IAccessor, True

37 IColumnsInfo, True

38 IColumnsRowset, True

39 IConnectionPointContainer, True

40 IRowset, True

41 IRowsetChange, False

...

87 Jet OLEDB:Locking Granularity, 2

88 Jet OLEDB:Bulk Transactions, 0

89 Jet OLEDB:Inconsistent, True

90 Jet OLEDB:Pass Through Query Bulk-Op, False

91 Bookmarkable, True
To me this looks like I have found some sort of internal property list,
possibly between Access and the database provider (Jet in this case) -- a
list that normal developers would not access.

Can anyone confirm what this list is? Is there a way to enumerate the
"normal" ADO Recordset property list? If so, how?

A related question: Many controls (eg, TextBox, ComboBox) have the
properties .Value (default property) and .OldValue. However, these were not
returned in my enumerations. Are these properties outside the enumerated
lists? Or am I not doing this right? (I used the same code snippet above,
substituting the name of the form control under test for RS.)

Thanks in advance.