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 .ActiveConnection = CurrentProject.Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnection 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.google.c om...

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 .ActiveConnection = CurrentProject.Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnection 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.ActiveConnection 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 .ActiveConnection = CurrentProject.Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnection 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 .ActiveConnection = CurrentProject.Connection (undefined variable)
Set List1.Recordset = objRS (data member not found)

jw****@hotmail.com (James Cane) wrote in message news:<d5**************************@posting.google. 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 .ActiveConnection = CurrentProject.Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnection 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.ActiveConnection 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 .ActiveConnection = CurrentProject.Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnection 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*********@natpro.com (Roger) wrote in message news:<8c**************************@posting.google. com>...

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

jw****@hotmail.com (James Cane) wrote in message news:<d5**************************@posting.google. 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 .ActiveConnection = CurrentProject.Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnection 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*********@natpro.com (Roger) wrote in message news:<8c**************************@posting.google. com>...

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

jw****@hotmail.com (James Cane) wrote in message news:<d5**************************@posting.google. 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 .ActiveConnection = CurrentProject.Connection
End With

Set List1.Recordset = objRS
End Sub

In case you're wondering, I have to set the ActiveConnection 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?

[Albert D. Kallal]

Are you talking about using the call back function?

I find if you have a reocrdset, or even a array of data, the call function
simply screams in terms of performance.

If you are talking bout a long delimitated string like:

one;two;three

Etc, that approach is slow as a dog when it get large. Further, it is
limited to 2000 characters anyway.

I would give the call back function a try, as it performs very well. You can
shove you data into a reocrdset, or even an array, and them map that data
right into the combo box, and NO long string is involved.

Check out:

http://www.mvps.org/access/forms/frm0049.htm

I used the call back with both array data (which can be mapped directly to
the combo box), and also with reocredsets via the absolute position (I don't
think you have that in ado). However, either approach for a thousand entries
is absolute instant in terms of performance.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
No************@msn.com
http://www.attcanada.net/~kallal.msn

[Dimitri Furman]

On Jan 13 2004, 05:41 am, Steve Jorgensen <no****@nospam.nospam> wrote
in news:tl********************************@4ax.com:

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,

It does exist, and I successfully used it to bind ADO recordsets that came
from SQL Server to combo boxes (one caveat is that the Recordset property
is set to Nothing until you actually access the combo's list in some way,
e.g. drop it down).

James,

As to your original question, I'd try binding a listbox to a disconnected
ADO recordset (not a fabricated one), which at some point was opened from
an actual data source (MDB table, etc.) If that works, you may try using
Steve's function that makes a fabricated recordset look as if it was once
connected:

http://groups.google.com/groups?selm...03b9gvb72b%404
ax.com

--
(remove a 9 to reply by email)

[Lyle Fairfield]

"Albert D. Kallal" <pl********************@msn.com> wrote in
news:C22Nb.82744$JQ1.64841@pd7tw1no:

