ADO and listboxes

[Chris Strug]

Hi,

Simple question, is it possible to use an ADO created recordset as a source
for a listbox in Access 2000?

if I try it at the minute (listbox.rowsource = rst) then I get an type
mismatch error.

Is this possible or am I talking out of my rear end?

Thanks

Chris Strug

 

[Vadim Rapp]

CS> Simple question, is it possible to use an ADO created recordset as
CS> a source for a listbox in Access 2000?

set listbox.recordset=rst

However, attempts like this - forcing Access to use pre-built ADO objects
instead of letting it build its own - is a straight way to crashes. Avoid.

Vadim

 

[Steve Jorgensen]

Cheers Vadim,

Let me explain my problem. I have a purchase order database. A PO consists
of several line items. What I want to do is to create a PO and let the user
add line items one by one. These line items are added into a temporary ADO
recordset. When all the line items have been included the system will create
the PO record and all the line items from the temporary recordset and
generate the PO ID.

However, as I can't use local tables in an ADP project, I wanted some way of
organising the line items before they are included in their table.

I hope this explains why, and if you know a better way feel free to let me
know.

Your time is appreciated. Many thanks

Chris Strug

Unless you have a really slow link or a really heavy server load (and I mean
-really- heavy), I usually just recommend using batch tables. Everyone makes
their edits into a table filtering on their own batch ID, then you run a stored
procedure to post the batch. The batch table can also double as a historical
transaction log.

 

[Chris Strug]

Unless you have a really slow link or a really heavy server load (and I mean
-really- heavy), I usually just recommend using batch tables. Everyone makes
their edits into a table filtering on their own batch ID, then you run a stored
procedure to post the batch. The batch table can also double as a historical
transaction log.

Thanks for the reply.

I'm sorry I'm still trying to get the hang of ADp / SQL Server projects. If
I could just run through what you suggested...

1. Create a new table on the server. This table takes the same structure as
the current line items table with an additional field containing the batch
ID of the current set of items.

2. When users enter a line item, it is included in the batch table which is
sourced to my text box filtered on this particular ID.

3. When a user has entered all the line items, the records in the batch
table for this order are included in the line item table proper....

I think I can do it....

This system isn't going to get that big (I'd say it won't get over 10K
records), I just want the practice of developing an ADP project!

However, thanks for your help. Please let me know if my understanding is
wrong! I'll let you know how I get on.

Thanks

Chris.

 

[BJ Freeman]

However, as I can't use local tables in an ADP project, I wanted some way of
organizing the line items before they are included in their table.

Chris:
You can use user specific tables. this is about the same except for the
network traffic.
also you can use a stored procedure to access those tables.

if you watch a ado recordset being access thru the profiler, you can see
what I mean.
using a SP will reduce your network traffic.

 

[Steve Jorgensen]

Thanks for the reply.

I'm sorry I'm still trying to get the hang of ADp / SQL Server projects. If
I could just run through what you suggested...

1. Create a new table on the server. This table takes the same structure as
the current line items table with an additional field containing the batch
ID of the current set of items.

2. When users enter a line item, it is included in the batch table which is
sourced to my text box filtered on this particular ID.

3. When a user has entered all the line items, the records in the batch
table for this order are included in the line item table proper....

I think I can do it....

This system isn't going to get that big (I'd say it won't get over 10K
records), I just want the practice of developing an ADP project!

However, thanks for your help. Please let me know if my understanding is
wrong! I'll let you know how I get on.

Thanks

That's pretty much how it goes. You have to map the foreign key relationships
generated in the batch tables to the live data tables, and you can either do
that with really hairy stored procedures, or you can just make the fields in the
master tables not IDENTITY columns, and copy the IDs from the batches.

I'm not saying this is all necessarily simple. If you need to do edits and
deletions in a batch, you have to implement a 3-step post (delete removed
records, update modified records, then add new records), and you might have to
implement a custom optimistic locking scheme to prevent one person's updates
from overwriting another person's updates. Still, this method tends to work out
the best in the end.

 

[Vadim Rapp]

CS> Let me explain my problem. I have a purchase order database. A PO
CS> consists of several line items. What I want to do is to create a
CS> PO and let the user add line items one by one. These line items
CS> are added into a temporary ADO recordset. When all the line items
CS> have been included the system will create the PO record and all
CS> the line items from the temporary recordset and generate the PO
CS> ID.

I wouldn't bother with the temporary recordset or tables. Have a subform for
the line items, have the users create them one by one, using regular Access
means. Access will be creating new records in the table.

A separate "flag" bit column in the orders' table would indicate an incomplete
order - having server default value 1 by default; set to 0 when the order is
placed. You don't process orders with flag=1. Then, some batch script run after
hours deletes all order records (and their line items) with flag=1. Or,
depending on your application, you might even hold for several days, in case
the user comes back - then he'll find his "basket" in place.

I think this way will save you dozens of hours of coding all these intermediate
structures, and make the project much more straightforward.

regards,

Vadim