Recordset changes underlying table automatically

Hi all,

I probably have a misunderstanding of the use of a ADO Recordset.

I thought a Recourdset is a view and if I change it without updating
the underlying table (data) wouldn't change. But what I was
experiencing was different.

I opened a recordset with a select query of a table then manipulated
the recordset. Suprisingly I found that the underlying table (a linked
table from a SQL server) changed. Is this the normal way that a
Recordset behave? If so, is there an easy way to avoid it? I have
tried different combination of Lock type and Cursor type but did not
succeed.

Thank you for any hints,

0
muster
9/27/2007 8:19:06 PM
access 16762 articles. 3 followers. Follow

5 Replies
426 Views

Similar Articles

[PageSpeed] 58

Use adLockBatchOptimistic and don't make any call to .UpdateBatch.

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"muster" <muster@gmail.com> wrote in message 
news:1190924346.373289.5030@n39g2000hsh.googlegroups.com...
> Hi all,
>
> I probably have a misunderstanding of the use of a ADO Recordset.
>
> I thought a Recourdset is a view and if I change it without updating
> the underlying table (data) wouldn't change. But what I was
> experiencing was different.
>
> I opened a recordset with a select query of a table then manipulated
> the recordset. Suprisingly I found that the underlying table (a linked
> table from a SQL server) changed. Is this the normal way that a
> Recordset behave? If so, is there an easy way to avoid it? I have
> tried different combination of Lock type and Cursor type but did not
> succeed.
>
> Thank you for any hints,
> 


0
Sylvain
9/27/2007 9:14:34 PM
On Sep 27, 5:14 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> Use adLockBatchOptimistic and don't make any call to .UpdateBatch.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "muster" <mus...@gmail.com> wrote in message
>
> news:1190924346.373289.5030@n39g2000hsh.googlegroups.com...
>
>
>
> > Hi all,
>
> > I probably have a misunderstanding of the use of a ADO Recordset.
>
> > I thought a Recourdset is a view and if I change it without updating
> > the underlying table (data) wouldn't change. But what I was
> > experiencing was different.
>
> > I opened a recordset with a select query of a table then manipulated
> > the recordset. Suprisingly I found that the underlying table (a linked
> > table from a SQL server) changed. Is this the normal way that a
> > Recordset behave? If so, is there an easy way to avoid it? I have
> > tried different combination of Lock type and Cursor type but did not
> > succeed.
>
> > Thank you for any hints,- Hide quoted text -
>
> - Show quoted text -

Thanks. I tried that but got "unupdated records too many or too large"
message. Is there a way to change the default size of the UpdateBatch
cache?

0
muster
9/28/2007 1:39:04 PM
Don't know about changing the size of UpdateBatch.  You could also try to 
disconnect the recordset and maybe it's also a CacheSize problem or the fact 
that you are using a server side instead of a client side recordset.

However, I'm not sure to understand what you are trying to do here.  If you 
want a local table to manipulate your date, then you should use a local 
table (or some kind of temporary table on the server) and not a recordset. 
If you want a more powerful local data manipulation mecanism then switch to 
..NET.

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"muster" <muster@gmail.com> wrote in message 
news:1190986744.338820.108140@50g2000hsm.googlegroups.com...
> On Sep 27, 5:14 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>> Use adLockBatchOptimistic and don't make any call to .UpdateBatch.
>>
>> --
>> Sylvain Lafontaine, ing.
>> MVP - Technologies Virtual-PC
>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>
>> "muster" <mus...@gmail.com> wrote in message
>>
>> news:1190924346.373289.5030@n39g2000hsh.googlegroups.com...
>>
>>
>>
>> > Hi all,
>>
>> > I probably have a misunderstanding of the use of a ADO Recordset.
>>
>> > I thought a Recourdset is a view and if I change it without updating
>> > the underlying table (data) wouldn't change. But what I was
>> > experiencing was different.
>>
>> > I opened a recordset with a select query of a table then manipulated
>> > the recordset. Suprisingly I found that the underlying table (a linked
>> > table from a SQL server) changed. Is this the normal way that a
>> > Recordset behave? If so, is there an easy way to avoid it? I have
>> > tried different combination of Lock type and Cursor type but did not
>> > succeed.
>>
>> > Thank you for any hints,- Hide quoted text -
>>
>> - Show quoted text -
>
> Thanks. I tried that but got "unupdated records too many or too large"
> message. Is there a way to change the default size of the UpdateBatch
> cache?
> 


