Running an Access project crashes Access 2007 SP1

[John]

I am currently testing an older Access project for compatibility with Access
2007, SP1 prior to a roll-out.

I'm experiencing a problem with a complex form that fails to open and
crashes Access 2007. It runs fine on Access 2003. The database is SQL Server
2000. Using references to ADO 2.5, 2.7 or 2.8 makes no difference.

The form has 6 tabbed pages. On one of the tabbed pages there is a sub-form.
That sub-form has 2 tabbed pages and one of those tabbed pages has 5
sub-forms. The form has about 23 sub-forms in all.

A routine in a loop binds an ADO Recordset to each of the sub-forms using
the expression:

Set frm.Form.Recordset = rs

The problem begins when I get to the 5 sub-forms on the nested tabbed page.

I know the recordsets are created. For the first two, the expression appears
to execute but the value of frm.Form.Recordset is Nothing and there is no
crash. On the third, Access 2007 crashes (outside the debugger) or hangs
(inside the debugger). Ending task on the latter occasionally reboots the
machine (XP Pro, SP2).

This project was created (upsizing an earlier .MDB file) under Office XP and
migrated to Office 2003 with no problem, although I've had an issue recently
with a couple of controls (textbox and combobox) suddenly getting "lost" on
the form (i.e. they couldn't be located by Access even though they were
recognized in the debugger). In this case, I recreated these controls from
scratch and the project worked fine afterwards.

Recreating the crashing sub-form from scratch has not solved the problem.
Nor has re-creating the project from scratch and importing all the objects.

I'm open to any suggestions.

Thanks in advance,

John

 

[Sylvain Lafontaine]

I don't see the purpose of using ADP instead of MDB if you manually set the
frm.form.Recordset to a recordset. While the best option would be that
Acces 2007 doesn't crash, in your case, you should try with regular forms'
and subforms' recordsets; with the forms and subforms data sources pointing
toward a stored procedure or a Select statement.

Also, it's possible that the problem comes from the code/method used to
create these external recordsets but you give no detail on this matter.

 

[John]

The ADO code that creates the recordsets is pretty standard--it could have
been copied and pasted out of the Help example. The SQL statements that
populate these recordsets are stored in an array which is initialized at
startup.

The aplication was upsized due to poor performance over a wide-area network.
As I mentioned it runs fine under Access 2003.

Is there any behavior differences with sub-forms and tabbed pages between
Access 2003 and Access 2007? It looks like the sub-forms are lost on the
form or perhaps the tabbed page.

John

 

[Sylvain Lafontaine]

The aplication was upsized due to poor performance over a wide-area

network. As I mentioned it runs fine under Access 2003.

If you are directly binding your recordsets to your forms/subforms, I don't
see why using an ADP project should make these going faster. In my opinion,
you should see the exact same speed under this case scenario with or without
an ADP; so you should either keep your MDB file or, if you want to go the
ADP road, go for it for good; without stopping half-way.

If you can't solve your speed problem; another solution would be to remote
desktop; using Terminal Server or some dedicated workstations (these
workstations could be virtualized). This is usually the easiest solution
for accessing a database over the WAN.

 

[John]

My apologies for not getting back sooner, but I cannot access this newsgroup
from work.

I did manage to find a solution to the problem after spending much time with
the debugger, although I'm not sure why three sub-forms failed to bind their
recordsets without crashing Access while the other three failed
spectacularly. I saw that the initialization of a major sub-form (which had
a tab control with two pages) fires that sub-form's Current event which was
used to retrieve the data for the 6 additional sub-forms on those two pages.
In Access 2003 this didn't create a problem, but something about the object
creation sequence or object location context in Access 2007 is different,
Postponing the databinding until further in the initialization process of
the main form solved the problem. The line

frm.Form.Recordset

would seem to mean something different when executing from within the
context of a sub-form as opposed to the main form (even though the debugger
had no difficulty identifying the sub-form). From the latter, the Recordset
property is visible and the recordset binds. From the former, it doesn't.
Perhaps in Access 2003 the behavior was the same, only it was handled more
gracefully. I haven't spent time debugging the code in Access 2003 as it
wasn't failing, but may take a look just for my own satisfaction.

