Custom-made ADO recordsets and listboxes...

[James Cane]

Here's an interesting problem that someone might have an answer to...

Some time ago, I wrote a set of utility classes which wrap up the
custom row source function needed to add arbitrary items to a combo or
listbox. It all works nicely and allows me to do things such as
sorting by clicking on column headings.

Recently, all the machines here were upgraded to Access XP from 97 and
I thought it might be time to take advantage of the new ADO support.
As you might know, using a row source function is rather slow, even
with well optimised code, and scrolling through a large list box isn't
exactly the smoothest experience you can have. It's all a lot faster
if you can bind the listbox directly to some data.

So I wrote some code to create a disconnected recordset, populate it
with my data and attach it to a listbox. The thing is, I can get the
column headings to show correctly and I can even get the listbox to
display the correct number of rows, but all the contents of the rows
are blank, no matter what I do. Here's some simple code to
illustrate:

Create a new blank form. Add one list box and the following
procedure:

Private Sub Form_Load()

Dim objRS As ADODB.Recordset

Set objRS = New ADODB.Recordset

With objRS

Call .Fields.Append( "Item1", adChar, 255)
Call .Open

Call .AddNew
.Fields("Item1" ).Value = "Test Line 1"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 2"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 3"
Call .Update

Set .ActiveConnecti on = CurrentProject. Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnectio n property.
Access apparently doesn't support binding to disconnected recordsets.
I suspect that might be part of the problem, but I can't see why.
When you look at the contents of the recordset in code, it all seems
to have worked successfully.

Anybody got any ideas?

Thanks in advance!

James

[Gary Floam]

James,

You can fill a list box from a function, and the function can get its data
from an array. That may be what you are looking for.

Gary

"James Cane" <jw****@hotmail .com> wrote in message
news:d5******** *************** ***@posting.goo gle.com...

Here's an interesting problem that someone might have an answer to...

Some time ago, I wrote a set of utility classes which wrap up the
custom row source function needed to add arbitrary items to a combo or
listbox. It all works nicely and allows me to do things such as
sorting by clicking on column headings.

Recently, all the machines here were upgraded to Access XP from 97 and
I thought it might be time to take advantage of the new ADO support.
As you might know, using a row source function is rather slow, even
with well optimised code, and scrolling through a large list box isn't
exactly the smoothest experience you can have. It's all a lot faster
if you can bind the listbox directly to some data.

So I wrote some code to create a disconnected recordset, populate it
with my data and attach it to a listbox. The thing is, I can get the
column headings to show correctly and I can even get the listbox to
display the correct number of rows, but all the contents of the rows
are blank, no matter what I do. Here's some simple code to
illustrate:

Create a new blank form. Add one list box and the following
procedure:

Private Sub Form_Load()

Dim objRS As ADODB.Recordset

Set objRS = New ADODB.Recordset

With objRS

Call .Fields.Append( "Item1", adChar, 255)
Call .Open

Call .AddNew
.Fields("Item1" ).Value = "Test Line 1"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 2"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 3"
Call .Update

Set .ActiveConnecti on = CurrentProject. Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnectio n property.
Access apparently doesn't support binding to disconnected recordsets.
I suspect that might be part of the problem, but I can't see why.
When you look at the contents of the recordset in code, it all seems
to have worked successfully.

Anybody got any ideas?

Thanks in advance!

James

[Steve Jorgensen]

For terminology purposes, you might want to know that what you've created here
is called a fabricated recordset, not a disconnected recordset. A
disconnected recordset has to have at one time been connected.

Now, in Access 2000 and 2002, you can set a form's .Recordset (not
..RecordSource) property to an existing recordset with some restrictions such
as the fact that Access expects any ADO recordset to have used the special ADP
wrapper provider around a SQL Server connection, such as what
CurrentProject. ActiveConnectio n gives you in an ADP. Sometimes you can get
away with other kinds of ADO recordsets for read-only, but it's hit or miss,
and there are lots of don'ts if you don't want Access to crash.

Regarding combo and list boxes, unless they've added it in Access 2003, these
don't have the .Recordset property, so you can't set it.

