access front end to sql back end and form navigation buttons

  • Thread starter Thread starter Keith G Hicks
  • Start date Start date
K

Keith G Hicks

This is just a general question on technique. I don't need specific
details. I have an MS Access 2k front end hooked to an MS SQL 2k back end.
The main table in the system has about 30,000 records in it so binding the
form to the entire dataset is dumb. I want to bind the form to only one
record at a time to keep it fast. My client wants to have navigation
buttons on the form. So of course I have to code the thing so that the nav
buttons pull up the correct records in the correct sequence from the data.
What I've done to handle this works fine so far but I'm curious how other
people have handled this. Is there a website with 3rd party tools for this
and/or appropriate code for the front end and back end? Again, just looking
for general ideas on this.

Thanks,

Keith
 
Mary Chipman "wrote the book" on this. Check it out.
One idea is to use filters and force the client to input some type of search
criteria.
Binding to 200 or so records is no big deal. So you wouldn't even have to
code nav buttons as long as your user realized they would have to enter new
search criteria when they were done with that current "batch".

Also, Allen Browne has a good tip for using cbos with large amounts of data.
Check out his web site.
The basic trick is to forc the user to enter X characters before
"activating" the cbo.
e.g. user enters "Jo" and then the list drops down for all records that
start with "Jo".
 
I've done the whole thing for other clients where they can only pull up a
record by setting a criteria or 2 to choose the record. And that's what I
prefer. But this client needs to have the whole nav button thing.
So here's what I did.
I create a temp table on the back end for the current user that has the
primary key, a sort order # and a couple other important fields for the
table in question. When the user clicks a nav buttion I have an SP that
gets the correct record from that temp table and passes the pkey back to the
front end so it can pull up the correct record. I pass "F", or "P", or "N",
or "L" and the record the user is currently viewing and then a cursor skips
to the record needed according to which nav button was clicked. The temp
table is of course dropped at the appropriate time. It all works very very
fast and well. I still have to work out code so that if a record in the
table is deleted by another user and then the current user navigates to it.
But that shouldn't be too hard. The whole thing works very well on a table
that has about 20,000 records in it.
I'll definitley check out the Allen Browne combo box tips. It sounds like a
technique I used on another project that was pure Access some time ago.

Where is the Mary Chipman info that you mentioned to be found?

Thanks for the info,

Keith
 
If you are using ODBC to talk to SQL Server, binding a
form to that many records is fine. Your only real
concern is to possibly prevent an intent to share lock
from escalating to an exclusive lock on the server.
Since all the clients can only edit one record at a time,
this is not usually a problem. The problem can happen
when a user needs to have a table lock that needs a
higher locking level than the form. Usually this only
happens when a client updates the whole table like:

UPDATE tblOrders SET OrderQty = 0

The reason the lockup will happen is because Access has
the tendency to not return all records and leaves an open
query on the server until it reaches the end of the
table. You can fix this by forcing Access to go straight
to the last record with:

Me.RecordsetClone.MoveLast

This will cause Access to go to the last record without
actually going there in the interface.

Another thing to remember is you absolutely should use a
dynaset as the underlying query and not a snapshot for
this many records. Snapshot performance will be horrible.
 
Back
Top