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

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" <(E-Mail Removed)> wrote in message
news:1517EF1A-DEE6-494C-AE5F-(E-Mail Removed)...
> 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

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.

"Alex Dybenko" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:1517EF1A-DEE6-494C-AE5F-(E-Mail Removed)...
> > 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
>
>

"KPR" <(E-Mail Removed)> wrote in message
news:1517EF1A-DEE6-494C-AE5F-(E-Mail Removed)...
> 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.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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" <(E-Mail Removed)> wrote in message
news:35CA55A0-442E-4506-A1AE-(E-Mail Removed)...
> 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.
>
> "Alex Dybenko" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:1517EF1A-DEE6-494C-AE5F-(E-Mail Removed)...
>> > 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
>>
>>

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.

"Dirk Goldgar" wrote:

> "KPR" <(E-Mail Removed)> wrote in message
> news:1517EF1A-DEE6-494C-AE5F-(E-Mail Removed)...
> > 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.
>
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>