I'm confused by the rest of your response. This application has a long
heritage. Originally it was a combined Access/Jet database which was later
split into an Access front-end and a separate Jet back-end using linked
tables. The back-end was upsized SQL Server 2000 about 4 years ago and a
decision was made to go with an Access project and ADO rather than use ODBC.
I was not involved in the upsizing, but Microsoft and most authorities were
recommending ADO and Access data projects at that time. Those who have used
the application over the years stated the upsizing was a vast improvement. I
find it strange that Microsoft is now recommending .ACCDB front-ends using
ODBC to connect to SQL Server, since ODBC is a deprecated technology.

John

 

[Sylvain Lafontaine]

My apologies for not getting back sooner, but I cannot access this
newsgroup from work.

I did manage to find a solution to the problem after spending much time
with the debugger, although I'm not sure why three sub-forms failed to
bind their recordsets without crashing Access while the other three failed
spectacularly. I saw that the initialization of a major sub-form (which
had a tab control with two pages) fires that sub-form's Current event
which was used to retrieve the data for the 6 additional sub-forms on
those two pages. In Access 2003 this didn't create a problem, but
something about the object creation sequence or object location context in
Access 2007 is different, Postponing the databinding until further in the
initialization process of the main form solved the problem. The line

frm.Form.Recordset

would seem to mean something different when executing from within the
context of a sub-form as opposed to the main form (even though the
debugger had no difficulty identifying the sub-form). From the latter, the
Recordset property is visible and the recordset binds. From the former, it
doesn't.

I don't really understand how you are using this piece of code into your VBA
code but obviously, the name "frm" is the name of a control (form control)
who does exist on the main form but don't exist on the subform. I don't see
how you could make a reference to this control (located only on the parent
form) directly from the subform; without making first a reference to the
parent form. The fact that this work in Access 2003 is probably a bug in my
opinion that has been corrected in A2007.

The debugger knows it probably because it keep the contextual (environment)
information of the variable.

Perhaps in Access 2003 the behavior was the same, only it was handled more
gracefully. I haven't spent time debugging the code in Access 2003 as it
wasn't failing, but may take a look just for my own satisfaction.

I'm confused by the rest of your response. This application has a long
heritage. Originally it was a combined Access/Jet database which was later
split into an Access front-end and a separate Jet back-end using linked
tables. The back-end was upsized SQL Server 2000 about 4 years ago and a
decision was made to go with an Access project and ADO rather than use
ODBC. I was not involved in the upsizing, but Microsoft and most
authorities were recommending ADO and Access data projects at that time.
Those who have used the application over the years stated the upsizing was
a vast improvement. I find it strange that Microsoft is now recommending
.ACCDB front-ends using ODBC to connect to SQL Server, since ODBC is a
deprecated technology.

John

What I don't understand is why you are making a direct assignation of the
recordset rs to the property Me.Recordset - using the code « Set
frm.Form.Recordset = rs » instead of using standard record sources such as a
Select query or calling a stored procdure.

 

[John]

What I don't understand is why you are making a direct assignation of the
recordset rs to the property Me.Recordset - using the code « Set
frm.Form.Recordset = rs » instead of using standard record sources such as
a Select query or calling a stored procdure.

A sub-form itself doesn't expose a Recordset property, but as it is a form
the Recordset property can be accessed through the Form property of the
sub-form. So frm refers to the sub-form, Form to the sub-from's Form
property. The data in the recordset is then bound to the datasheet for
display and manipulation. The data stored in the recordset is retrieved by
an SQL statement which happens to be stored in an array which is assigned to
the CommandText property and executed. Writing this code as a stored
procedure is another possibility, but the pressures of getting the upsizing
done as quickly as possible led to the developer falling back on his VB6
experience to initialize the 20+ sub-forms from within a loop looking up the
SQL statements in the array and using standard boilerplate code (as I
mentioned, you can find this ADO code in the Help system) to retrieve the
data.

Although I maintain this project, my background isn't Access. I've talked
with a veteran Access developer who mentioned that the syntax for
referencing sub-forms is convoluted, possibly because Access uses a form to
contain other forms. It seems odd for an object at the same level of the
hierarchy to contain like objects, but the designers of Access saw this as
desirable and have made it work.

This application will be ticketed fro a re-write, possibly within the next
year or two and will have the opportunity to modernize the architecture.

John

 

[Sylvain Lafontaine]

