WHY does CancelUpdate Delete current record?

[Thom]

OK, I've wiped the hard drive, reinstalled Win98, DCOM, MDAC 2.0, and Visual Studio 6.0(with no service packs).

I've rebuilt my ADO 2.0 project, using Access97 as the backend DB, and while using disconnected recordsets:

If I go into EDIT mode and THEN invoke the CancelUpdate method of the recordset-the current record is DELETED! Any ideas why? I've tried it with dynamic AND keyset cursors, but it STILL deletes. This does NOT happen with Addnew-however! My boss is suggesting I just don't allow Edits to be canceled! <g> in order to get the project back on track. ANYONE have a better idea/solution. My next step is to pay the obscene $$ to call Microsoft to try for an 'official' answer.

As an aside, does this happen with SQL databases? I'm thinking of trying to convince the division to move to SQL server, IF we can just put a SQL DB on the server & have it accessed from the clients(the way I intended the Access DB to work). HELP, please!

[LG]

Thom,
Check this post.
http://forums.vb-world.net/showthread.php?threadid=22135

[JHausmann]

You'll spend more money if you go to SQL server but you should have far fewer problems, better responsiveness and more security/reliability, And, as an added bounus, you'll get better tools for your database's support and maintenance.

Does your problem happen on SQL Server, using disconnected recordsets? Dunno, never had the need to use them.

[JHausmann]

Instead of CancelUpdate, have a look at Resync.

[Thom]

Thanks for your replies! Yes I now KNOW SQL is DEFINITELY the way to go for this project JHausmann! What I'm trying to do is get the first version out in Access2000, then convince the powers above to go to SQL if it's decided to spread the test program around the company. That's the main reason for the disconnected recordsets-we might end up with 10-20 people trying to do heavy-duty tasks all at the same time-& I thought it would be less of a drain on the Network if they were disconnected.

I really appreciate the replies; I ended up writing code to go around the CancelUpdate method-it must be a unique problem with ADO & Access.

[Clunietp]

The CancelUpdate method works fine for me

I'm running this code with VB6 SP4, Access 2000 and ADO 2.5


Dim cn As Connection
Dim rs As Recordset

Set cn = New Connection
Set rs = New Recordset

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Nwind2k.mdb"

rs.CursorLocation = adUseClient
rs.Open "Select * from Customers", cn, adOpenStatic, adLockOptimistic

'show initial value
MsgBox rs(1)

'begin edit
rs(1) = rs(1) & rs(1)

'show the changed value
MsgBox rs(1)

'cancel update
rs.CancelUpdate

'show the value, its back to the original value and the record still exists
MsgBox rs(1)

[Thom]

Figures! there must be something wrong with the PC or the VB install! Oh well-I'm going to go to ADO 2.5 with A2K and it might work for me then as well. Thanks Clunietp for letting me know it CAN work in Access!