View Single Post
  #3 (permalink)  
Old March 17th, 2004, 08:47 AM
Dataman Dataman is offline
Authorized User
Join Date: Jun 2003
Location: Glendale, AZ, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts

Wiley Support explanation may be a little long winded.
(Access 2003 and SQL Server 2000)

I have an ADO recordset that I create when a form opens. For this example the recordset has and ID(PK), a description field and a numeric sort order field. This recordset is fully updatable and comes from a SQL server stored procedure.

The recordset is then bound to a list box and the user can modify the sort order of the items in the list using two command buttons which move items up or down in the list.

In an earlier version I just issued SQL statements via the connection object back to the SQL server box to make the changes. Then I would go get the recordset again, tie it to the list box to show the user the change. This works, but the delay to the SQL server box could be a second or more depending on network traffic.

What I'm trying to do is to not have to make a trip to the SQL server box every time the user moves and item in the list. I would like to make all the changes on the client side and then using the UpdateBatch method submit all the changes back to the SQL server at the same time. It should significantly increase the performance of the client.

My recordset is stored in a module level variable called rstTemp. I would like to apply a SQL statement against the recordset held within the variable.

Update SortOrder SET SortOrder = " & intNewSortOrder & " FROM " & rstTemp & " WHERE ID = 12

The above SQL statement should only be taken as an example of what I'm trying to accomplish. I know that I can use the Find method and then issue an update statement, I just thought it might be possible to do something like the SQL statement above.

Thanks in advance,
Reply With Quote