@templeowls, adding on ...
anytime you have data that doesn't need to be edited, like the RowSource for a combobox or data for a report, using Pass-Throughs is a good idea for better performance.
> new to SQL
do you mean SQL Server or SQL, the language that queries are stored in? If the latter (or both), it's good to learn the basics
basic SQL for Access
SELECT fieldlist
FROM tablename
IN anotherdatabase.mdb
WHERE criteria
GROUP BY fieldlist
HAVING criteria for fields that are aggregated
ORDER BY fieldlist;
~~~~
Alternately and more specifically, here is an SQL statement for a pass-through query where the back-end is SQL Server Azure, which uses similar but not the same syntax.
About
Coalesce:
it will be common to use the Coalesce function if data is in SQL Server, instead of using Nz in Access.
~~
SELECT Q.QuestionID
, Coalesce( Q.Question, Left( Q.QuestLong + '',100)) AS Q
, Q.SetID as Set_
, Q.QuestionID as ID
, COALESCE(
(
SELECT Sum( R.RScore) as QTotal
FROM dbo.Reaction AS R
INNER JOIN dbo.QReact AS QR
ON R.ReactID = QR.ReactID
WHERE
QR.QuestionID = Q.QuestionID
)
,0) as qScore
, Coalesce( Q.Question, Left( Coalesce(Q.QuestLong,''),200)) AS Question_
FROM dbo.QUESTION Q
WHERE ( 1=1 )
ORDER BY Left( Coalesce( Q.Question, Q.QuestLong, ''),100 )
~~
Note:
using WHERE ( 1=1 ) for criteria makes it easier for VBA code to change the WHERE clause.
This query also calculates a sum, qScore, which can be quite fast if you do it at the source!
~~~~
When querying a linked table, you use the Access flavor of SQL (Structured Query Language).
When using a Pass-Through query, Access doesn't care about any linked tables. The ODBC (Open DataBase Connectivity), which is a program(s) to translate from Access to native SQL to get the desired data, is bypassed so executing what is wanted is faster, and only what is needed is brought down.
> When/If I utilize queries in Access, should I be using passthrough queries or just normal ones?
That depends on the purpose of them. If you need to edit data, prehaps best to use queries based on linked tables, and limit the records with criteria for faster performance.
~~~ Pass-Thru
It's a good idea to keep saved queries for pass-throughs (perhaps named in a way that users don't see). Then, before opening an object, change the SQL for its data based on saved pass-through query (ies).
Pass-Through Queries define other properties than SQL such as the connect string. While you can set the other properties yourself, it can be convenient to include "_PT" in the query name to know that query is already set up to be a pass-through, and modify it's SQL for that user. Of course, each user should have thier own front-end.
To store values, like for criteria, database properties are handy. Here is some VBA code to handle object properties such as for the database.
VBA >
Properties > Get and set ... read, write, show, delete
http://msaccessgurus.com/VBA/Code/Properties.htm
~~~ performance
> best performance possible
It took a
long week to create an Access app to track questions and answers -- then more than a month to make it fast enough to run using an Azure back-end ... along with more changes, so not all of that additional time was performance.
Access is a great prototyping tool to figure out how to organize and present the information. It is also fast! So you don't have to worry about things like limiting the number of records you pull down.
However, once you've used Access to jump you to the next step, expect more time (and studying/learning ) to optimize everything ~
