Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Why does Access 2007 make many calls to SQL Server looking for Extended Properties?

Posted on 2008-10-23
9
Medium Priority
?
1,009 Views
Last Modified: 2013-12-05
I have seen some posts on the internet (i.e. Expert Exchange) regarding this, but would like to try a new question. I am having the same problem.

I have an Access 2007 project that was once Access 2003 project. The application connects to a SQL Server 2000 database. Everything worked great in Access 2003, but then I upgraded to Access 2007. Now, the Access application makes about 5,000 calls per minute to the database and slows performance down, with intermittent delays. SQL Profiler shows this call to the DB:

SELECT *, sql_variant_property(value, 'basetype') AS type FROM ::fn_listextendedproperty(N'MS_Description',N'user',N'dbo',N'table',N'[ TABLE NAME ',NULL,NULL)

The SQL Server DB has about 100 tables. For 31 of the tables, a call is made by Access with its respective table name in the SELECT statement above. Some articles suggest the Extended Properties in tables are the problem and need to be removed. Others say its the foreign keys used in the joins of the T-SQL and that they should  be removed. Importing to a new ADP is not possible, as I tried and many times. After importing, the forms are unrecognizable and cant be repaired.

Do you have other suggestions? What else can I try?
0
Comment
Question by:gtrapp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 9

Expert Comment

by:borki
ID: 22794857
Hi gtrapp

Not sure what is causing this, I am just at the start of converting an .adp from Access 2002 to 2007. So far we have not found such an issue, although only a few selected users have been running the 2007 version.

The extended property that you see in the profiler is what you use as a comment for the field in the Access table designer. Perhaps you could try to delete this? I think Access uses the field to display information in the status bar when you generate forms, so maybe it still uses them at run-time?

How many users are running your fornt end to have 5000 calls/minute?

Keep us posted

Felix
0
 

Author Comment

by:gtrapp
ID: 22800534
There is one Access front-end application making 5,000 calls. If I deploy this, there will be about 15 users. Also, there is another Access front-end application that behaves the same way. Currently, no one is using the applications now, but if the users did, I think the DB server would choke. I will take a look at the comments.
0
 

Author Comment

by:gtrapp
ID: 22800669
I checked comments in Design View and did not see any. I check about 10 tables, of the 31 that were named in the SELECT statement above.

Microsoft has been working on this for 4 weeks now. Like you said, very few people with this people, which means MS is not going to fix it for a very long time.

0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 9

Expert Comment

by:borki
ID: 22801196
So you are sayinging you lodged the issue with MS Support? 4 weeks sounds pathetic.

I want to check this out now myself, even though my tests have not shown any performance issues, but then I have not really looked for this issue and it might have gone unnoticed.

What filter did you set up in SQL Profiler?
0
 

Author Comment

by:gtrapp
ID: 22823108
Yes, its been 4 weeks now and slowly getting to be 5 weeks. MS says they have esculated the issue.

I did not use any filter with the SQL Profiler.
0
 

Author Comment

by:gtrapp
ID: 22889524
MS has found a bug with my Access database and in 3-4 months will supply a hot-fix. I was able to import the old code, reports, forms, and modules to a new Access 2007 ADP file, which avoided the 5,000 calls to the DB bug.
0
 
LVL 9

Accepted Solution

by:
borki earned 1500 total points
ID: 22889709
Good news! I have been using .ADPs for a number of situations and have never experienced your issue.

However, I use the steps of re-import once a month on most DBs regardless of any issues, as it keeps the filesize of the ADP down. After this step, my bigest ADP is about 8 Mb, making various changes over the month it ends up about 18 Mb (after 2x compile, compact & repair)! Clearly there are issues with bloat!

I will look out for the tech details of the hot-fix.
0
 
LVL 1

Expert Comment

by:Satu7nine
ID: 23779491
Borki - I'm seeing the same issue you raised here.

Did you ever get anywhere with Microsoft in terms of a hotfix?
0
 

Author Comment

by:gtrapp
ID: 23779511
No. I imported forms, code, reports and such all to a new Access 2007 file. That fixed the problem.
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question