0
Sylvain
9/28/2007 4:07:11 PM

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
skrev i meddelandet news:Onv09meAIHA.4752@TK2MSFTNGP04.phx.gbl...
> Don't know about changing the size of UpdateBatch.  You could also try to 
> disconnect the recordset and maybe it's also a CacheSize problem or the 
> fact that you are using a server side instead of a client side recordset.
>
> However, I'm not sure to understand what you are trying to do here.  If 
> you want a local table to manipulate your date, then you should use a 
> local table (or some kind of temporary table on the server) and not a 
> recordset. If you want a more powerful local data manipulation mecanism 
> then switch to .NET.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "muster" <muster@gmail.com> wrote in message 
> news:1190986744.338820.108140@50g2000hsm.googlegroups.com...
>> On Sep 27, 5:14 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
>> blanks, no spam please)> wrote:
>>> Use adLockBatchOptimistic and don't make any call to .UpdateBatch.
>>>
>>> --
>>> Sylvain Lafontaine, ing.
>>> MVP - Technologies Virtual-PC
>>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>>
>>> "muster" <mus...@gmail.com> wrote in message
>>>
>>> news:1190924346.373289.5030@n39g2000hsh.googlegroups.com...
>>>
>>>
>>>
>>> > Hi all,
>>>
>>> > I probably have a misunderstanding of the use of a ADO Recordset.
>>>
>>> > I thought a Recourdset is a view and if I change it without updating
>>> > the underlying table (data) wouldn't change. But what I was
>>> > experiencing was different.
>>>
>>> > I opened a recordset with a select query of a table then manipulated
>>> > the recordset. Suprisingly I found that the underlying table (a linked
>>> > table from a SQL server) changed. Is this the normal way that a
>>> > Recordset behave? If so, is there an easy way to avoid it? I have
>>> > tried different combination of Lock type and Cursor type but did not
>>> > succeed.
>>>
>>> > Thank you for any hints,- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>> Thanks. I tried that but got "unupdated records too many or too large"
>> message. Is there a way to change the default size of the UpdateBatch
>> cache?
>>
>
> 
0
Carina
9/29/2007 3:12:10 AM

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> 
skrev i meddelandet news:Onv09meAIHA.4752@TK2MSFTNGP04.phx.gbl...
> Don't know about changing the size of UpdateBatch.  You could also try to 
> disconnect the recordset and maybe it's also a CacheSize problem or the 
> fact that you are using a server side instead of a client side recordset.
>
> However, I'm not sure to understand what you are trying to do here.  If 
> you want a local table to manipulate your date, then you should use a 
> local table (or some kind of temporary table on the server) and not a 
> recordset. If you want a more powerful local data manipulation mecanism 
> then switch to .NET.
>
> -- 
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "muster" <muster@gmail.com> wrote in message 
> news:1190986744.338820.108140@50g2000hsm.googlegroups.com...
>> On Sep 27, 5:14 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
>> blanks, no spam please)> wrote:
>>> Use adLockBatchOptimistic and don't make any call to .UpdateBatch.
>>>
>>> --
>>> Sylvain Lafontaine, ing.
>>> MVP - Technologies Virtual-PC
>>> E-mail: sylvain aei ca (fill the blanks, no spam please)
>>>
>>> "muster" <mus...@gmail.com> wrote in message
>>>
>>> news:1190924346.373289.5030@n39g2000hsh.googlegroups.com...
>>>
>>>
>>>
>>> > Hi all,
>>>
>>> > I probably have a misunderstanding of the use of a ADO Recordset.
>>>
>>> > I thought a Recourdset is a view and if I change it without updating
>>> > the underlying table (data) wouldn't change. But what I was
>>> > experiencing was different.
>>>
>>> > I opened a recordset with a select query of a table then manipulated
>>> > the recordset. Suprisingly I found that the underlying table (a linked
>>> > table from a SQL server) changed. Is this the normal way that a
>>> > Recordset behave? If so, is there an easy way to avoid it? I have
>>> > tried different combination of Lock type and Cursor type but did not
>>> > succeed.
>>>
>>> > Thank you for any hints,- Hide quoted text -
>>>
>>> - Show quoted text -
>>
>> Thanks. I tried that but got "unupdated records too many or too large"
>> message. Is there a way to change the default size of the UpdateBatch
>> cache?
>>
>
> 
0
Carina
9/29/2007 3:31:53 AM
Reply:

