Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 16th, 2004, 02:19 PM
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default ADO Recordset and SQL Statements

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
 
Old March 17th, 2004, 03:17 AM
Authorized User
 
Join Date: May 2003
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team
 
Old March 17th, 2004, 08:47 AM
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 21st, 2004, 09:53 AM
Registered User
 
Join Date: Mar 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old March 21st, 2004, 06:19 PM
Authorized User
 
Join Date: Jun 2003
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
ADO Recordset Scootterp Access VBA 4 February 27th, 2006 06:44 PM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM
ADO/SQL Server RecordSet and the OrderBy Property Dataman Access 3 February 9th, 2004 11:11 AM
inserting utf-8 data using ado recordset to ms sql behrang79 Classic ASP Databases 0 September 29th, 2003 07:12 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.