A sub-form itself doesn't expose a Recordset property, but as it is a form
the Recordset property can be accessed through the Form property of the
sub-form. So frm refers to the sub-form, Form to the sub-from's Form
property. The data in the recordset is then bound to the datasheet for
display and manipulation. The data stored in the recordset is retrieved by
an SQL statement which happens to be stored in an array which is assigned
to the CommandText property and executed. Writing this code as a stored
procedure is another possibility, but the pressures of getting the
upsizing done as quickly as possible led to the developer falling back on
his VB6 experience to initialize the 20+ sub-forms from within a loop
looking up the SQL statements in the array and using standard boilerplate
code (as I mentioned, you can find this ADO code in the Help system) to
retrieve the data.

This might be the heart of your problem: you are coding an ADP project like
if it was a VB6 application. If your programmer want to keep coding in VB6,
he should have kept the VB6 IDE to develop the application instead of
switching to ADP.

Although I maintain this project, my background isn't Access. I've talked
with a veteran Access developer who mentioned that the syntax for
referencing sub-forms is convoluted, possibly because Access uses a form
to contain other forms. It seems odd for an object at the same level of
the hierarchy to contain like objects, but the designers of Access saw
this as desirable and have made it work.

Nothing strange in that. A lot of modern frameworks give you the
possibility of having a collection of objects of the save level inside
another object. Probably that almost anyone doing object orienting
programming see that on a regular basis; especially for some high level
objects like GUI objects. I'm surprised to see that you find this
surprising.

This application will be ticketed fro a re-write, possibly within the next
year or two and will have the opportunity to modernize the architecture.

Very good idea indeed but don't forget to tell the next programmer to leave
behind VB6 for good.

 

[John]

This might be the heart of your problem: you are coding an ADP project
like if it was a VB6 application. If your programmer want to keep coding
in VB6, he should have kept the VB6 IDE to develop the application instead
of switching to ADP.

No, the heart of the problem was the changed behavior when initializing a
group of sub-froms from another sub-form's Current() event in Access 2007.
The earlier developer needed a way to retrieve data for more than 20
sub-forms and wrote a generic routine to do it. That routine looks up and
executes SQL statemnents stored in an array. It works correctly and performs
well. How would you do it differently? The programmer didn't have unlimited
funds to get the job done.

Nothing strange in that. A lot of modern frameworks give you the
possibility of having a collection of objects of the save level inside
another object. Probably that almost anyone doing object orienting
programming see that on a regular basis; especially for some high level
objects like GUI objects. I'm surprised to see that you find this
surprising.

I just want to be sure you understand my point. Are you aware of frameworks
where a combo box is a collection for other combo boxes or a text box a
collection for other text boxes? That is the relationship of a form to a
sub-form, as the latter is a form in its own right. I imagine the Access
team worked very hard to get this to work as they wanted a very transparent
way of rendering one-to-many data relationships.

John

 

[Guest]

I just want to be sure you understand my point. Are you aware

where a combo box is a collection for other combo boxes or a

That is not a very good example, because a form is a container
object. A combo box is not a container object: a text box is not
a container object.

I'm sure that the original developers worked very hard to make
forms container objects. Also, it was very demanding on the
hardware and Operating System at the time: most frameworks
avoided full blown container objects for that reason. Even
Access cheats in one place: a form data sheet is not a collection
of objects, it is a single window with data painted on it to
make it look like a collections of objects.

On the other hand, a form object is already a child object,
sometimes a child of the desktop but normally a child of
the Application, so all of the 'child object' stuff already existed.
And this is a child object where you can have multiple siblings.
Most Windows applications can not run the main application
window as a child, and cannot have siblings, but Access forms
are already subforms of the Application window. That is
not just an analogy, in the 1st and second version, the Access
interface was more-or-less built on the same technology using
form/subform windows inside the main application window.
(The property window is still a subform window, but AFAIK
the technology is no longer shared).

As an aside, look at kb/956054. 4 Files are updated in the hotfix:
Accwiz.dll
Msaccess.exe
Msaexp30.dll
Soa.dll

I don't recognise MsaExp30, but SOA and AccWiz are both
libraries which were originally written in Access Basic and
ran in Access.

However, the interesting thing is that the Access 2.0 developers
did so well, and had such a coherent vision and implementation.
Every version since, including A97 (which is often considered
the best version) has weakened that vision and that implementation.

By the way, the FORM property you are using is a hangover
from Access 2.0, that is, it is part of an object model which
predates COM. All forms had a FORM property, because
the forms weren't proper objects themselves, and both VBA
and the macro language needed something they could reference
as the form object. In the DAO COM object model, the
sub-form DOES have a recordset property. The fact that the
recordset property is missing from the ADP subform, and you
have to use a backward compatible kludge, indicates that the
ADP developers, while doubtless smarter than me, implemented
a lightweight solution, and probably thought they were much
cleverer than their predecessors.

