I have a MS Access 2000 front-end for a SQL 2000 db. I have a form that inserts a record which uses the following code. The code works fine, but is too slow. I've changed the fillfactor to 80 in SQL Server, and that didn't help.
if you are inserting a row, why do you "select *" from a table immediately prior to the insert
I'd have thought it would make more sense to insert directly. "insert into..."
Not sure what the column 'IntmaxEntry' relates to -is this in effect an autonumber field (ie is the the table defintion suspect), alternatively would it be better to define an intial query to retrive the current highest intmaxentry then a new query to "insert into..."
If you don't use the results from "select *....." , ie you do't actually do anything with the query why run it in the first place (this will have a significant impact on times especialy if the specified table has many rows).
perhaps the strategy is
query 1: find the current highest intmaxentry
query 2: insert new record
'doing it he way you are proposing could have problems unless you secure a table lock between tieh find the current intmaxentry, and the time you commit the new record. If you can use the autonumber filed then it absolves you from having to handle the risks of two (or more) users wanting to update the intmaxentry concurrently.