I used the call back with both array data (which can be mapped directly
to the combo box), and also with reocredsets via the absolute position
(I don't think you have that in ado).

I have it here.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[James Cane]

Yes I am talking about the callback function. I've just always found
it to be extremely slow.

I've got a wrapper class for the callback function which allows me to
add arbitrary data and / or SQL select statements. I have also
implemented "click heading to sort" functionality. It all works quite
well, just slowly!

Internally, the wrapper class is caching its data in a two-dimensional
native string array, which it reads directly when the callback
requires data, so there's very little code running in the callback
itself and it should all be as fast as possible.

It's just been my experience that Access is inherently slow when using
the callback to load a listbox. I've even found that to be the case
when I hard-code a brain-dead callback function to always return a
string constant.

"Albert D. Kallal" <pl********************@msn.com> wrote in message news:<C22Nb.82744$JQ1.64841@pd7tw1no>...

Are you talking about using the call back function?

I find if you have a reocrdset, or even a array of data, the call function
simply screams in terms of performance.

If you are talking bout a long delimitated string like:

one;two;three

Etc, that approach is slow as a dog when it get large. Further, it is
limited to 2000 characters anyway.

I would give the call back function a try, as it performs very well. You can
shove you data into a reocrdset, or even an array, and them map that data
right into the combo box, and NO long string is involved.

Check out:

http://www.mvps.org/access/forms/frm0049.htm

I used the call back with both array data (which can be mapped directly to
the combo box), and also with reocredsets via the absolute position (I don't
think you have that in ado). However, either approach for a thousand entries
is absolute instant in terms of performance.

[James Cane]

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

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?

This is an upgrade to existing library code and it's got to be
backwards compatible with what's already there.

The original reason for using a listbox was that a datasheet form has
no equivalent to the row source callback, so you have to bind it to a
dataset. In Access XP, that might be solvable by creating your own
recordset and binding to it, but 97 didn't have that ability so you
could only bind a form to the results of a database query.

[James Cane]

Thanks Dimitri

Unfortunately, you can't bind a listbox to a disconnected recordset -
I've already tried! In fact, it's explicitly forbidden by the
documentation.

Actually, the reason for my original question was that I was having
trouble making a fabricated recordset appear to be connected. I can
successfully define the recordset, add some data and then set a
connection. I can even bind it to a listbox without receiving errors.
By all accounts, the recordset is now a standard connected recordset.

The problem is that the listbox just refuses to show data in the
recordset - it shows an equivalent number of blank rows instead.
Bizzarely, looking at the recordset in code shows that it does have
the correct data - all the field objects return the proper values,
recordset navigation works correctly, etc.

Weird huh?

It must be something not available to VBA that the listbox needs, but
isn't set correctly.

Reading through some back posts, I've seen mention of an interesting
technique described by Steve, whereby you stream out the recordset to
XML, modify the XML stream and reload it. This sounds like fun,
although possibly overkill, but I might have a play around. One
possiblity might be creating recordsets directly by building the
appropriate XML.

Dimitri Furman <df*****@cloud99.net> wrote in message news:<Xn****************************@199.184.165.2 39>...

James,

As to your original question, I'd try binding a listbox to a disconnected
ADO recordset (not a fabricated one), which at some point was opened from
an actual data source (MDB table, etc.) If that works, you may try using
Steve's function that makes a fabricated recordset look as if it was once
connected:

http://groups.google.com/groups?selm...03b9gvb72b%404
ax.com

[David W. Fenton]

pl********************@msn.com (Albert D. Kallal) wrote in
<C22Nb.82744$JQ1.64841@pd7tw1no>:

I find if you have a reocrdset, or even a array of data, the call
function simply screams in terms of performance.

That depends on how big the result set returned happens to be.

I once implemented a very flexible unbound editing procedure using
callback functions to return records of tables with one row of the
listbox being one field of data. The tables had about 100 fields
(it was actually a join between two 1:1 tables), and there was a
noticable lag in populating the listbox. Part of that was the
nature of what the callback function was doing, which involved a
couple of recordsets, not just one.

But I've always found callback functions slower than SQL.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

jw****@hotmail.com (James Cane) wrote in
<d5**************************@posting.google.com >:

It's just been my experience that Access is inherently slow when
using the callback to load a listbox. I've even found that to be
the case when I hard-code a brain-dead callback function to always
return a string constant.

This has been my experience, as well.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Dimitri Furman]

On Jan 14 2004, 07:37 am, jw****@hotmail.com (James Cane) wrote in
news:d5*************************@posting.google.co m:

Unfortunately, you can't bind a listbox to a disconnected recordset -
I've already tried! In fact, it's explicitly forbidden by the
documentation.
Hm. Could you provide a reference? I'm asking because I am actually binding
disconnected ADO recordsets to combo boxes in an A2K2 app - it works fine,
so I'm wondering what the docs say about this. I guess list boxes might be
different, although I'd be surprised if this were the case. My code looks
like this, where cm is a Command object associated with a stored procedure
on SQL Server:

