What is better queries or code? What about aliases? Anything else? 1

[misscrf]

I am working to clean up a database app. When I made it, I made it normalized. Over time, with the need to get things done on the fly, I have extra queries, reports and stuff.

I am looking to clean it all up. I have found some great tools to help, including a database documenter, to give me a list of my objects, and a tool to find and replace object names.

Here is my question: As the control source to my forms, some of their controls (like combo boxes, etc) and reports, I have a mixture of queries and SQL control source/record source/row source not saved as a query. Some of this also lives in the VBA backend.

What is best? Is there a guidline for when to save as a query, when to use the query builder, but save the statement, without saving it as a query, and when to put the statement in VBA?

I figure I should get some sense of what is the best way for organization, structure, stability and performance.

I have also been encouraged to create aliases in my queries, which I understand I should also do in my code.

Why? I am happy to follow the credo, but I have a hard time doing something well if I don't understand the purpose.


Thanks for any thoughts!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[lameid]

1. SQL Statment VS Querydef object as a record source

The advantage to using an SQL Statment is that it is more portable. You can import your report or form into another database and not have to worry about remembering to get the query too.

The advantages to using a Querydef object (saved query) are that it is can be compiled so it will execute faster (save the query, run it, and save it again). SQL statements have to be compiled at each execution. Secondly, if you ever want to upsize your database to SQL server, the queries will be upsized and the upsizing report will tell you what queries didn't go. If you have SQL statements, you won't know it won't work until you try and open the form or report.

Now you can make an informed decision about your situation.

Throwing VBA into the mix... In VBA I prefer to use the straight SQL, when debugging everything is there in front of you and it is easy to make changes to a where clause without messing with the parameters collection and making sure they are setup correctly in the querydef in addition to portabilty. On the other hand, the SQL is not compliled like before... I'd say in VBA stick to the literal SQL unless you need to milk some extra performance. And then it probably would only be noticeable on the more complex statements.

2. Aliases... They seem very popular in my SQL book but I can not give you a good reason to ALWAYS use them. I can tell you Jet SQL (Access SQL) is more forgiving than say SQL server SQL. In subqueries, SQL server can be less forgiving if you do not use aliases for a table that is also in the main / parent from clause. In Jet SQL, I think you can get away with only using the alias in a subquery. On the other hand, there is the chance you may want to quickly add a subquery and Find and replace is not always clean (think table Invoice and table InvoiceDetail; replacing Invoice would affect both FQN's (Fully Qualified Name) in the Select clause). In short, reasons to use aliases are to maybe save work in the long run. If you need them, it won't hurt anything to add them later. On the other hand... an alias can make for a much shorter FQN... So I.InvoiceNum is fater to type than Invoice.InvoiceNum and arguably easier to read. FQN's are definitely the way to go because it keeps you from breaking statements if you add a table with the same field names. And therefore I guess you could say it prevents you from needing to prefix every field name with the table name because technically it you do it with FQN's. Prefixing is a practice I personally find maddening.

 

[misscrf]

Thank you for this well thought out response. It is definitely the kind of food for thought I was looking for. sorry I didn't respond sooner.

misscrf

It is never too late to become what you could have been ~ George Eliot