Similar Artilces:

Macro to change flag reminder date +/or time on many messages at once?
This is one I haven't tried to fix before. I have a special use for Outlook. Besides regular messages, I send myself "To Do's" to and from home marked with a special phrase when it's a To Do that has rules that mark it with a flag, etc. In either work or home, a lot of those To Do's need to be deferred to a different time or day. It's been tedious as I have to change each manually. Now I made it easier some time ago by putting a flag option right on the toolbar so at least I don't have to open each message. The preview pane is on for this folder and I th...

Reverting changed data
Is there a method that I can call that will return the value of an attribute to what it was when the form was loaded if it was changed? Also, is there a method that I can call that will cancel a save? I.E., the user clicks on the save button, an onSave event fires, a test fails, and the form does not save. If the user had hit save and close, it doesn't save and it doesn't close. I greatly appreciate any help you can give me. The original value should be available in the value attribute: crmForm.all.fieldname.getAttribute("value"). You need to check if this is true for...

"Table Tents"
I have Office 2007 and can use Publisher or Word:). I need to make "table tents" for 30 different exhibitors' tables, for a non-profit expo. My idea is to turn an 8.5 X 11 page to landscape. So I have the 8.25 up and down. Then I just need to write one line of text in Cambria 48 text size- twice on each page. I would like to print two of the exhibitors per page (non-profit, you know). Splitting it at ~4.0. Each Table tent would be cut to a 11 X ~4 inch size and then that would be folded in half. I would fold over each half page, and the name would be visible at t...

Resize chart automatically when window size changes?
I have a chart, created by another, that changes size automatically when I expand or shrink the Excel window. He can't remember how he did this, and I would like to duplicate this feature on other charts. Can't find anything in documentation or on-line. May not be using correct "Microsoft speak." Thanks This works only on chart sheets. Select the chart, go to Tools menu > Options > Chart tab. Find and check Chart Sized with Window Frame. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "XYMooseHun...

Using an update query to update amount in stock automatically
I have created a database to keep a track of orders. In the stock table I have a field called amount in stock. In order table I have a field called order quantity. I have created an update query to update amount in stock to [amount in stock]-[order quantity]. I wish to create a macro to run the update query on my order form once the sale is confirmed. My problem is that I want to just update the single record on the order form, and not all items of stock from all existing orders. I know I can add a parameter criteria to my update query (i.e. enter order number) so that only stock relating ...

changing entry's quickly #3
I have aproximately 5000 entries for product codes. My problem is this: I need to get rid of the last two digits of each code quickly. For example: I need to change 0234600 to 02346 is there a simple way to do this? It needs to be done in text format because of the zero at the start of each product. Thanks -- cj21 ------------------------------------------------------------------------ cj21's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25673 View this thread: http://www.excelforum.com/showthread.php?threadid=390953 ...

How do i create an automatic monthly cash flow from lists of cost.
I am in the process of creating a 'Project Costings' solution which I hope colleagues at work can easily input basic project data into. Excel will then calcualte all the necessary figures e.g. Overheads etc. without using VBA My question is how do I get my monthly Cash Flow statement to automatically update in categories from a seperate sheet of costs i.e. If I have a list of 10 items in say Equipment budget listed down with dates of purchase next to them. How do I then automatically update the Cash Flow statement in the relevant month of purchase with the correct amounts. I...