rs.CursorLocation = adUseClient
rs.Open cm, , adOpenForwardOnly, adLockReadOnly
If rs.State = adStateOpen Then
Set rs.ActiveConnection = Nothing
Set Me.cboUsername.Recordset = rs
End If
Actually, the reason for my original question was that I was having
trouble making a fabricated recordset appear to be connected. I can
successfully define the recordset, add some data and then set a
connection. I can even bind it to a listbox without receiving errors.
By all accounts, the recordset is now a standard connected recordset.
I'm not so sure. Just setting a connection does not necessarily make it a
true disconnected recordset in Access' eyes. More on this below.
The problem is that the listbox just refuses to show data in the
recordset - it shows an equivalent number of blank rows instead.
Bizzarely, looking at the recordset in code shows that it does have
the correct data - all the field objects return the proper values,
recordset navigation works correctly, etc.
Does the listbox itself contain valid data? In other words, what happens if
you look at List1.Column(0)? I've seen some weird display issues with
invisible text, although never with list boxes.

Also, can you try binding it to a combo box instead?
Reading through some back posts, I've seen mention of an interesting
technique described by Steve, whereby you stream out the recordset to
XML, modify the XML stream and reload it. This sounds like fun,
although possibly overkill, but I might have a play around. One
possiblity might be creating recordsets directly by building the
appropriate XML.
I'd definitely try that. If you look into details of what Steve does,
you'll see that he is adding some attributes that are present in a
recordset that was once based on real data, but are not present in a
fabricated recordset, to fool Access into believing that it is dealing with
a true disconnected recordset (which in my experience does work, at least
with combos).
Dimitri Furman <df*****@cloud99.net> wrote in message
news:<Xn****************************@199.184.165.2 39>...

James,

As to your original question, I'd try binding a listbox to a
disconnected ADO recordset (not a fabricated one), which at some
point was opened from an actual data source (MDB table, etc.) If that
works, you may try using Steve's function that makes a fabricated
recordset look as if it was once connected:

http://groups.google.com/groups?selm...6cr3u03b9gvb72
b%404 ax.com

--
(remove a 9 to reply by email)

[Steve Jorgensen]

On 15 Jan 2004 02:47:33 GMT, Dimitri Furman <df*****@cloud99.net> wrote:

On Jan 14 2004, 07:37 am, jw****@hotmail.com (James Cane) wrote in
news:d5*************************@posting.google.c om:

Unfortunately, you can't bind a listbox to a disconnected recordset -
I've already tried! In fact, it's explicitly forbidden by the
documentation.
Hm. Could you provide a reference? I'm asking because I am actually binding
disconnected ADO recordsets to combo boxes in an A2K2 app - it works fine,
so I'm wondering what the docs say about this. I guess list boxes might be
different, although I'd be surprised if this were the case. My code looks
like this, where cm is a Command object associated with a stored procedure
on SQL Server:

rs.CursorLocation = adUseClient
rs.Open cm, , adOpenForwardOnly, adLockReadOnly
If rs.State = adStateOpen Then
Set rs.ActiveConnection = Nothing
Set Me.cboUsername.Recordset = rs
End If

Have you made sure Access is not actually reconnecting the recordest? I've
had this problem (among others) trying to use disconnected recordsets with
forms. If you, for instance, change the form's filter (not ServerFilter), or
any number of other similar activities that should not require requerying, you
find that Access has reconnected the recordset.

....I'd definitely try that. If you look into details of what Steve does,
you'll see that he is adding some attributes that are present in a
recordset that was once based on real data, but are not present in a
fabricated recordset, to fool Access into believing that it is dealing with
a true disconnected recordset (which in my experience does work, at least
with combos).

Neat. And it shows real data? It doesn't try to reconnect the recordset and
become confused that there's no real table/data back there?

