UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> ADODB Recordset in Access 2007    
 
   
gem1204
post Dec 21 2008, 06:12 PM
Post#1



Posts: 70
Joined: 18-October 04



I have an MS Access 2003 database that has code in the open event of a form that sets the recordset to an adodb recordset.
This is the code
CODE
Dim sql As String
sql = "Select * from tblQuickenData"
Dim RsClone As ADODB.Recordset
Set RsClone = New ADODB.Recordset
RsClone.Open sql, CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic
Set Me.Recordset = RsClone

When I open the form with MS Access 2003 the records are updatable...I can add, delete append. When I open the same form with MS Access 2007 the records are read only.
Can someone tell me why this is and how to fix it? I have a lot of databases that I've developed using MS Access 2003 and I would really like to convert them. I've used Access for well over 10 years. There are a lot of great new things in Access 2007, but there are also a heck of a lot of things that don't seem to work.
Thanks
GEM
Go to the top of the page
 
GroverParkGeorge
post Dec 21 2008, 07:02 PM
Post#2


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


The following comment appears in MSDN with regard to ADO recordsets

To bind a Microsoft Access form to a recordset, you must set the form's Recordset property to an open ADO Recordset object. A form must meet two general requirements for the form to be updatable when it is bound to an ADO recordset.

The general requirements are:

The underlying ADO recordset must be updatable via ADO.
The recordset must contain one or more fields that are uniquely indexed, such as a table's primary key.

In my testing, I get similar results to yours, even though the recordset opened IS updatable and has a primary key. That is, I can edit the recordset via ADO, but not in the form itself.

I'm still looking for relevant information.
Go to the top of the page
 
Bob_L
post Dec 21 2008, 08:05 PM
Post#3


Utterly Banned
Posts: 7,038
Joined: 5-December 02



try using this syntax instead:
!--c1-->
CODE
RsClone.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

using CurrentProject.Connection instead of .AccessConnection
Go to the top of the page
 
GroverParkGeorge
post Dec 21 2008, 09:37 PM
Post#4


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Hmm. That seems like it should work, but in my testing I'm getting the same result as with .AccessConnection.
Also, Access Help says:
You can use the AccessConnection property to return a reference to the current Microsoft ActiveX Data Objects (ADO) Connection object and its related properties. Read-only Connection.expression.AccessConnection
expression A variable that represents a CurrentProject object.
Remarks
You should use the AccessConnection property if you intend to create ADO recordsets that will be bound to Access forms. The form will not be updateable unless it is created by using the OLE DB Provider for Microsoft Access, even if the recordset is updateable in ADO.
Have you been able to test the .Connection syntax successfully? I'm still puzzled.
Go to the top of the page
 
Bob_L
post Dec 21 2008, 09:40 PM
Post#5


Utterly Banned
Posts: 7,038
Joined: 5-December 02



The other possibility is that the database files need to be in a Trusted Location.
Go to the top of the page
 
datAdrenaline
post Dec 21 2008, 10:26 PM
Post#6


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


Hey Bob ....

Just a quick FYI ... JET/ACE does not support adOpenDynamic recordset .. when requested as you have done so (with CurrentProject.Connection, which defaults to a server side cursor), they will be silently coerced to an adOpenKeyset typed ADO recordsets ... thumbup.gif

Edited by: datAdrenaline on Sun Dec 21 23:03:56 EST 2008.
Go to the top of the page
 
datAdrenaline
post Dec 21 2008, 10:44 PM
Post#7


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


I beleive the symptoms you are seeing is due to A2007's "feature" of enabling 'SandBoxMode' by default. For more information on SandBoxMode in A2007 check out ...
http://office.microsoft.com/en-us/access/HA101674291033.aspx
And
http://office.microsoft.com/en-us/access/HA012301901033.aspx
Go to the top of the page
 
LPurvis
post Dec 21 2008, 10:48 PM
Post#8


UtterAccess Editor
Posts: 16,085
Joined: 27-June 06
From: England (North East / South Yorks)


>> will be silently coerced to an adOpenKeyset typed ADO
lthough using the AccessConnection built in connection (which inherently has a client side cursor) then the type will coerce to static (adOpenStatic) thumbup.gif
Go to the top of the page
 
GroverParkGeorge
post Dec 21 2008, 11:01 PM
Post#9


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


No joy changing sandbox mode to "2" from "3", theoretically setting it for all connections other than Access.
Go to the top of the page
 
datAdrenaline
post Dec 21 2008, 11:09 PM
Post#10


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


Hey Leigh ...
>> AccessConnection built in connection (which inherently has a client side cursor) then the type will coerce to static (adOpenStatic) <<
.. Yep .. smirk.gif
... I edited my post to be more clear ...
Go to the top of the page
 
datAdrenaline
post Dec 21 2008, 11:16 PM
Post#11


UtterAccess Editor
Posts: 17,929
Joined: 4-December 03
From: Northern Virginia, USA


Interesting find George ... from the article, it seemed as though Sandbox wouldb have been the culprit .. maybe a bug??
Go to the top of the page
 
GroverParkGeorge
post Dec 21 2008, 11:25 PM
Post#12


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


I am now looking at the trust settings. Also, I may create a new accdb and try it again with the sandbox mode already altered.
Go to the top of the page
 
