Problem with adp subform

[Alexander Jagl]

Hi everyone!

I have two problems with a subform in an adp project. (Access 2002
SP3, SQL Server 2000)
table 'customer': ID (primary key), first_name, last_name, ... (about
5000 records)
table 'order': ID (primary key), customerID, order_date, ... (about
25000 records)
Form 'f_customer' is based on a view 'v_customer'.
(SELECT ID, first_name, last_name FROM customer)
Subfrom 'f_orders' is on a view 'v_orders'.
(SELECT ID, customerID, order_date FROM order)
Child / Master link are set to customerID and ID

Problem 1:
If I open the main form the first customer and his orders are shown
correctly. Then if I jump to the last customer, the synchronisation
does not work, no orders are displayed. If I jump to record 4999 and
then again to 5000, the orders are displayed.

I tried different recordsources for the subform: A view with
child/master link, a stored procedure with an input parameter.

Problem 2:
Sometimes, not allways, if I open the form and jump to the last
record, Access quits with no error message. I have no special code in
the two forms. This happens with the view and the stored procedure.

Both erros only happen when I open the form and then jump to the last
customer record. If I jump from record 1, 2, 3 to 10 and then jump to
the last customer everything is ok.

Thanks for your help,
Alex

 

[Steve Jorgensen]

I can't tell you for certain, but what I can tell you is that opening forms
bound to recordsets with 1000s of records is a horrible abuse of a
Client/Server architecture. In C/S, the idea is to let the server do
searching, sorting, and aggregateing, and return a few rows to the client. If
you return 1000s of rows, you are using vast amounts of unnecessary network
bandwidth.

 

[JOe]

I use stored procedures in SQL 2000 to do most of the
work, and just have my final record set come into my
ADPs. I return 10,000 records o=in some of my queries
wityh no issues.

 

[Peter Kaufman]

Hi,

I agree that it is better to not return so many records - this is
exactly one of the reasons why.

OTOH, 5,000 records shouldn't cause the crash. I would look first at
the network hardware/traffic. Is the hardware modern (switch, not hub)
and that there are no other hardware issues.

Peter

 

[Steve Jorgensen]

I use stored procedures in SQL 2000 to do most of the
work, and just have my final record set come into my
ADPs. I return 10,000 records o=in some of my queries
wityh no issues.

OK, you're lucky for now. You are still abusing your network bandwidth by
doing this.