Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 16th, 2004, 02:19 PM
Authorized User
 
Join Date: Jun 2003
Location: Glendale, AZ, USA.
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
Reply With Quote
  #2 (permalink)  
Old March 17th, 2004, 03:17 AM
Authorized User
 
Join Date: May 2003
Location: Delhi, Delhi, India.
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
Reply With Quote
  #3 (permalink)  
Old March 17th, 2004, 08:47 AM
Authorized User
 
Join Date: Jun 2003
Location: Glendale, AZ, USA.
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
Reply With Quote
  #4 (permalink)  
Old March 21st, 2004, 09:53 AM
Registered User
 
Join Date: Mar 2004
Location: , , .
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

Reply With Quote
  #5 (permalink)  
Old March 21st, 2004, 06:19 PM
Authorized User
 
Join Date: Jun 2003
Location: Glendale, AZ, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:16 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.