Hi there,

   I have a fully updatable ADO recordset from SQL Server in my Access 2003 database. Is it possible to apply a SQL Statement to the recordset. What I'm trying to do is apply and update SQL statement to the recordset and when I'm finished with all the changes, I will use the updatebatch method of the ADO recordset object when I'm finished making all the changes. Doing this will allow me to not have to make a trip to the SQL server box for each change. Is it possible to perform this...I thought I read that you could somewhere, but can't find any documentation in the help files/books.

Thanks,
Mike

I am not getting your point. can you explain it more

Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team

Wiley Support Team...my 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,
Mike

Dear Dataman

Here are a couple of potential solutions you could use:

1a) Apply a filter on your recordset
b) Use a For Next loop with the Update/Delete methods on the fiiltered recordset in order to achieve a similar result to one you are trying to achieve.

2. Alternatively save the recordset to disk in an XML format and then you can reissue a SQL statement against the data

Gerty,
   thanks for the ideas. I just went ahead and issued my update statements via my connection object and then reopened the recordset. At least I was able to pass all 3 SQL statements at the same time. It seems to be fairly fast, but only time will tell, and the users will ultimately have final say.

Thanks again,
Mike