UpdateBatch and ADO disconnected recordsets - DOES NOT UPDATE BATCH !

[Andrew Bingham]

Using Access 2002 and ADO 2.7 and SQL Server 2000

I can set a form's recordset to an ADO recordset with a ClientSide cursor
and edit the data in the form.

However:
Me.Recordset.UpdateBatch

does NOT update the data in the table. No error is produced it just seems to
do nothing

Why ?

cheers

Andrew
--
****************************************************************************
andrewbingham.com

tel 01223 514674 (Cambridge)
mobile 07970 161057
fax 07970 601283
email And...@AndrewBingham.com

DISCLAIMER, PLEASE NOTE:
This communication is for the attention of the named recipient only
The content should not be passed on to any other person.
It is sent in good faith, in confidence, and without legal responsibility.

VIRUS CHECK
Emails and attachments are virus checked using Norton® AntiVirus®
2002 which is regularly updated. However it remains the recipients
responsibility to check emails and attachments sent, or forwarded,
from andrewbingham.com for viruses and macro viruses
****************************************************************************

[Vlad Vissoultchev]

if the recordset is disconnected you'd better "reconnect" it before batch
updating. somthing like:

Set Me.Recordset.ActiveConnection = oConn

HTH,
</wqw>

"Andrew Bingham" <And...@AndrewBingham.com> wrote in message
news:#prJKGDJ...@tk2msftngp13.phx.gbl...

[Bob Barrows]

Andrew Bingham wrote:
> Using Access 2002 and ADO 2.7 and SQL Server 2000
>
> I can set a form's recordset to an ADO recordset with a ClientSide
> cursor and edit the data in the form.
>
> However:
> Me.Recordset.UpdateBatch
>
> does NOT update the data in the table. No error is produced it just
> seems to do nothing
>
> Why ?
>

Hard to say without seeing the recordset open statement and the code to
reconnect to the database ... Did you do all the following steps?

rs.CursorLocation=adUseClient
rs.LockType = adLockBatchOptimistic
rs.open ...
set rs.ActiveConnection = nothing

. . . make changes ...
rs.update
........

set rs.ActiveConnection = cn
rs.UpdateBatch

HTH,
Bob Barrows

[Andrew Bingham]

Yes I did all the above

I use disconnected recordsets in VB with no problem

cheers

Andrew

--
****************************************************************************
andrewbingham.com

tel 01223 514674 (Cambridge)
mobile 07970 161057
fax 07970 601283
email And...@AndrewBingham.com

DISCLAIMER, PLEASE NOTE:
This communication is for the attention of the named recipient only
The content should not be passed on to any other person.
It is sent in good faith, in confidence, and without legal responsibility.

VIRUS CHECK
Emails and attachments are virus checked using Norton® AntiVirus®
2002 which is regularly updated. However it remains the recipients
responsibility to check emails and attachments sent, or forwarded,
from andrewbingham.com for viruses and macro viruses
****************************************************************************

"Andrew Bingham" <And...@AndrewBingham.com> wrote in message

news:%23prJKGD...@tk2msftngp13.phx.gbl...

[Bob Barrows]

I think you will need to ask this on an Access newgroup. There may be a
limitation imposed by using a Form's Recordset property of which I am
unaware.

Bob Barrows

[Andrew Bingham]

Hi

Below is is the code to get the form data and to save it:.

I can get UpdateBatch to work with non-parametised Stored Procedures, but
not with a parametised command

cheers

Andrew

============================================================================
Private Sub LoadData()

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset

Set cmd = New ADODB.Command
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset

With cnn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Initial Catalog").Value = "TestData"
.Properties("Data Source").Value = "Server1"
.Properties("User ID").Value = "sa"
.Properties("Password").Value = "SecretPassword"
.Open
.CursorLocation = adUseClient
End With

With cmd
Set .ActiveConnection = cnn
.CommandText = "up_sel_ReturnDetailForReturnID"
>>>>>>>>> this is a stored procedure in SQL Server
.Parameters.Append .CreateParameter("ReturnID", adInteger,
adParamInput, , 20) >>>>>>>>> this is the stored procedure's parameter
End With

With rst
.Open cmd, , adOpenDynamic, adLockBatchOptimistic, adCmdStoredProc
Set .ActiveConnection = Nothing
End With

Set Me.Recordset = rst

End Sub
============================================================================
Private Sub cmdSave_Click()

Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection

With cnn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Initial Catalog").Value = "TestData"
.Properties("Data Source").Value = "Server1"
.Properties("User ID").Value = "sa"
.Properties("Password").Value = "SecretPassword"
.Open
End With

With Recordset
.Update
Set .ActiveConnection = cnn
.UpdateBatch
End With

End Sub

****************************************************************************
andrewbingham.com

tel 01223 514674 (Cambridge)
mobile 07970 161057
fax 07970 601283
email And...@AndrewBingham.com

DISCLAIMER, PLEASE NOTE:
This communication is for the attention of the named recipient only
The content should not be passed on to any other person.
It is sent in good faith, in confidence, and without legal responsibility.

VIRUS CHECK
Emails and attachments are virus checked using Norton® AntiVirus®
2002 which is regularly updated. However it remains the recipients
responsibility to check emails and attachments sent, or forwarded,
from andrewbingham.com for viruses and macro viruses
****************************************************************************

"Andrew Bingham" <And...@AndrewBingham.com> wrote in message
news:%23prJKGD...@tk2msftngp13.phx.gbl...