Now, it is possible to set the Row Source Type (not Row Source) to the name of
a function that can be used to populate a listbox or combo box, and I've seen
this work very well. The function can be written to return data from a DAO or
ADO recordset, so it provides a way for these recordsets to be used as sources
for combo and list boxes. On busy networks, this can be a powerful thing
since you can apply a filter to a clone of an in-memory recordset pretty much
instantly on modern PC hardware.

On 12 Jan 2004 11:49:09 -0800, jw****@hotmail. com (James Cane) wrote:

Here's an interesting problem that someone might have an answer to...

Some time ago, I wrote a set of utility classes which wrap up the
custom row source function needed to add arbitrary items to a combo or
listbox. It all works nicely and allows me to do things such as
sorting by clicking on column headings.

Recently, all the machines here were upgraded to Access XP from 97 and
I thought it might be time to take advantage of the new ADO support.
As you might know, using a row source function is rather slow, even
with well optimised code, and scrolling through a large list box isn't
exactly the smoothest experience you can have. It's all a lot faster
if you can bind the listbox directly to some data.

So I wrote some code to create a disconnected recordset, populate it
with my data and attach it to a listbox. The thing is, I can get the
column headings to show correctly and I can even get the listbox to
display the correct number of rows, but all the contents of the rows
are blank, no matter what I do. Here's some simple code to
illustrate:

Create a new blank form. Add one list box and the following
procedure:

Private Sub Form_Load()

Dim objRS As ADODB.Recordset

Set objRS = New ADODB.Recordset

With objRS

Call .Fields.Append( "Item1", adChar, 255)
Call .Open

Call .AddNew
.Fields("Item1" ).Value = "Test Line 1"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 2"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 3"
Call .Update

Set .ActiveConnecti on = CurrentProject. Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnectio n property.
Access apparently doesn't support binding to disconnected recordsets.
I suspect that might be part of the problem, but I can't see why.
When you look at the contents of the recordset in code, it all seems
to have worked successfully.

Anybody got any ideas?

Thanks in advance!

James

[Roger]

this doesn't work with access97...
the two lines fail
Set .ActiveConnecti on = CurrentProject. Connection (undefined variable)
Set List1.Recordset = objRS (data member not found)

jw****@hotmail. com (James Cane) wrote in message news:<d5******* *************** ****@posting.go ogle.com>...

Here's an interesting problem that someone might have an answer to...

Some time ago, I wrote a set of utility classes which wrap up the
custom row source function needed to add arbitrary items to a combo or
listbox. It all works nicely and allows me to do things such as
sorting by clicking on column headings.

Recently, all the machines here were upgraded to Access XP from 97 and
I thought it might be time to take advantage of the new ADO support.
As you might know, using a row source function is rather slow, even
with well optimised code, and scrolling through a large list box isn't
exactly the smoothest experience you can have. It's all a lot faster
if you can bind the listbox directly to some data.

So I wrote some code to create a disconnected recordset, populate it
with my data and attach it to a listbox. The thing is, I can get the
column headings to show correctly and I can even get the listbox to
display the correct number of rows, but all the contents of the rows
are blank, no matter what I do. Here's some simple code to
illustrate:

Create a new blank form. Add one list box and the following
procedure:

Private Sub Form_Load()

Dim objRS As ADODB.Recordset

Set objRS = New ADODB.Recordset

With objRS

Call .Fields.Append( "Item1", adChar, 255)
Call .Open

Call .AddNew
.Fields("Item1" ).Value = "Test Line 1"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 2"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 3"
Call .Update

Set .ActiveConnecti on = CurrentProject. Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnectio n property.
Access apparently doesn't support binding to disconnected recordsets.
I suspect that might be part of the problem, but I can't see why.
When you look at the contents of the recordset in code, it all seems
to have worked successfully.

Anybody got any ideas?

Thanks in advance!

James

[James Cane]

Thanks Steve.