GroverParkGeorge
post Dec 21 2008, 11:31 PM
Post#13


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


I don't have Access 2003 on this machine. Has someone else been able to verify that the form IS updatable when the ADO recordset is bound to the form in Access 2003?
Go to the top of the page
 
Bob_L
post Dec 21 2008, 11:35 PM
Post#14


Utterly Banned
Posts: 7,038
Joined: 5-December 02



I just thought of something - perhaps this needs to be in the form's ON LOAD event in 2007 instead of the ON OPEN event. Just a thought, but I thought I had heard someone say that the form events were slightly different in 2007 than in 2003 regarding when you can load a recordset.
Go to the top of the page
 
GroverParkGeorge
post Dec 21 2008, 11:50 PM
Post#15


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


No joy yet for me.
Go to the top of the page
 
BananaRepublic
post Dec 22 2008, 12:03 AM
Post#16


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


I built a database in 2003 (actually, a 2000 format in 2003 environ), then converted the database in 2007 and found ADODB to be updateable. This is the code I used behind the form:
!--c1-->
CODE
ars.CursorLocation = adUseClient
ars.ActiveConnection = CurrentProject.Connection
ars.LockType = adLockOptimistic
ars.CursorType = adOpenStatic
ars.Open "SELECT * FROM Table1"

The key here is the explicit declaration of CursorLocation to adUseClient. In 2000, I got an error saying that recordset wasn't valid when I tried to set it to form or the combobox, but in 2007, it silently accepts it and renders it non-updateable.
Odon't know if this is the culprit but I can update the ADODB recordset given the parameters above. Omit the line for Cursor Location and it's now non-updateable.
HTH.
Go to the top of the page
 
GroverParkGeorge
post Dec 22 2008, 12:12 AM
Post#17


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


In the form itself, through controls on the form? They're editable in VBA, via the recordset, but my reading of the problem was that the OP was able to edit them in the form.

Edited by: GroverParkGeorge on Mon Dec 22 0:14:29 EST 2008.
Go to the top of the page
 
BananaRepublic
post Dec 22 2008, 12:23 AM
Post#18


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Yep, through the form and a combobox's rowsource to boot.
Here's both samples, 2000 and 2007. A unbound form that has its recordset set at Open event, along with a unbound combobox's rowsource as well.
Attached File(s)
Attached File  RecordsetTest.zip ( 46.09K )Number of downloads: 32
 
Go to the top of the page
 
GroverParkGeorge
post Dec 22 2008, 01:02 AM
Post#19


UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA


Most cool, BananaRepublic. That works.

In addition to requring the cursor, the recordset in my test accdb is updatable with:
CODE
Private Sub Form_Open(Cancel As Integer)
im sql As String
sql = "Select * from tblQuickenData"
Dim RsClone As ADODB.Recordset
Set RsClone = New ADODB.Recordset
RsClone.CursorLocation = adUseClient
RsClone.CursorType = adOpenStatic
RsClone.LockType = adLockOptimistic
RsClone.ActiveConnection = CurrentProject.[color="red"]Connection [/color]
RsClone.Open sql
Set Me.Recordset = RsClone
Set RsClone = Nothing
End Sub


But NOT with:

CODE
Private Sub Form_Open(Cancel As Integer)
Dim sql As String
sql = "Select * from tblQuickenData"
Dim RsClone As ADODB.Recordset
Set RsClone = New ADODB.Recordset
RsClone.CursorLocation = adUseClient
RsClone.CursorType = adOpenStatic
RsClone.LockType = adLockOptimistic
RsClone.ActiveConnection = CurrentProject.[color="red"]AccessConnection[/color]
RsClone.Open sql
Set Me.Recordset = RsClone
Set RsClone = Nothing
End Sub


That's what Bob predicted and that's exactly the opposite of what the Help file said about using AccessConnection.
Go to the top of the page
 
BananaRepublic
post Dec 22 2008, 01:36 AM
Post#20


Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic


Just tested twice to verify, and I can see why if we were using 2007, we would have never figured it out.
On 2000/2003, if I omit the line for Cursor Location, an error is raised saying that recordset isn't valid. OTOH, 2007 silently accepts it and render it non-updateable. At that point, it hit me that ADO by default prefers Server-side cursor, which makes sense but in realm of binding forms or controls, Access requires more control and thus needs the cursor to be on Client side so it can handle the keys management behind the scene.
[soapbox]
This is a perfect case showing that yes, it's possible to go too far in shielding users from errors and trying to handling it behind the scenes. I realize Access is marketed toward office monkeys and not necessarily programmers, but I do think regardless of whom may actually use Access, disclipline is absolutely necessary and we can't have that if Access tries to cheerfully hide the problem and bake us a new sheet of cookies. It may be all delicious and chewy but will do absolutely no good if we asked for oatmeal raisin cookies and got chocolate chip cookies instead. So, Microsoft, I would like to ask you to re-consider the general behavior of Access and ponder the benefits of requiring the users, whatever their background may be, to exercise disclipline in developing the application instead of masking their bad programming habits and making it work, only to make the application a hopelessly convulted and complicated mess that IT wants nothing to do with it.
[/soapbox]
I hope the OP is able to reproduce the behavior.
Also, thanks for that tidbit about AccessConnection. I actually never heard of this, but good to know as the help files seems to be at odds with the tests.
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 06:57 AM