Accessing Me.Recordset in Access XP is seriously flawed.

[Tassos Pavlakos]

It seems that Access XP is seriously flawed when it comes to accessing
Me.Recordset inside an ADP.

This has been mentioned before in this newsgroup in relation to checkboxes
but it seems that this is a more general problem.

Here are my findings:

When you access the current form's recordset in any way (for example
checking Me.Recordset("ID").Properties("KEYCOLUMN") in the OnLoad event

handler), Access XP enters some buggy mode with the following symptoms:

1) If there is a checkbox bound to a bit field, clicking on the checkbox
makes Access crash
2) Trying to programmatically change the value of *any* field of the form
(say Me.txtName="Tassos") also makes Access crash.

The above generally happen only if they immediately follow accessing
Me.Recordset.

If, on the other hand, after accessing Me.Recordset, you fiddle with, say,
some combobox, or change the value of some other (non-checkbox)

field via the UI and *then* do anything of the above, Access has magically
exited its buggy mode and everything works fine.

Until you move to the next record that is! Access XP seems to remember that
access to the recordset (regardless of where and when you did

it!), and enters the buggy mode again everytime you move to another record!

Solutions (for situations where you can't escape accessing Me.Recordset)
=======
It seems that *setting* the value of a field of the recordset makes Access
XP exit its buggy mode. Considering all the above, right after

having finished accessing Me.RecordSet and/or in the OnCurrent event handler
set the value of a field of the recordset

(Me.Recordset("Name")="blabla") and all is fine. I usually have a field
ModifiedAt in my records, to keep track of when was the last time the

record was modified, and I set that to the current datetime.

Hope this saves some hours to someone!

Comments are welcome!

[Windows 200 SP4, Access XP SP3, MDAC 2.8 SP1]

 

[Allen Browne]

Thanks for posting.

While I don't use ADPs, there's a couple of other factors you might like to
consider, and see if it makes any difference.

1. Name AutoCorrect
Does not make any difference if you uncheck the boxes under:
Tools | Options | General | Name AutoCorrect.
I have no idea whether this will help with an ADP.

2. Yes/No Fields
It's interesting that your experience is associated with a check box. JET
(the data engine in Access) is incapable of handling the Null value in a
yes/no field. Is this a required (non-nullable) field in your table? I have
seen JET crash as a result of nulls in a yes/no field (the result of an
outer join in my case.)

3. Form's Recordset
Assigning a Recordset to a form was only introduced in Access 2000 (if I
recall correctly), and it is buggy in an MDB too. Would it be possible to
assign a stored procedure to the form's RecordSource instead? Even a SQL
statement (string).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

It seems that Access XP is seriously flawed when it comes to accessing
Me.Recordset inside an ADP.

This has been mentioned before in this newsgroup in relation to checkboxes
but it seems that this is a more general problem.

Here are my findings:

When you access the current form's recordset in any way (for example
checking Me.Recordset("ID").Properties("KEYCOLUMN") in the OnLoad event

handler), Access XP enters some buggy mode with the following symptoms:

1) If there is a checkbox bound to a bit field, clicking on the checkbox
makes Access crash
2) Trying to programmatically change the value of *any* field of the form
(say Me.txtName="Tassos") also makes Access crash.

The above generally happen only if they immediately follow accessing
Me.Recordset.

If, on the other hand, after accessing Me.Recordset, you fiddle with, say,
some combobox, or change the value of some other (non-checkbox)

field via the UI and *then* do anything of the above, Access has magically
exited its buggy mode and everything works fine.

Until you move to the next record that is! Access XP seems to remember
that
access to the recordset (regardless of where and when you did

it!), and enters the buggy mode again everytime you move to another
record!

Solutions (for situations where you can't escape accessing Me.Recordset)
=======
It seems that *setting* the value of a field of the recordset makes Access
XP exit its buggy mode. Considering all the above, right after

having finished accessing Me.RecordSet and/or in the OnCurrent event
handler
set the value of a field of the recordset

(Me.Recordset("Name")="blabla") and all is fine. I usually have a field
ModifiedAt in my records, to keep track of when was the last time the

record was modified, and I set that to the current datetime.

Hope this saves some hours to someone!

Comments are welcome!

[Windows 200 SP4, Access XP SP3, MDAC 2.8 SP1]

 

[Sylvain Lafontaine]

Since the latest service pack (SP2) for WinXP, the increased instability of
ADP-XP has been mentionned many times. I don't know specifically about
Win2000 but if I were you, I would stop losing my time with A2002 and buy
the 2003 version.

Adp2003 is still very buggy but much less than Adp2002.

 

[Larry Linson]

Adp2003 is still very buggy but
much less than Adp2002.

So, maybe it is time for the original poster to follow current advice and go
back to MDB, Jet, and ODBC?

 

[Sylvain Lafontaine]

No, because I know how to circumvent the bugs that have hurted me in the
past and I'm able to achieve a much greater speed over the Internet with ADP
than with linked tables.

Of course, I'm pushing a little my next client to go with .NET instead of
Access (either ADP or MDB) but it's him who will take the decision. (If he
becomes my next client, of course! Otherwise, I don't care what he will
choose.)

