Why does Access 2007 make many calls to SQL Server looking for Extended Properties?
Posted on 2008-10-23
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?