Fastest way to open a recordset

[Jan]

Hi,
What is the fasted way to open a form with a filtered set of records
(possibly 100 from a total of 5000) in a multi-user database over a network.
The program is Access 2000. The OS is Win2000. The database is split into
a back end and front end.

Currently I have the form opening with a dialog box where the user puts in
part or all of a customer name. Then a button opens the real form in
datasheet view showing only 2 fields. The user scrolls down until the
intended name is found then double clicks to open the form in form view.
The user then must be able to scroll back and forth through the recordset in
form view.

Should I based the recordsource of the form on a table, a query in the
recordsource property of the form or is there a faster way. Also, is the
form structure I've chosen the best option.

Of course it works great on my pc but slows on a network.

Thanks in advance for any and all help.
Jan

 

[Pavel Romashkin]

It might be best to do away with the luxury or letting the user scroll
through the names. This will limit the traffic required to send all the
names over to the FE machine. For those who can't spell, put in a Lookup
button that will do a LIKE search.
Another trick is to use SELECT TOP queries to fill in only the first
screenfull of records instead of SELECT * that will transfer the whole table.

Pavel

 

[Jonathan Parminter]

-----Original Message-----
Hi,
What is the fasted way to open a form with a filtered set of records
(possibly 100 from a total of 5000) in a multi-user database over a network.
The program is Access 2000. The OS is Win2000. The database is split into
a back end and front end.

Currently I have the form opening with a dialog box where the user puts in
part or all of a customer name. Then a button opens the real form in
datasheet view showing only 2 fields. The user scrolls down until the
intended name is found then double clicks to open the form in form view.
The user then must be able to scroll back and forth through the recordset in
form view.

Should I based the recordsource of the form on a table, a query in the
recordsource property of the form or is there a faster way. Also, is the
form structure I've chosen the best option.

Of course it works great on my pc but slows on a network.

Thanks in advance for any and all help.
Jan

Hi Jan,
I suggest that you do all the record selection in the
dialog box. Then change the record source of the form to a
select statement that returns just the selected record/s.
Users navigate using dialog only unless have selected
multiple records.

Faster as, usually it returns a single record, or at least
few records. Reduces likelihood of record lock conflicts.

The form that is opened is not bound to a record source.

' code in dialog
dim strSQL as string

strSQL="SELECT * FROM qrySource " _
& "WHERE ([PK]=" & lstSelection & ");"

docmd.openform FormName:="myForm", OpenArgs:=strSQL

' code in form
private sub Form_Load()
me.recordsource=me.OpenArgs
end sub

' qrySource is name of unfiltered query
' PK is Primary Key
' lstSelection is listbox name
' myForm is form name

Luck
Jonathan

 

[Jan]

Hi Jonathan,
Thanks for the prompt reply.

I have tried your suggestion and I'm doing something wrong. My dialog form
is not bound and I made the second form unbound. Followed your instruction
to put the me.recordsource = me.openarg in the Load property of the second
form. It just asks me twice to enter a name and then opens the second form
blank.
Have I left out a step?
Thanks.
Jan

-----Original Message-----
Hi,
What is the fasted way to open a form with a filtered set of records
(possibly 100 from a total of 5000) in a multi-user database over a network.
The program is Access 2000. The OS is Win2000. The database is split into
a back end and front end.

Currently I have the form opening with a dialog box where the user puts in
part or all of a customer name. Then a button opens the real form in
datasheet view showing only 2 fields. The user scrolls down until the
intended name is found then double clicks to open the form in form view.
The user then must be able to scroll back and forth through the recordset in
form view.

Should I based the recordsource of the form on a table, a query in the
recordsource property of the form or is there a faster way. Also, is the
form structure I've chosen the best option.

Of course it works great on my pc but slows on a network.

Thanks in advance for any and all help.
Jan

Hi Jan,
I suggest that you do all the record selection in the
dialog box. Then change the record source of the form to a
select statement that returns just the selected record/s.
Users navigate using dialog only unless have selected
multiple records.

Faster as, usually it returns a single record, or at least
few records. Reduces likelihood of record lock conflicts.

The form that is opened is not bound to a record source.

' code in dialog
dim strSQL as string

strSQL="SELECT * FROM qrySource " _
& "WHERE ([PK]=" & lstSelection & ");"

docmd.openform FormName:="myForm", OpenArgs:=strSQL

' code in form
private sub Form_Load()
me.recordsource=me.OpenArgs
end sub

' qrySource is name of unfiltered query
' PK is Primary Key
' lstSelection is listbox name
' myForm is form name

Luck
Jonathan

 

[Jonathan Parminter]

Hi Jan,
I'm assuming that the dialog successfully allows a user to
select a customer name.

What you need to do is check that the query (qrySource, to
continue with the example) has the fields that are
required for your select statement and for the form you
are calling.

A method that you can use is to set the form recordsource
to the query (qrySource) and then open the form. If you
still get the prompts for the name, you then need to
consider whether to remove the name field from the form or
to add the name field to the query (qrySource).

Once you get this working then remove this query from the
recordsource of the form.

note: using keywords such as 'name' and 'date' as field
names is not good practice as these can cause ambiguous
references to object properties and methods that have the
same name. Plus, it is better to perhaps combine with the
table name or purpose name. Also consider not having
spaces and non-text characters in name.

-----Original Message-----
Hi Jonathan,
Thanks for the prompt reply.

I have tried your suggestion and I'm doing something wrong. My dialog form
is not bound and I made the second form unbound. Followed your instruction
to put the me.recordsource = me.openarg in the Load property of the second
form. It just asks me twice to enter a name and then opens the second form
blank.
Have I left out a step?
Thanks.
Jan

"Jonathan Parminter"

-----Original Message-----
Hi,
What is the fasted way to open a form with a filtered

set
of records

(possibly 100 from a total of 5000) in a multi-user database over a network.
The program is Access 2000. The OS is Win2000. The database is split into
a back end and front end.

Currently I have the form opening with a dialog box

where
the user puts in

part or all of a customer name. Then a button opens

the
real form in

datasheet view showing only 2 fields. The user scrolls down until the
intended name is found then double clicks to open the form in form view.
The user then must be able to scroll back and forth through the recordset in
form view.

Should I based the recordsource of the form on a

table, a
query in the

recordsource property of the form or is there a faster way. Also, is the
form structure I've chosen the best option.

Of course it works great on my pc but slows on a network.

Thanks in advance for any and all help.
Jan

Hi Jan,
I suggest that you do all the record selection in the
dialog box. Then change the record source of the form to a
select statement that returns just the selected record/s.
Users navigate using dialog only unless have selected
multiple records.

Faster as, usually it returns a single record, or at least
few records. Reduces likelihood of record lock conflicts.

The form that is opened is not bound to a record source.

' code in dialog
dim strSQL as string

strSQL="SELECT * FROM qrySource " _
& "WHERE ([PK]=" & lstSelection & ");"

docmd.openform FormName:="myForm", OpenArgs:=strSQL

' code in form
private sub Form_Load()
me.recordsource=me.OpenArgs
end sub

' qrySource is name of unfiltered query
' PK is Primary Key
' lstSelection is listbox name
' myForm is form name

Luck
Jonathan

.