Access 2007 Strange Report Design Behavior

[AG]

Access 2007 front end ODBC linked SQL Server 2005 back end.

Any report with a view, table, query or passthrough query as a recordsource.

It seems that just about any design activity causes Access to query the
source.

This is in design view.

Start a new report based on anything with an ODBC source. Try to add new
controls by dragging from the field list.
Access queries the source once for each control, for each table in the
source. E.G. if a view is based on 5 tables, the full table is queried for
each control that uses it for a source.
When the tables contain a lot of records, it can take several minutes before
the controls can be added to the report. This is all with a 3.2G quad
processor maxed out.

I am monitoring this in SQL Profiler, so I am not guessing.

Once controls are added to a report, just clicking on a bound control,
causes Access to query the full table again - if the property sheet is
displayed.
So, if I just want to move or reformat or whatever a control, I need to wait
for Access to finish OR constantly close and reopen the property sheet.

The problem has nothing to do with the source (view, qurery, etc.)
efficiency. The reports perform very well at runtime.

There is similar behavior with some bound forms.

Does anyone know why this is happening or have a workaround?

Thanks,

 

[Mark Han[MSFT]]

Hi Ag,

Thank you for using Microsoft MSDN Managed Newsgroup. My name is Mark Han.
I am glad to work with you on this issue.

From your description, I understand that you meet a performance issue when
adding control into the report.
If I have misunderstood, please let me know.

Based on your description, it seems to be a product issue. I would like to
re-produce the issue. So please tell me the version of the access and the
SQL Server. To apply the current SQL service package might be helpful to
the issue.

Besides, if possible, could you share me with the profiler trace which
capture the process. my email is (e-mail address removed)(remove
online)

I look forward to your update.

Best regards,
Mark Han
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or
a Microsoft Support Engineer within 2 business day is acceptable. Please
note that each follow up response may take approximately
2 business days as the support professional working with you may need
further investigation to reach the most efficient resolution.
The offering is not appropriate for situations that require urgent,
real-time or phone-based interactions. Issues of this nature are
best handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx

============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Thanks for the reply Mark.

I am using Access 2007 SP1, SQL Server SP2.

Please re-read my post. The issue is not just when adding a control.
All one needs do is click on a control in design view.

This is obviously an Access issue and not a SQL Server issue.

I will send you a simple accdb based on the AdventureWorks SQL Server
database and the trace.

 

[Mark Han[MSFT]]

Hi AG,

Thank you for the update. I have re-readed the post. now I better
understand the issue. I agree with you that the issue is much more related
to the access application.

Besides, my email is (e-mail address removed),com (remove online.) I
double checked my email box; it seems that I don't receive your email. That
might be caused by some internet issue. So, if it is convenient to you,
please send email to me again. besides, I would like to share you my
personally email address.
(e-mail address removed) (remove online).

I look forward to your email.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Mark,

I just re-sent it. I had not removed 'online' as the instruction had wrapped
in my newsreader, so I missed it.

 

[Mark Han[MSFT]]

Hi AG,

Thank you for the update. I have double checked my mail box, however I
don't recevie your email. That might be caused by some internet issue. If
it is convenient to you, please send me a note again via the following 2
address. Once I receive your email, I will create a FTP(workspace) for you
to upload the required file. Since it is limited to pass 5KB file by email,
it is not very convenient to attache the files and send to me by email.
email address: (e-mail address removed)(remove online)
(e-mail address removed)(remove online).

I look forward to hearing from you.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[AG]

Email sent.
The original attachement was 31K, so I guess that was the reason for not
going through.

 

[AG]

Mark,

This process had taken way too long, so I opened a support inicident and my
problem was resolved within an hour.
There is a hotfix for the issue.

 

[Mark Han[MSFT]]

Hi AG,

Thank you for the update.

It took me so long time to receive your email. Sorry for the inconvenience.
As I explain in my first response to you, to install the recent hot
fix/service package might resolve the issue.

Besides, I'm gald that the issue is resolved by a hotfix. if you have any
other questions or concerns, please let me know.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================