adOpenStatic Recordset still changing my table

[KPR]

Hi,

I'd like to simply open a recordset, populate it with the records of a
table, set the RecordSource of my form to the RecordSet and then ensure that
any changes made in the form will never update my table. The code I'm using
is below but it still updates my table.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset

With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM MyTable"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.CursorLocation = adUseServer
.Open
End With

'Set my form's Recordset property to the ADO recordset

Set Me.Recordset = rs

cn.Close
Set rs = Nothing
Set cn = Nothing

Any help would be apprecitated.

Thanks,
Ken

 

[Alex Dybenko]

Hi,
try to disconnect recordset after opening it:
set rs.ActiveConnection =nothing


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[KPR]

Thanks Alex, but when I do this it does seem to disconnect the connection
fine but it raises errors when I try and work in the recordset because it's
still trying to save to the table but it can't.

 

[Dirk Goldgar]

Hi,

I'd like to simply open a recordset, populate it with the records of a
table, set the RecordSource of my form to the RecordSet and then ensure
that
any changes made in the form will never update my table. The code I'm
using
is below but it still updates my table.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset

With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM MyTable"
.LockType = adLockOptimistic
.CursorType = adOpenStatic
.CursorLocation = adUseServer
.Open
End With

'Set my form's Recordset property to the ADO recordset

Set Me.Recordset = rs

cn.Close
Set rs = Nothing
Set cn = Nothing

Any help would be apprecitated.

First, have you tried specifying

.LockType = adLockReadOnly

? Although I don't work with ADO all that much, that's the property that I
would expect to make the recordset read-only.

Second, is there any reason you aren't just using a normal bound form with
its AllowEdits, AllowDeletions, and AllowAdditions properties set to No?
Wouldn't that be simpler than opening your own recordset like this?

Third, I don't believe you should be closing the connection object "cn" in
this case. This is Access's connection, not one that you opened. I imagine
(without testing) that Acces either refuses to close it, or just opens it
again anyway, but it doesn't really make sense to try to close it.

 

[Alex Dybenko]

ok, perhaps you need to use:
Set Rs.ActiveConnection = Nothing

see:
http://support.microsoft.com/default.aspx/kb/184397

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[KPR]

Hi Dirk, I'm running a dynamic query in a mult-user environment so I want
each user to load a recordset and select which records they want to use in
the their query. I'll keep playing with the code.