I'm aware of the RowSourceType property and its use in conjunction
with a RowSourceType function to return arbitrary data. In fact, this
is something I've been doing perfectly successfully. The only issue
really is speed - on a decent machine, with Access 2002, a 4-column,
100 row listbox doesn't scroll particularly quickly with a custom row
source, even if you're directly grabbing the data from a text array as
fast as you can. Consequently, I've been trying to play around with
alternative approaches (without using ActiveX controls or messing
around hosting Win32 listboxes using the Windows API)

As for the Recordset property of a listbox, I'd suggest you take a
look. There really is, in Access 2002 (XP) at least, such a property.
It's read/write and accepts either an ADO or DAO recordset. In fact,
it appears to work in exactly the same way as the Recordset property
of a form or report.

Unfortunately, I can't use an ADO recordset taken from SQL Server for
two reasons - the first is that I'm not using SQL server, but MDB
files (for many reasons, most political or historical and outside my
control). The second, more relevant reason is that my data does not
necessarily have to come from a database.

Thanks again for your comments though

Steve Jorgensen <no****@nospam. nospam> wrote in message news:<gd******* *************** **********@4ax. com>...

For terminology purposes, you might want to know that what you've created here
is called a fabricated recordset, not a disconnected recordset. A
disconnected recordset has to have at one time been connected.

Now, in Access 2000 and 2002, you can set a form's .Recordset (not
.RecordSource) property to an existing recordset with some restrictions such
as the fact that Access expects any ADO recordset to have used the special ADP
wrapper provider around a SQL Server connection, such as what
CurrentProject. ActiveConnectio n gives you in an ADP. Sometimes you can get
away with other kinds of ADO recordsets for read-only, but it's hit or miss,
and there are lots of don'ts if you don't want Access to crash.

Regarding combo and list boxes, unless they've added it in Access 2003, these
don't have the .Recordset property, so you can't set it.

Now, it is possible to set the Row Source Type (not Row Source) to the name of
a function that can be used to populate a listbox or combo box, and I've seen
this work very well. The function can be written to return data from a DAO or
ADO recordset, so it provides a way for these recordsets to be used as sources
for combo and list boxes. On busy networks, this can be a powerful thing
since you can apply a filter to a clone of an in-memory recordset pretty much
instantly on modern PC hardware.

On 12 Jan 2004 11:49:09 -0800, jw****@hotmail. com (James Cane) wrote:

Here's an interesting problem that someone might have an answer to...

Some time ago, I wrote a set of utility classes which wrap up the
custom row source function needed to add arbitrary items to a combo or
listbox. It all works nicely and allows me to do things such as
sorting by clicking on column headings.

Recently, all the machines here were upgraded to Access XP from 97 and
I thought it might be time to take advantage of the new ADO support.
As you might know, using a row source function is rather slow, even
with well optimised code, and scrolling through a large list box isn't
exactly the smoothest experience you can have. It's all a lot faster
if you can bind the listbox directly to some data.

So I wrote some code to create a disconnected recordset, populate it
with my data and attach it to a listbox. The thing is, I can get the
column headings to show correctly and I can even get the listbox to
display the correct number of rows, but all the contents of the rows
are blank, no matter what I do. Here's some simple code to
illustrate:

Create a new blank form. Add one list box and the following
procedure:

Private Sub Form_Load()

Dim objRS As ADODB.Recordset

Set objRS = New ADODB.Recordset

With objRS

Call .Fields.Append( "Item1", adChar, 255)
Call .Open

Call .AddNew
.Fields("Item1" ).Value = "Test Line 1"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 2"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 3"
Call .Update

Set .ActiveConnecti on = CurrentProject. Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnectio n property.
Access apparently doesn't support binding to disconnected recordsets.
I suspect that might be part of the problem, but I can't see why.
When you look at the contents of the recordset in code, it all seems
to have worked successfully.

Anybody got any ideas?

Thanks in advance!

James

[James Cane]

Thanks Roger,

That's right. Partly because Access 97 doesn't provide native support
for ADO, and partly because the Recordset property of a Listbox is not
available in 97. If you use Access 2002, and make sure your VB
project contains a reference to "Microsoft ActiveX Data Objects", you
should get my code to run.

le*********@nat pro.com (Roger) wrote in message news:<8c******* *************** ****@posting.go ogle.com>...

