Significant slow down after a major VBA adjustment (1 Viewer)

[nIGHTmAYOR]

Access Version : MS Access 2000
Office Version : 2003

Setting :
GUI : mdb
DB : Sql Server 2000
Connection : Linked tables / ODBC
Security : mdw other than default system.mdw

Problem:
After applying a major adjustment to VBA project by useing a replace command (replaceing a custom security function in all forms , reports .. etc) uppon exiting VB Editor it required saveing every form and report on form , by selecting Yes and expecting this to take a while the process took fairly 5 seconds . To make sure i restarted project to find out the changes were saved , on experimenting however i found that the project slowed down significantly , finding nothing to do but applying compact and repaire option nothing have changed , also tried JetComp tool hopeing it would help but nothing. Now identifying symptoms , it seems that it take an average of 10 seconds for reports to warm up before even the query progress bar comes to show .
Of course i have the previous build which is still super fast compared to the modified one yet i need to apply such modifications since its a security fix required , Also to note the adustment isnt the cause as the adjustment concerns dropping down a procedure in security sequance not adding one.
Any thoughts , ideas ?

 

[Banana]

Do you have AutoCorrect option enabled? (Tools -> Option -> General tab)

 

[nIGHTmAYOR]

That was a fairly fast reply
Now i have googled your concern, and yes i have it enabled , and dispite it is mentioned in knowledge base that its evil to have it enabled yet i never had any of the listed problems before , as a matter of fact i come to find it usefull since i oftenly get to rename (sub queries) and (tables) names and save myself the hassel of haveing to change it everywhere, yet if i have to sucrafice it , what should i be doing ?

 

[Banana]

Create a new blank database, turn it off, then import all objects into it.

This may or may not fix your specific problem, but it only take few minutes so it couldn't hurt to try. If it didn't help, you are free to keep the old version as we try other solutions.

 

[nIGHTmAYOR]

i will end up losing users and security settings during such process

 

[MagicMan]

Use your Management Studio on SQl Server. My guess is everything is being pulled back to jet. You may have to use a pass-through or correct the new VBA code to ensure SQL Server can process the code on the server and pass back only the results. Another guess is that the new security function might produce better results as new views on the server versus a front end authorization which may force jet to retrieve all the data from all the tables for that form/query.

 

[Banana]

i will end up losing users and security settings during such process

Just to be clear, if you open the old database using the .MDW file you created with the Owner account, then closed it (but not Access itself), and created a blank database, the permissions should be inherited and should continue to work as before.

MagicMan's suggestion is excellent as well.

 

[nIGHTmAYOR]

My Setting is that i have Linked tables to an ODBC connection to the SQL Server and i havent changed it , as for the security adjustment it does nothing major , just insert 1 instead of 2 records per utilization and its not of a burden .

 

[nIGHTmAYOR]

Please elaborate on refraining from useing jet engine despite that i havent changed anything in Linked Table settings.

 

[Banana]

My best suggestion would be to go to support.mircosoft.com and search for the whitepaper on Jet/ODBC connectivity. The whitepaper will explain to you how Jet manage ODBC tables, and how certain query can force local evaluation, meaning that the *whole* table has to be pulled across the wire. Not a good thing for most cases.