However, if by your post you are making the statement that ADP is clearly an
unfinished business and that MS has made the decision of either pushing the
people back toward MDB files and linked tables as the low level entry to
enterprise databases or pushing them forward the .NET framework for more
serious business instead of finishing it, than I could not agree more with
you.

 

[Larry Linson]

However, if by your post you are making
the statement that ADP is clearly an
unfinished business and that MS has made
the decision of either pushing the people
back toward MDB files and linked tables as
the low level entry to enterprise databases
or pushing them forward the .NET framework
for more serious business instead of
finishing it, than I could not agree more with
you.

I didn't exactly say that in this post, but what you write does reflect my
understanding of Microsoft's actions in this area. I will be astonished if
there is significant "finishing" of ADPs in the next version of Access, but
as far as I can tell, ODBC-compliant databases will still be usable via
linked tables. ADPs have not been appropriate for most of the client-server
work I have done, because the server databases were other than Microsoft SQL
Server.

Larry

 

[Tassos Pavlakos]

1. Name AutoCorrect

Does not make any difference if you uncheck the boxes under:
Tools | Options | General | Name AutoCorrect.
I have no idea whether this will help with an ADP.

This option does not exist in ADPs.

2. Yes/No Fields
It's interesting that your experience is associated with a check box. JET
(the data engine in Access) is incapable of handling the Null value in a
yes/no field. Is this a required (non-nullable) field in your table? I have
seen JET crash as a result of nulls in a yes/no field (the result of an
outer join in my case.)

ADPs work with the SQL Server as the backend. SQL Server is capable of
handling the Null value. I have heard that the representation of bit/boolean
differs in SQL Server and Access, but that doesn't seem to be the actual
problem in the case I describe.

3. Form's Recordset
Assigning a Recordset to a form was only introduced in Access 2000 (if I
recall correctly), and it is buggy in an MDB too. Would it be possible to
assign a stored procedure to the form's RecordSource instead? Even a SQL
statement (string).

I use the Recordset in order to (automatically for each form) get access to
the metadata of the relevant table. I could revert back to not using
Recordset, but I thought I first try and find if I could bypass the bug(s) I
mention above.

Thanks for your feedback
tas

 

[Allen Browne]

Can you get at the Fields of the RecordsetClone instead of the Recorset?

Again, I don't use ADPs, but I don't see why that would not work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Tassos Pavlakos]

This seems to work in a test project that I created to isolate the problem,
but not in the actual project I'm working on...

So I guess I'll have to stick with the (ugly) hack I discovered...

Thanks anyway
tas

 

[Tassos Pavlakos]

In addition to the above, I have also discovered that Access (re)enters into
its buggy mode each time you use Me.Undo/DoCmd.RunCommand acCmdUndo...
Again changing the value of a field through the form's recordset, seems to
make Access exit buggy mode (it keeps getting worse, I know, but usually
that's the way ugly hacks end up isn't it?).

 

[Tassos Pavlakos]

I would be more than happy to do so, but in the corporate environment that I
work, it's not a matter of my choice unfortunately...

tas

Since the latest service pack (SP2) for WinXP, the increased instability of
ADP-XP has been mentionned many times. I don't know specifically about
Win2000 but if I were you, I would stop losing my time with A2002 and buy
the 2003 version.

Adp2003 is still very buggy but much less than Adp2002.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

It seems that Access XP is seriously flawed when it comes to accessing
Me.Recordset inside an ADP.

This has been mentioned before in this newsgroup in relation to checkboxes
but it seems that this is a more general problem.

Here are my findings:

When you access the current form's recordset in any way (for example
checking Me.Recordset("ID").Properties("KEYCOLUMN") in the OnLoad event

handler), Access XP enters some buggy mode with the following symptoms:

1) If there is a checkbox bound to a bit field, clicking on the checkbox
makes Access crash
2) Trying to programmatically change the value of *any* field of the form
(say Me.txtName="Tassos") also makes Access crash.

The above generally happen only if they immediately follow accessing
Me.Recordset.

If, on the other hand, after accessing Me.Recordset, you fiddle with, say,
some combobox, or change the value of some other (non-checkbox)

field via the UI and *then* do anything of the above, Access has magically
exited its buggy mode and everything works fine.

Until you move to the next record that is! Access XP seems to remember
that
access to the recordset (regardless of where and when you did

it!), and enters the buggy mode again everytime you move to another
record!

Solutions (for situations where you can't escape accessing Me.Recordset)
=======
It seems that *setting* the value of a field of the recordset makes Access
XP exit its buggy mode. Considering all the above, right after

having finished accessing Me.RecordSet and/or in the OnCurrent event
handler
set the value of a field of the recordset

(Me.Recordset("Name")="blabla") and all is fine. I usually have a field
ModifiedAt in my records, to keep track of when was the last time the

record was modified, and I set that to the current datetime.

Hope this saves some hours to someone!

Comments are welcome!

[Windows 200 SP4, Access XP SP3, MDAC 2.8 SP1]