Regarding the fact that your application worked correctly
in Access 2003, and fails in 2007, note that you were just
lucky to start with: most developers abandoned the idea
of assigning recordsets to adp forms, because although it is
a very good idea which we all wanted for a long time, the
implementation, like much of what was new in Access 2000,
was broken and flaky, with problems like you now have.
You may also be lucky that it works at all: several of these
backward-compatibility features not used by many people
have quietly disappeared over the years.

Having said that, the failure in 2007 has all the hallmarks of
another 'optimisation' such as we have been inflicted with
over the last 10 years. Someone has decided to optimise
delay loading of the subforms, and the form objects haven't
been created before they are used. This is a worthwhile
optimisation, because the subform loading sequence is
very sub-optimal and data intensive, doubtless because
fixing it was harder than it looked. The form loading
sequence takes place outside the VBA exception handler
(separation of the VBA and Access components), and
the whole application is closed when there is a memory
fault.


(david)

 

[Sylvain Lafontaine]

The big problem with directlysetting the recordset property of a subform is
what happens or what should happen when you have a continuous form as your
main form; because in this case scenario, the same subform can be repeated
multiple times but each time with a different set of data.

People using ODBC link and passthrough queries have this exact same problem
when dealing with reports because reports often have sub-reports and a
passthrough query cannot be directly used with a subreport.

If we get back to the original problem, my first suggestion was - and
remains - to use the Record Source instead of the Recordset property.

However, some people have the exact same problem for MDB or ACCDB data
file - and not just with ADP - when they are using the Recordset property
and are switching to A2007. A workaround that seems to work is to make sure
that there is not attempt to set the recordset of a subform when setting the
recordset of the main form is not finished:


Dim MainInPopulate as Boolean

Private Sub Form_Open(Cancel As Integer)

MainInPopulate= True
Call PopulateMainForm
MainInPopulate = False

Call PopulateSubForm(Clng(Nz(Me.Text1, 0)))

End Sub

Private Sub Form_Current()

If MainInPopulate = True Then
Exit Sub
End If

Dim lngId As Long
lngId = Nz(Me.Text1, 0)
Call PopulateSubForm(lngId)

End Sub

I would tell that there are other potential problems lurking around; like
the infamous error 3420 when you try to use the RecordsetClone property for
navigating around after a full requery of the form/subforms.

 

[Sylvain Lafontaine]

Sorry, I forgot to give credit for the previous workaround; it's from
Giorgio Rancati,
[Office Access MVP].

 

[Sylvain Lafontaine]

Another suggestion put forward by Armen Stein for a similar problem would be
to add DoEvents calls into the code at various points in order to give
asynchronous processes in Access a chance to catch up with what's happened.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Sorry, I forgot to give credit for the previous workaround; it's from
Giorgio Rancati,
[Office Access MVP].

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

The big problem with directlysetting the recordset property of a subform
is what happens or what should happen when you have a continuous form as
your main form; because in this case scenario, the same subform can be
repeated multiple times but each time with a different set of data.

People using ODBC link and passthrough queries have this exact same
problem when dealing with reports because reports often have sub-reports
and a passthrough query cannot be directly used with a subreport.

If we get back to the original problem, my first suggestion was - and
remains - to use the Record Source instead of the Recordset property.

However, some people have the exact same problem for MDB or ACCDB data
file - and not just with ADP - when they are using the Recordset property
and are switching to A2007. A workaround that seems to work is to make
sure that there is not attempt to set the recordset of a subform when
setting the recordset of the main form is not finished:


Dim MainInPopulate as Boolean

Private Sub Form_Open(Cancel As Integer)

MainInPopulate= True
Call PopulateMainForm
MainInPopulate = False

Call PopulateSubForm(Clng(Nz(Me.Text1, 0)))

End Sub

Private Sub Form_Current()

If MainInPopulate = True Then
Exit Sub
End If

Dim lngId As Long
lngId = Nz(Me.Text1, 0)
Call PopulateSubForm(lngId)

End Sub

I would tell that there are other potential problems lurking around; like
the infamous error 3420 when you try to use the RecordsetClone property
for navigating around after a full requery of the form/subforms.

 

[lynn]