Disconnected Recordset

[yaya013]

HELLO!!!
is it possible to work with disconnected recordset in
Access project (ADP)???

When I write Set Rs.ActiveConnection = Nothing
and then
Set Rs.ActiveConnection = CurrentProject.Connection
Rs.UpdateBatch

the table in the SQL Server are NOT update
Are there any other ways to solve this problem ?

Thanks in advance !

 

[cmmrfrds]

There is not enough information to work with.

Can you update the table from the ADP when NOT using the batch update?

Do you have sufficient permissions on the sql server tables?

What cursor location settings are you using? Is it a static cursor on the client side? What Locktype? i.e batchoptimistic

Is there something unique in the sql server table like a primary key or a timestamp field?

Are you iterating through the errors collection?

Need to see the connection and recordset properties on the Open.

 

[yaya013]

Hello cmmrfrds

Yse, when i'am not set the Rs.ActiveConnection = Nothing it is working fine.

I have sufficient permissions

I use adUseClient

There is nothing unique in the sql
I do not get any errors.

 

[cmmrfrds]

paste in your code.

 

[yaya013]

on the Form_Open event

Set Me.Recordset = AdoRS
Me.UniqueTable = "Customers"
Set AdoRS.ActiveConnection = Nothing

And then the user uses the form to do some
changes on the Recordset

In the CmdSave event

Set AdoRS.ActiveConnection = CurrentProject.Connection

AdoRS.UpdateBatch

I don't get any errors
But the table "Customers" has not changed

 

[cmmrfrds]

yaya013, I tried to recreate your example, but ran into the error message "data changed on Form but does not match criteria in recordsource". It is almost like Access is trying to resync with the data source. Did you run into this error and how did you get by it.

What you are trying to do makes sense, but there needs to be a way to tell Access just to accept the updates on the Form until one is ready for the batch update.

On your example, I wonder if you need to set the Form Recordset back to the other recordset before the batch update.

 

[yaya013]

I got this error but it's ok.

I understand my mistake.
I did not open the recordset proparely (adLockBatchOptimistic)
now i'ts working perfect.

Thanks !!

 

[yaya013]

to solve this error :

on the Form_Error event

If DataErr = 30014 Then Response = False