Tesiny, Ed
EdTesiny at oasas.state.ny.us
Wed Mar 9 14:41:24 CST 2011
If you want to do renaming, Rick Fishers Find and Replace will make it a breeze for ~$30 http://www.rickworld.com/products.html Ed Tesiny EdTesiny at oasas.state.ny.us -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jm.hwsn Sent: Wednesday, March 09, 2011 3:29 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access Reserved words Thanks, that makes a lot of sense. I'll review where I've used them and make adjustments as needed. Thanks again, Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman Sent: Wednesday, March 09, 2011 2:23 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access Reserved words Jim, <<One performance hit was my use of domain functions. I like domain functions. However, I'm told that they could possibly slow my database down and that I should create an "equivalent saved query." From my research there are people on both sides of this issue. Some say it makes no difference and in some cases it's the best choice. Other say never use domain functions. I say if it works - don't fix it.>> Couple of comments: 1. Domain functions are often slower, but not always. There was a very old Smart Access article with timings that showed in some cases that domain function could be faster then other methods. If in doubt, test. 2. There are custom procedures out on the net to replace the domain functions, which give more flexibility and typically the same speed. 3. Domain functions should never be used inside of a SQL statement (query). Reason being is that they cannot be optimized by the query parser. Your better off with a sub select (and after all, all a Domain function really is is an encapsulated SQL statement). Even better yet, use another query as a base "table" in your query and do a join on it. 4. Use them like they were intended to be used; places where a VBA expression is allowed but a SQL statement or recordset is not. Or with Dlookup, which is used to fetch one value; don't use it to fetch five different fields from the same record. Instead, open a recordset, find the record, and have all the fields available. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jm.hwsn Sent: Wednesday, March 09, 2011 03:03 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access Reserved words Here's my take on the entire suite with an emphasis on the analyzer... There are 12 modules (separate software packages) in the suite. If purchased individually, the cost would be about $4,000. The modules are priced differently so splitting up the price will not give you a true depiction of their worth. Four modules utilize either an Active X control or an executable that needs to be installed on each user's machine. That said, and installation "package" needs to be developed and then run by an administrator to install them on the user's machines. Without the Active X control or executable on the user's machine they don't work and the controls that use them will be unrecognizable. Three modules can be used on ONE machine and by ONE person. The one person must have administrative rights to the network. Needless-to-say we will not be using these three modules. Five of the modules are specifically designed to be used on a single developer's machine. Three of them deal with writing code, with suggestions on how to write code, standardize code and how to adhere to "best practices" when writing code. One module compares database files and lists the differences between them, making suggestions on which option is best, and if needed merge them. The fifth and last module is probably the most powerful. The Analyzer module reviews the code, the structure of the database and makes recommendations on how to improve functionality (if possible) of the database and to help mitigate any possible errors. I ran the analysis on last Friday. I was given 244 errors, 3,042 suggestions and 830 performance suggestions. I know about most of them, but the complexity of the database and how we need to show, modify and manipulate the data is more important that squeezing out a few nanoseconds when running a query, showing a form or report. Most of the errors reported that a field did not exist... well I connect to an Excel spreadsheet and import several rows of data. When I connect to Excel it is linked to the database and then the link is destroyed. Of course, it does not exist, but there is no way the analyzer can know that. One suggestion is to "show hyperlink hand on hover" for buttons, etc. I do that with code in the "on mouse move" event. But what I didn't know is in A2007 one of the properties for buttons is "Cursor on Hover" which is set to default. The other option is "hyperlink hand." So do I want to change the 386 instances (or about 10% of the total number suggestions) to comply with the suggestion? One performance hit was my use of domain functions. I like domain functions. However, I'm told that they could possibly slow my database down and that I should create an "equivalent saved query." From my research there are people on both sides of this issue. Some say it makes no difference and in some cases it's the best choice. Other say never use domain functions. I say if it works - don't fix it. So far, I've deleted two tables that were not used; deleted 27 duplicate indexes, modified several queries in the hope of speeding things up; and analyzed several errors, suggestions and performance tips. I still have a lot of work to do to go through all their recommendations. Do I think it's worth the money. Yes. It costs $299 US as an individual package. I'm always looking for ways to slim down my code and to ensure error messages are kept to a minimum. If by going through this and 2 or 3 calls from users are NOT received... it's worth it. Two other packages that I like are the Speller and the CodeTools. The Speller is used by the programmer. It looks at the LABELS and CAPTIONS of objects to ensure everything is spelled correctly. It uses the dictionary from the Office package on the machine. For some reason, I misspelled Excel at least 10 times in the labels/captions. There were several other words that were misspelled that I didn't catch and during the usability testing were detected. I like it too. CodeTools is good and saved me several hours. I typically don't put in error trapping when a button only closes a form or quits the application. Using CodeTools, it verified and put in error trapping in the appropriate places if it was missing. So far that's my thoughts on the tools. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins Sent: Tuesday, March 08, 2011 3:02 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access Reserved words try "be interested to know more FROM a real user." __________________________________ From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins [Darryl.Collins at iag.com.au] Sent: Wednesday, 9 March 2011 7:51 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access Reserved words Jim, I would like to know more about your thoughts on this product. It looks rather useful, but i note it is also rather pricey. Now that maybe ok as it might still be great value for money given what it can do. Or it may not be... be interested to know more for a real user. cheers darryl. ________________________________________ From: accessd-bounces at databaseadvisors.com [accessd-bounces at databaseadvisors.com] On Behalf Of jm.hwsn [jm.hwsn at gmail.com] Sent: Wednesday, 9 March 2011 6:28 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] Access Reserved words My customer of the Access 2007 project I'm working on wanted some functionality that is not native to Access. For example, they wanted full Rich Text Formatting for several fields. These fields are used as feedback papers for the users. They purchased the FMS Total Access Ultimate Suite to be used with this project. One of the components is the Total Access Analyzer. I received the suite after I essentially finished the majority of the work. I ran the analyzer and it told me that I have 954 objects documented. It has 85 tables, 451 queries, 125 forms, 74 reports, 1 macro and 25 modules. It also informed me that there was a table that wasn't being used and a field in two queries that didn't belong to any table. It also told me it found 244 errors, 3042 suggestions on improving it and 830 performance issues. I haven't worked through most of them yet, but many are small things such as "transaction log" for a query is turned on and should be turned off. One item identified is: a column "conflicts with future SQL Server reserved word." I did some research and found out the reserved words it talks about have been on the list since at least 2000 and is still on list for SQL Server 2010. Another item was: a column "conflicts with Jet reserved word." In the description it says it's a "minor issue." I am aware of the problems of using reserved words. BUT. I used "language" and "comp" as field names. I should have known better, but I also used "order" and "default." I have had NO issues with these reserved words - yet. My question is: Should I spend the time to change the field names? If I don't change them, what kind of problems will it create? Thanks, Jim -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com