#Name? until F5, then fine - ACC2007 SQL Server 2005 Vista Busines

[NKTower]

We have an ADP app that behaves just fine on XP platform w/ Access 2003 and
SQL Server 2005. One client recently added a Vista Business desktop w/
Access 2007 on it. On that machine only, when the client asks for any
record, the subform returns #Name? in every field until we hit F5 and then it
is just fine. The form is bound to a SQL statement that is generated on the
fly and put in the RecordSource property. The query will always return one
known-to-exist record.

I have replicated the problem on a Vista Business platform with Access 2007
and SQL Server Express. I've put in request for hotfix kb/936519 but not
gotten the link yet.

And while we are at it, where do they hide the release/SP level in Access
2007? I've yet to find HELP/ABOUT. I presume that on my test machine it is
baseline release straight off of MAPS DVD.

 

[Sylvain Lafontaine]

The first thing to do - even before applying the service pack - would be to
check the references and to decompile/recompile the ADP project directly on
the target machine (ie, the Vista Business desktop); not on another machine
like a WinXP workstation.

Of course, after applying the SP, you should decompile and recompile the ADP
file again like it should be with any other SP.

 

[Sylvain Lafontaine]

Could you elaborate just a bit -
a.. decompile ? Is there such a process? I have gone into VBA on the
Vista/Access 2007 machine and made some trivial changes and then recompiled.
Is there something more to it than that?
b.. Does decompile/recompile imply that we are going to have to maintain
two releases because if we build it in 2007 the .adp won't work reliably on
XP?
c.. check references - I presume you mean TOOLS / REFERENCES and make sure
that there are no "Missing" items
This is really bizarre as the form behaves just fine in XP, but shows the
#Name? in VIsta/Access 2007 until you press F5 at which point the data
displays properly. It has worked fine w/o changes on XP/2003 platforms for
several months.

I obtained and downloaded the hotfix for KB 956519 - "When you try to run a
Microsoft Access project (adp) in Access 2007, the project may run slowly.
Or, the project may take longer than expected to respond to actions that are
performed on the project."

The hotfix did not install - "Expected version not found."


I don't think you need to read this, but I'll put it in for completeness:
I have been working with Access since 1.1, but am completely new to Access
2007. I'm not sure that I think that the "ribbon" is an improvement. Part
of the problem is that I am visually handicapped (low vision) / legally
blind so the loss of the text menus is not a good thing for me. The pretty
graphics with transparency of the Aero GUI are a disaster for me. I have
not been able to find where the version/release number of Access is.

6 months ago, when I was called in on the project, there was a problem with
the "Employee Maintenance" form. It was a tabbed form, where the main form
was used to select an employee, and the tab control had 7 tabs across, each
with a sub-form on it. Some of the subforms were also tabbed with linked
subforms as well. Each subform had many controls on it. Of course when the
main form was positioned there was a major delay as all of the data was
retrieved at once. The client "hit the wall" when trying to add a 8th tab
and subform - hard crash in design view. I believe that it was due to
having too many controls in the combined form/subforms/subsubforms.

I broke the link to all of the subforms and just used the tab for navigation
I introduced a single dummy subform, called sfm_EM_Tab___Placeholder to hold
down the position below the tabs. Then at run time, in the tab's OnChange
event, I use a SELECT statement ...

Select Case tab_number
Case 0 ' Member
Me.sfm_EM_Tab___Placeholder.SourceObject = "sfm_EM_Tab_0_Member"
Set sform = Me.sfm_EM_Tab___Placeholder.Form
sform.InputParameters = "@id=" & Trim(Me.ID)
Set sform = Nothing
Case 1
.... ( other subforms may have different/more selective input parameters )
.... etc
End Select

As I said, this works just fine in XP

Regards,
Bruce

 

[Sylvain Lafontaine]

First, I agree with you that in its actual incarnation, the Ribbon is a
disaster for many people. I've myself a problem with my eyes and I see
everything a little fuzzy at this moment because I don't/can't wear my
contact lenses anymore so I totally understand your situation (even if my
situation is far from being as worse as yours).

For the decompiling process, this can be accomplished in two ways. The
first way is to create a shortcut to open the MDB file and add the option
/decompile in it. If you know how to create such a shortcut, it's an easy
way to do it.

The second way is to change the references in the TOOLS / REFERENCES
window: you add a dummy reference - one that you don't really need - and you
close the dialog window by clicking on the button OK (important). By
changing the references, you are forcing Access to recompile everything the
next time it need to execute some piece of VBA code. Of course, removing a
dummy reference will have the same effect as adding it. This is the method
that I'm usually using because it's fast and easy. Of course, after that,
it's not a bad idea to make a COMPACT / REPAIR.

It's also possible that you have some form of corruption or there has been a
problem when upgrading the project from A2003 to A2007, so another
possibility would be to create an empty ADP project and import everything
into it. You can also try to see what happens if you copy this single form
to another empty project in order to test it.

A final possibility would be to make an explicit requery of the form after
changing the InputParameters property; as a requery is usually not done
automatically after changing this property. However, you've said that this
was working properly on WinXP, so I cannot really tell you anything more
here.

For getting the version of Access (the About box), click on the big Round
button then click on the Access Options button that will be on the lower
right corner. After that, choose RESOURCES at the bottom left and then
ABOUT at the bottom right.

 

[Sylvain Lafontaine]

Also, instead of changing the InputParameters property, maybe that changing
the RecordSource property in order to pass the value of the @id parameter
will work better; something like:

sform.RecordSource = "EXEC MyStoredProcedure " & Trim(Me.ID)

 

[Robert Morley]

For the decompiling process, this can be accomplished in two ways. The
first way is to create a shortcut to open the MDB file and add the option
/decompile in it. If you know how to create such a shortcut, it's an easy
way to do it.

Another thing I've discovered about decompiling, at least under Access
2002/2003 (though I don't believe it was useful under any previous version),
is that if you're using the /decompile switch, do it once without holding
the Shift-key down, thus allowing any startup code to run; then do it AGAIN
holding down the Shift-key as the database opens. For some reason, this
will do a "fuller" decompile than either of the two alone.

I've never figured out what's going on behind the scenes to cause this
strange behaviour, but I've seen evidence of it on several large databases.
After compacting, they will often be several hundred K smaller when the
above sequence is followed compared to just a normal, single decompile and
compact.


Rob

 

[Robert Morley]

Sorry, re-reading my own post, I didn't really make it clear: it's not
specifically the version of Access that seems to cause the strange
behaviour, it's the version of the database itself. 2000-format databases
don't seem to exhibit the behaviour; 2002/2003-format databases do,
regardless of whether they're MDBs or ADPs.


Rob

 

[Sylvain Lafontaine]

Hi, I just repeated the test with an ADP project without any autorun code
and this is true, repeating the decompilation step a second time do increase
the level of compaction. However, I didn't notice any difference with or
without the use of the Shif-Key.

This also worked the same when using the switch /compact along with the
/decompile switch: repeating the process a second increase the level of
compaction. The final result is the same wheither or not the switch
/compact to make the compaction step.

However, the method using a change in the References didn't work as well; so
when there is a compilation problem, maybe we shouldn't use it.

 

[Robert Morley]

Interesting...especially with the references!


Rob