Link to home
Start Free TrialLog in
Avatar of kwebster7327
kwebster7327

asked on

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

I see this was asked and answered long ago here, but the accepted answer didn't really present a practical fix.

Problem: Legacy Access 2000 ADP application referencing a SQL Server database which has been ported up to Access 2007. The application works, but throws hundreds of calls to SQL looking for extended properties on tables per minute! Calls are like:

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

A good many folks on Google suggest setting the table's MS_SubdatasheetName extended property to "NONE", but this hasn't worked for me.

Has anyone managed to solve this problem?
ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of kwebster7327
kwebster7327

ASKER

Christian,

Thank you.

This looks like it might work. I've got a few forms which won't come over, but I can fix them one by one.

Biggest problem remaining is getting a custom menubar in the old ADP transferred over. Any ideas on that?

Thanks again!
Hi,

unfortunately not, that will be transferred automatically, you'll find that in the ribbons under Add-Ins.

It is highly recommended to create a ribbon instead and move the commandbar functions to the ribbon - looks very much more professional and you get rid of these old menus. But there is no automatic process for that, you must do that on your own (there are tools like RibbonCreator or you can do that by creating a XML file and load that on start from the SQL Server from a table).

Cheers,

Christian
I've think I've gotten everything ported except those menu bars.

I'd like to switch to the ribbon, but the old menu bars are pretty extensive and have a lot of code driving them. This thing is too close to the end of it's life to do that kind of rewrite.

After spending a day on it, I have code to get them created, but can't get them to actually fire the events they are supposed to. I suspect it is because the index and ID are read-only properties of the command button, so they don't line up with the code.

After Googling extensively, I'm surprised nobody else has posted anything about this.
Hi,

command bars are a little bit tricky and it is possible, depending on your code, that you've created the command bars more than once so the wrong ones are tied to the events. It is a good idea to first clean up the command bars by removing all of them (if they are created by code) at the application start.

If the command bars are static they should be already in the Add-In menu with the same functionality than before (but my tool above doesn't import command bars so that's only the case in the original file or if you recreate them manually).

I use command bars very rarely so I'm afraid I cannot help you very much with this issue.

Cheers,

Christian
Christian,

Fixed it (so far) with the help of coworkers who weren't as wrapped up in the typical "can't see the forest for the trees" view I was having.

1) Add your code to the existing ADP.
2) Create new ADP with the your code.
3) Open new ADP and use "Import External Data" to pull in ONLY the forms that failed. At the same time, be sure to check the "Import Menus and Toolbars" checkbox under the advanced options.
4) Compile new ADP into ADE.

In my case, the output files are much smaller, indicating the amount of legacy garbage we were hauling around.

Thanks again for your help.
Hi,

maybe a good idea to do that again, it's possible that the manually imported forms now work for a new import using the code (but the command bars must be imported manually).

You can use this as often as you want, it's a good clean-up from time to time.

Glad if I could help you.

Cheers,

Christian