Incredibly Slow Access Times in Access 2007

[Mark Olbert]

I have a Sql Server 2008 database that has two image fields, each of which holds a PDF of about 25KB in size.

Working with the database through Access 2007 is unbelievably slow. In fact, it's almost totally unusable slow. I even get timeouts
just posting a new record to the database. Which isn't running on my local machine, BTW; it's on a server on my LAN.

Is there some trick to tuning either Access or SqlServer 2008 to get this sucker to be a little snappier?

- Mark

 

[Sylvain Lafontaine]

Try hiding them under a tab or better, put them on a separate form to be
loaded only when necessary when the user click on a button.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Mark Olbert]

I'm working through the datasheet view, not a form (it is, or should be, a very simple application).

One thing I've noticed is that inserting a new record with image fields is very "expensive" in terms of time. Inserting a new
record, absent the image fields, and then editing the record to add the image fields is much faster (although still oddly slow).

The time cost seems to rise "geometrically" the more image fields there are in a record (the one I'm using contains two).

Why is that?

- Mark

 

[Paul Shapiro]

You can check a few things to see if they help:
1. Updated network drivers on the server and the workstation.
2. Access 2007 SP1 with all post-SP1 hotfixes.
3. Access files (mdb or adp) excluded from virus checking on both the server
and workstation.
4. Each user is running their own copy of the adp from their local disk
drive.

 

[Mark Han[MSFT]]

Hi Mark,

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

From your description, I understand that it is very slowly to remotely post
a new record to the SQL 2008 database from access 2007 adp
If I have misunderstood, please let me know.

To better understand the issue, I would like to re-produce the issue. if it
is convenient to you, please send me a email and then I will create a
FTP(workspace) for you to upload the required files. my email address is
(e-mail address removed) (remove online)

Besides, please to test if the issue will happen between access 2007 and
SQL Server 2005; and post the error message here.

If there is anything unclear, please do not hesitate to let me know. Have a
nice day!

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.
=========================================================

 

[Mark Olbert]

Mark,

The problem seems to be in the way SqlServer (2008; I don't have 2005 available) handles INSERTs on records containing one or more
image fields. I say this because the same problem shows up when working with the same database through SqlServer Management Studio,
rather than Access.

If you have an image field, the INSERT is very slow. If you don't, it's normal.

- Mark

 

[Mark Han[MSFT]]

Hi Mark,

I agree with you. the issue might be related to the SQl Server 2008. and I
try to re-produce the issue described by you. But I fails. So if it is
convenient to you, could you post your code to insert record containing
image fields?

Besides what is the version of the SQL Server 2008?

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.
========================================================

 

[Mark Han[MSFT]]

Hi Mark,

I haven't heard from you for a few days. i understand that you might have a
busy schedule.

if it is convenience, please send me a note to tell me the status of the
issue. Besides, if you have questions or concens on posting the code to
insert the recore containing, pleae let me know.

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).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Mark Olbert]

Mark,

Sorry I've been off-forum for a bit. I've determined that the problem relates to the size of objects stored in the image field.
They're much bigger than the actual binary data in the file that gets inserted into the field.

I've started a separate thread on this because the question is a bit different.

- Mark

 

[Mark Han[MSFT]]

Hi Mark,

Thank you for the update.

Based on the current situation, I agree with you that the issue might be
related to the size of the object stored in the image field.

According to your description, i know that you have started a separate
thread on the issue. Thank you for the understanding and cooperation.

if you need us further assistance, feel free to let me know.

Have a nice day.

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.
=========================================================