this doesn't work with access97...
the two lines fail
Set .ActiveConnecti on = CurrentProject. Connection (undefined variable)
Set List1.Recordset = objRS (data member not found)

jw****@hotmail. com (James Cane) wrote in message news:<d5******* *************** ****@posting.go ogle.com>...

Here's an interesting problem that someone might have an answer to...

Some time ago, I wrote a set of utility classes which wrap up the
custom row source function needed to add arbitrary items to a combo or
listbox. It all works nicely and allows me to do things such as
sorting by clicking on column headings.

Recently, all the machines here were upgraded to Access XP from 97 and
I thought it might be time to take advantage of the new ADO support.
As you might know, using a row source function is rather slow, even
with well optimised code, and scrolling through a large list box isn't
exactly the smoothest experience you can have. It's all a lot faster
if you can bind the listbox directly to some data.

So I wrote some code to create a disconnected recordset, populate it
with my data and attach it to a listbox. The thing is, I can get the
column headings to show correctly and I can even get the listbox to
display the correct number of rows, but all the contents of the rows
are blank, no matter what I do. Here's some simple code to
illustrate:

Create a new blank form. Add one list box and the following
procedure:

Private Sub Form_Load()

Dim objRS As ADODB.Recordset

Set objRS = New ADODB.Recordset

With objRS

Call .Fields.Append( "Item1", adChar, 255)
Call .Open

Call .AddNew
.Fields("Item1" ).Value = "Test Line 1"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 2"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 3"
Call .Update

Set .ActiveConnecti on = CurrentProject. Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnectio n property.
Access apparently doesn't support binding to disconnected recordsets.
I suspect that might be part of the problem, but I can't see why.
When you look at the contents of the recordset in code, it all seems
to have worked successfully.

Anybody got any ideas?

Thanks in advance!

James

[James Cane]

Thanks Gary,

That's what I've been doing up until now. I don't know if you've
noticed, but custom functions for listboxes aren't exactly hot
performers. When you scroll through a large list (particularly if
it's got several columns), it's a slow and somewhat jerky experience.

I was looking into the idea of using fabricated recordsets to speed it
all up a bit.

le*********@nat pro.com (Roger) wrote in message news:<8c******* *************** ****@posting.go ogle.com>...

this doesn't work with access97...
the two lines fail
Set .ActiveConnecti on = CurrentProject. Connection (undefined variable)
Set List1.Recordset = objRS (data member not found)

jw****@hotmail. com (James Cane) wrote in message news:<d5******* *************** ****@posting.go ogle.com>...

Here's an interesting problem that someone might have an answer to...

Some time ago, I wrote a set of utility classes which wrap up the
custom row source function needed to add arbitrary items to a combo or
listbox. It all works nicely and allows me to do things such as
sorting by clicking on column headings.

Recently, all the machines here were upgraded to Access XP from 97 and
I thought it might be time to take advantage of the new ADO support.
As you might know, using a row source function is rather slow, even
with well optimised code, and scrolling through a large list box isn't
exactly the smoothest experience you can have. It's all a lot faster
if you can bind the listbox directly to some data.

So I wrote some code to create a disconnected recordset, populate it
with my data and attach it to a listbox. The thing is, I can get the
column headings to show correctly and I can even get the listbox to
display the correct number of rows, but all the contents of the rows
are blank, no matter what I do. Here's some simple code to
illustrate:

Create a new blank form. Add one list box and the following
procedure:

Private Sub Form_Load()

Dim objRS As ADODB.Recordset

Set objRS = New ADODB.Recordset

With objRS

Call .Fields.Append( "Item1", adChar, 255)
Call .Open

Call .AddNew
.Fields("Item1" ).Value = "Test Line 1"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 2"
Call .AddNew
.Fields("Item1" ).Value = "Test Line 3"
Call .Update

Set .ActiveConnecti on = CurrentProject. Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnectio n property.
Access apparently doesn't support binding to disconnected recordsets.
I suspect that might be part of the problem, but I can't see why.
When you look at the contents of the recordset in code, it all seems
to have worked successfully.

Anybody got any ideas?

Thanks in advance!

James

[Steve Jorgensen]

On 13 Jan 2004 02:22:25 -0800, jw****@hotmail. com (James Cane) wrote:

Thanks Steve.

I'm aware of the RowSourceType property and its use in conjunction
with a RowSourceType function to return arbitrary data. In fact, this
is something I've been doing perfectly successfully. The only issue
really is speed - on a decent machine, with Access 2002, a 4-column,
100 row listbox doesn't scroll particularly quickly with a custom row
source, even if you're directly grabbing the data from a text array as
fast as you can. Consequently, I've been trying to play around with
alternative approaches (without using ActiveX controls or messing
around hosting Win32 listboxes using the Windows API)
In my opinion, a list box with much more than 100 rows is being abused, no
matter what kind of source it has. Perhaps, some sort of drill-down approach
would make more sense. You could do something like having a text box used to
progressively filter a recordset, and show just the first 50 rows in a list
box below. I've seen this technique used in an app before, and it seemed to
work quite well.
As for the Recordset property of a listbox, I'd suggest you take a
look. There really is, in Access 2002 (XP) at least, such a property.
It's read/write and accepts either an ADO or DAO recordset. In fact,
it appears to work in exactly the same way as the Recordset property
of a form or report.

I'll certainly check again. I was pretty sure I had tried that in Access 2002
before, and there was no such property, but since you have just been playing
with this, I assume you are correct.

[James Cane]

In general, I'd agree about the 100 row listbox thing, but in this
context I'm using a large listbox like a grid control. The users
require all the data to be available at once (generally no more than
200-300 rows) in a scrollable form. I can't use a grid control, as
I'm not able to use any ActiveX controls (politics again - can't
install them on the target machines!), and I don't even have a
ListView or FlexGrid available for use. The listbox is the best I can
do unfortunately.