Pivot table field types
Hi, when dragging in data fields into a pivot table, the default field type most often comes up as 'count'. I often have to right click over the field, and change the field setting to sum, and do this individually for each field. Does someone know if there is a way to change the default to 'sum' or to quickly convert all the data fields to sum in my pivot table? This is annoying the hell out of me! Much appreciated, Steve. -- stevehere ------------------------------------------------------------------------ stevehere's Profile: http://www.excelforum.com/member.php?actio...

How do I change the size of Value Axis Title box for an Excel Cha.
The title box cannot be sized. I must reduce the font to very small to prevent truncating the title. I would have thought I could drag the box to the size I wanted but I do not get the chance to do so. Instead of a Title, you can use a Text Box: Select the Chart Type the text you want in the Title Press the Enter key A text box will appear in the centre of the chart Drag it to the location you want it, and format/resize as desired. If you prefer, you can link the text box to a worksheet cell, and display its text: Select the chart Type an equal sign Click on the cell you want to link Pr...

insert table into connecting text box w/link
How do I insert a rather large table (single column, many rows) into text boxes which overflow from page to page? You don't... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "display name" <display name@discussions.microsoft.com> wrote in message news:B397C80A-B72D-4D1B-B106-E14E3222A331@microsoft.com... > How do I insert a rather large table (single column, many rows) into text > boxes which overflow from page to page? ...

duplicate names in Pivot Table
The more I use Excel, the more surprises I get! I have a Pivot table based on a 16-column data list, one of the columns being "End Uses," say, office machines, laptops, FAX machines, etc., a total of 15 different end uses. Each End use occurs several times in the column. For some reason, two of these end uses, though spelled identically, appear twice on the drop-down list of the table. Any idea how that might have happened? Probably one of the items has a space character at the end, and the other doesn't. K. Georgiadis wrote: > The more I use Excel, the more surpri...

pivot table
hi Though I have been using Excel for quite a while. I just did my first pivot table today. I need a report based on specific date (Actually it is every Sunday). The date I have entered are Nov 1, 2009 and Nov 8, 2009) in the Excel but when I used date as the page report filter. The date filter is <Nov 1 > Nov 9 with many dates in between. How do I make it to show only Nov 1, 2009 and Nov 8, 2009. I really very happy because pivot table really useful tool except for my problem re date. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/exce...

Prevent data change in custom view
In a custom view on a public folder, where data are grouped, it is possible to change editible fields in items by dragging & dropping it from one group to another. In my case this happened erroneously and data were changed unintentionally. Is there a way to prevent any data change in a view, e.g.to make a view READ-ONLY? ...

Font for composing messages won't change
I am using Office 2000 premium suite Outlook 2000 Word 2000 Word is email editor. I seem to be stuck with Arial 10 point as default. How can I change this? Someone told me to change the Word email template, but I don't know where that template is stored (or what it is called) Steve Ok, first launch a new e-mail in your Word editor. Click down in th message area, then click on the "Format" button on your menu bar. Choose "Font." Select the font and size (color, etc.) you would lik in this box, then click the "Default" button at the bottom right of th d...

Link current query to a table?
I'm trying to create a cost estimate form, and I'm working on one underlying query to power it. Its based off a lot number. A certain digit in our lot number indicates the blend of the product. I have a table of blend codes and descriptions based on that digit. I have a column to calculate the blend code from the lot number but I am having no luck getting the description into either the query or the form. I have tried a Dlookup but I seem to have trouble getting it to refer back to the query that it is in. I know I could create another table of Lots and their associ...

30 tables in a form?
How come when i tried to put all my tables into a form, Access doesn't let me do it and gave me an error? You need to provide more information. A form may be based on a query comprising several tables, but it is unclear what you mean when you say you "tried to put all my tables into a form". In what way exactly are you attempting to do that, and what is the error message? What do you hope to accomplish? <emerlita@shaw.ca> wrote in message news:1176302831.519449.88430@d57g2000hsg.googlegroups.com... > How come when i tried to put all my tables into a form, Acce...