By the way, experience leads me to avoid any techniques along this line in a
real production app since one wrong step can lead to a hard crash. Since
disconnected recordsets are not officially supported as sources, things that
work in testing might well be expected to blow up after a service pack install
or an MDAC update.

[Dimitri Furman]

On Jan 15 2004, 12:19 am, Steve Jorgensen <no****@nospam.nospam> wrote
in news:0h********************************@4ax.com:

Have you made sure Access is not actually reconnecting the recordest?
I've had this problem (among others) trying to use disconnected
recordsets with forms. If you, for instance, change the form's filter
(not ServerFilter), or any number of other similar activities that
should not require requerying, you find that Access has reconnected
the recordset.
Yes, I remember you mentioned that before. I doubt Access does it in this
particular case though. The recordset is based on a parameterized SP, there
is no direct access to tables, so Access would have to be pretty smart to
figure out on its own how to reconnect the recordset. I'll run a trace
though to double-check.
...

I'd definitely try that. If you look into details of what Steve does,
you'll see that he is adding some attributes that are present in a
recordset that was once based on real data, but are not present in a
fabricated recordset, to fool Access into believing that it is dealing
with a true disconnected recordset (which in my experience does work,
at least with combos).
Neat. And it shows real data? It doesn't try to reconnect the
recordset and become confused that there's no real table/data back
there?

Nope, real data, it behaves just like a regular combo would. I've seen it
try to reconnect in case of a recordset bound to a form, it doesn't seem to
do it here.
By the way, experience leads me to avoid any techniques along this
line in a real production app since one wrong step can lead to a hard
crash. Since disconnected recordsets are not officially supported as
sources, things that work in testing might well be expected to blow up
after a service pack install or an MDAC update.

All true, but sometimes the temptation is irresitible <g>

--
(remove a 9 to reply by email)

[Dimitri Furman]

On Jan 15 2004, 07:56 am, Dimitri Furman <df*****@cloud99.net> wrote in
news:Xn****************************@199.184.165.23 9:

On Jan 15 2004, 12:19 am, Steve Jorgensen <no****@nospam.nospam> wrote
in news:0h********************************@4ax.com:

Have you made sure Access is not actually reconnecting the recordest?
I've had this problem (among others) trying to use disconnected
recordsets with forms. If you, for instance, change the form's
filter (not ServerFilter), or any number of other similar activities
that should not require requerying, you find that Access has
reconnected the recordset.

Yes, I remember you mentioned that before. I doubt Access does it in
this particular case though. The recordset is based on a parameterized
SP, there is no direct access to tables, so Access would have to be
pretty smart to figure out on its own how to reconnect the recordset.
I'll run a trace though to double-check.

I looked at the trace - it doesn't reconnect the recordset behind the
scenes. The SP runs only once, and that user account has no direct access
to SQL Server tables. But I did see how Access tries to reconnect when you
filter a disconnected recordset bound to a form.

--
(remove a 9 to reply by email)

[Graeme]

I apologise for being a little late to catch up on this one but i've got a
small contribution:

I've found when binding fabricated (and disconnected) recordsets to
listboxes, that edited (or new) data doesn't appear in the list box until
you submit 'updatebatch'

With a connection this updates all pending records, without a connection
this updates all pending records in your recordset but nothing actually
happens to the live data. [this obviously doesn't matter if it's a
fabricated recordset]

I then refresh.the listbox by re-establish the linke....

Set me.lstChemical.Recordset = rsC [rsC is a disconnected Recordset]
My question is:
If i'm using a disconnected recordset to hold a small amount of data (say
30-60 recors) , i'd like to use a simple list box to view the records. I
then perform updates deletes and inserts and then use Updatebatch when the
user is finished to submit his/her changes to the database [my users could
be connecting in over a very slow link] Currently i have to perform
establish a connection and then "updatebatch " to get the data to appear
correctly [current ] in the list box.. is there a better way of getting the
data in the list box in the first place?

Many thanks

Graeme.