Interestingly, the speed doesn't vary particularly with the size of
the dataset, once you've loaded the data into memory, as the custom
row function only calls on demand for the data it needs to display.
Consequently, the speed is really more a function of the number of
columns and rows visible at once (the size of the listbox control).

Steve Jorgensen <no****@nospam. nospam> wrote in message news:<tl******* *************** **********@4ax. com>...

On 13 Jan 2004 02:22:25 -0800, jw****@hotmail. com (James Cane) wrote:

Thanks Steve.

I'm aware of the RowSourceType property and its use in conjunction
with a RowSourceType function to return arbitrary data. In fact, this
is something I've been doing perfectly successfully. The only issue
really is speed - on a decent machine, with Access 2002, a 4-column,
100 row listbox doesn't scroll particularly quickly with a custom row
source, even if you're directly grabbing the data from a text array as
fast as you can. Consequently, I've been trying to play around with
alternative approaches (without using ActiveX controls or messing
around hosting Win32 listboxes using the Windows API)

In my opinion, a list box with much more than 100 rows is being abused, no
matter what kind of source it has. Perhaps, some sort of drill-down approach
would make more sense. You could do something like having a text box used to
progressively filter a recordset, and show just the first 50 rows in a list
box below. I've seen this technique used in an app before, and it seemed to
work quite well.

As for the Recordset property of a listbox, I'd suggest you take a
look. There really is, in Access 2002 (XP) at least, such a property.
It's read/write and accepts either an ADO or DAO recordset. In fact,
it appears to work in exactly the same way as the Recordset property
of a form or report.

I'll certainly check again. I was pretty sure I had tried that in Access 2002
before, and there was no such property, but since you have just been playing
with this, I assume you are correct.

[Steve Jorgensen]

On 13 Jan 2004 07:15:43 -0800, jw****@hotmail. com (James Cane) wrote:

In general, I'd agree about the 100 row listbox thing, but in this
context I'm using a large listbox like a grid control. The users
require all the data to be available at once (generally no more than
200-300 rows) in a scrollable form. I can't use a grid control, as
I'm not able to use any ActiveX controls (politics again - can't
install them on the target machines!), and I don't even have a
ListView or FlexGrid available for use. The listbox is the best I can
do unfortunately.

Like a grid control - why a listbox, then? Why not use a form in datasheet
view or continuous form formatted like a grid control?