string table
Is there a way to paste a list of string IDs, Values and Captions into the string table, or should I say into a string resource file? "Steve Russell" <srussell@innernet.net> wrote in message news:%23cGTtwvgDHA.1084@tk2msftngp13.phx.gbl... > Is there a way to paste a list of string IDs, Values and Captions into the > string table, or should I say into a string resource file? I'm not sure I understand your question, Steve, but you can open the *.rc file as text and paste stuff in it. Jeff... Jeff wrote, I'm not sure I understand your question, Steve, but yo...

Changing existing code to add "IF Statement"
I am currently using a database that is working well. However, I have discovered that I need to make a slight change so that one of the fields calculates differently based on the data used in another field. This is the existing code: =DSum("[WeeksService]","Service Record Query","[EmployeeID] = " & [EmployeeID] & " And [DepartmentName] = Current_Department_Name") I would like to have the code add ALL of the weeks service IF the Current Department Name is equal to "Reserves". Can anyone provide help? Thanks in advan...

how do I change the default font size in endnotes? 03-03-10
I cannot change the default font size in endnotes (Word 2003). If I use the font menu and seek to apply 12-pointas default, it reverts to 10-point every time I create a new endnote. Can somebody help? You should be able to change your default font. Click Format, Font, change font size, click on Default button to select the new size. The font size will be the same for your endnotes. "kar201245" wrote: > I cannot change the default font size in endnotes (Word 2003). If I use the > font menu and seek to apply 12-pointas default, it reverts to 10-point every >...

Can you change the intercept of the X-axis to a non-zero value?
Mathematically it sounds stupid, but anyway: I have a simple time series plotting economic growth rates against time. In 1998 the growth rate for the country in question is negative, so the line graph drops below the X-axis, to -6.7%. The X-axis now goes straight through the graph, which is pretty unsightly. Is there a way I could move the X-axis down to Y=-8% for example? Format the axis and change the value of Crossing At best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "newaglish" <newaglish@discussions.microsoft.com> wrote in messa...

Amortization table problem
I am trying to set up a dynamic amortization schedule that allows me t change the loan variables (interest rate, length of loan, amount, etc. I am using the PPMT and IPMT functions to calculated the principal an interest payments for each period. My model covers a 30 year tim horizon. The problem occurs when I set the loan time less than 30 years. Fo example, for a 20 year loan I have a line that counts 1, 2, 3, ..., 20 0, 0, to tell the PPMT and IPMT equations which period to calculate. But when it reaches year 21, a zero feeds into the PPMT equation fo time period, resulting in a #NUM...

Automatic "Mark as read" for 1 folder?
I'm using Outlook 2002 (SP-2). I also use Qurb, a whitelist, to automatically direct spam out of my InBox and into a separate "Qurb" folder. Is there some way I can create a rule that when a message goes into the Qurb folder it is automatically marked as read? "T. Wise" <terwise111@yahoo.com> wrote in message news:%23dX0GakSEHA.3012@tk2msftngp13.phx.gbl... > I'm using Outlook 2002 (SP-2). I also use Qurb, a whitelist, to > automatically direct spam out of my InBox and into a separate "Qurb" folder. > > Is there some way I can create...

pivot table #15
is there a way to preserve border outlines in a pivot table after you refresh? Right-click a cell in the pivot table, and choose Table Options Ensure that Preserve Formatting is turned on, and AutoFormat is turned off, then click OK To format cells, enable selection should be turned on. To enable selection -- From the Pivot toolbar, choose PivotTable>Select If it's not already activated, click on Enable Selection To format a section of a pivot table, e.g. subtotals -- Move the pointer to the left of a subtotal heading in the pivot table. When the black arrow appears (like the on...

what is the format of Journal Data in Journal Table?
I was wondering whether i can retrieve the data from Journal Table in VB application to view the printed documents. What is the format of this field and what is the suitable application should be used to view this journal content? Thx! NJ, I am trying to do same - as far as I can work out the data is a compressed (zip) version of the (partial) xml receipt. I have been able to extract the uncompressed receipt with the data but now I am trying to figure out how to display it - I am thinking that the XML parser has not been exposed in QS rules and that I have to build my own parser and displa...

Outlook 2002 SP2 font changing
I have a signature saved in one font and when I create a new message it does the autosign in a different font. Any ideas on getting this to populate correctly? ...