Slow datetime field in SQL-linked tables, only on Home Ribbon tab

[Pat G]

Hi,

Our shop has used ODBC linking to SQL tables from MS-Access for many years.
I have recently noticed a problem and I am not sure how long it existed
and/or if there is a fix. Our environment is Windows XP(SP2), Office
2007/MS-Access 2007, SQL 2005 and SQL 2000. The error occurs when linked to
either SQL 2005 or 2000.

We have many large tables, 40,000+ records, and most have datetime fields,
some with multiple datetime fields and these are the problem. When I create a
new linked table I choose the External Data tab in the MS-Access "ribbon" at
the top, I choose "More" under Import and go through the steps to link a
table. After completing the link, if I open the table, still within the
"External Data" ribbon tab, the table opens and I can focus on any cell, use
arrows or tab to move quickly from field to field NO PROBLEM. I can even
update the datetime field in records. However, if I select the "Home" tab
from the top ribbon, the linked table remains open and I can focus into any
cell and move using arrow keys or tabs, BUT, if I move into a datetime field,
there is a long delay(30 secs - 5 minutes) before control returns. When it
does, the total record count now appears in the bottom status bar("1 of
40,000" for example). If I move down the same datetime column there is no
more delay. However, if I move into another datetime column, the delay
recurs. It seems like the first time each datetime column receives focus it
loops through each record doing something and it takes way longer than simply
clicking the "Last Record" arrow in the bottom status bar.

If I close the table and open again the same problem occurs where the
initial focus to a datetime column results in long delays. This only occurs
when the current ribbon tab is "Home". Pretty weird, huh?

I would appreciate it greatly if someone can solve this problem but in the
meantime I can ask our users to work under ribbon tabs other than "Home".

Thanks,

Pat Grealy

 

[Sylvain Lafontaine]

First, your best bet would be to post to a newsgroup dedicated to ODBC
linked tables such as m.p.a.odbcclientsvr or m.p.a.externaldata. This one
is about ADP and SQL-Server and has nothing to do with ODBC linked tables.

In your case, the first thing to do would be to see if there is any
difference of behavior between using a MDB file or the newer format ACCDB.
You should also check if setting any index on the datetime column change
this behavior. Taking a look with the SQL-Server Profiler could also be a
very good idea because it will probably tell you if Access is doing
something on the SQL-Server. Check also for any extended properties for
these datetimes columns on the sql-server; particularly any that could be
related to any sorting order.

Did you observe the same effect if you create a duplicate of the table on
the SQL-Server? Did you see any difference if you increase or lower the
number of records?

Finally, the most important question would be: do you have the same problem
if you build a form and use it to display your data? IMHO, displaying a
linked table directly to an user should never be done as this is not the way
of doing a proper Access aplication. By doing this, you are calling for
trouble.

For the rest, I don't use ODBC linked tables nor Access 2007 so I cannot
tell you anything more on this topic.

 

[Pat G]

Sylvain,
Thanks for your quick reply. I will re-post to more appropriate place but I
do not know where the newsgroups that you suggest are located. Are they in
this Microsoft Discussion
Groups(http://www.microsoft.com/communities/newsgroups/en-us/)?

I tried most of your suggestions without success:
1) Same behavior in .MDB and .ACCDB
2) Same behavior using a form
3) Time delay directly correlated to record count; in fact, with small
record counts(<500) the delay is negligible
4) There are no extended properties; I have tried with/without index on the
datetime field; for testing, I am using the dummy SQL table with 3 fields,
40K records described below:
= = = = = = = = = =
/****** Object: Table [dbo].[PG_TestDateTime40K] Script Date: 05/16/2008
11:05:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PG_TestDateTime40K](
[N] [int] NOT NULL,
[TestString] [varchar](50) NOT NULL CONSTRAINT
[DF_PG_TestDateTime40K_TestString] DEFAULT ('Test'),
[TestDate] [datetime] NULL,
CONSTRAINT [PK_Tally_N] PRIMARY KEY CLUSTERED
(
[N] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object: Index [IX_PG_TestDateTime40K] Script Date: 05/16/2008
11:17:12 ******/
CREATE NONCLUSTERED INDEX [IX_PG_TestDateTime40K] ON
[dbo].[PG_TestDateTime40K]
(
[TestDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB =
OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
= = = = = = = = = =

Pat G.