SQL Query Trees in VBA?

J

Joe W

Hello,

I want to convert some of the queries I built in the Access
Query Builder to VBA modules. I have quite a few nested
queries. How can I convert these to VBA modules?

Below is the SQL code for two of the queries. These are
part of a small book sales reporting database. The first
one is for "query 1," the "baseline" query that extracts
data only from tables.

The second SQL example is for a query called "2004 BUSINESS
SUMMARY" that draws data from Query 1.

Thank you for help.


------------------------------------------
SQL CODE FOR QUERY 1

SELECT [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked table].isbn,
[Order-line linked table].description, Sum([Order-line
linked table].amount) AS Sales, Sum([Order-line linked
table].[order-qty]) AS Quantity, [Customer Price
Categories].Field2 AS Category, "6/30/2004" AS
ReportPeriod, [Order-line linked table].["invoice-date"],
pro1_constit.[constit-id], [All Books_1].Title, IIf([All
Books_1]!Title=Null,[Order-line linked
table]![description],[All Books_1]!Title) AS ExpandedTitle,
[All Books_1].[Hard/Soft], [All Books_1].Price, [Order-line
linked table].[promo-code], [Order-line linked
table].[order-type]
FROM (([Order-line linked table] INNER JOIN [Customer Price
Categories] ON [Order-line linked table].[price-categ] =
[Customer Price Categories].Field1) LEFT JOIN pro1_constit
ON [Order-line linked table].[cust-number] =
pro1_constit.[constit-id]) LEFT JOIN [All Books] AS [All
Books_1] ON [Order-line linked table].[item-number] = [All
Books_1].[APA Item Number]
GROUP BY [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked table].isbn,
[Order-line linked table].description, [Customer Price
Categories].Field2, "6/30/2004", [Order-line linked
table].["invoice-date"], pro1_constit.[constit-id], [All
Books_1].Title, IIf([All Books_1]!Title=Null,[Order-line
linked table]![description],[All Books_1]!Title), [All
Books_1].[Hard/Soft], [All Books_1].Price, [Order-line
linked table].[promo-code], [Order-line linked
table].[order-type]
HAVING (((pro1_constit.[sort-name]) Not Like "*Do Not Use")
AND (([Order-line linked
table].["invoice-date"])>=#1/1/2004# And ([Order-line
linked table].["invoice-date"])<=#6/30/2004#) AND
((pro1_constit.[constit-id])<>"46525090" And
(pro1_constit.[constit-id])<>"46806705") AND (([Order-line
linked table].[promo-code])<>"COMP-SO") AND (([Order-line
linked table].[order-type])="C"))
ORDER BY [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked
table].description, [All Books_1].Title;

------------------------------------------------------
SQL CODE FOR QUERY "2004 BUSINESS SUMMARY"

SELECT Query1.[price-categ], Query1.[sort-name],
Query1.isbn, Query1.ExpandedTitle, Sum(Query1.Sales) AS
SumOfSales, Sum(Query1.Quantity) AS SumOfQuantity,
Query1.Category, Query1.ReportPeriod, Query1.[Hard/Soft]
FROM Query1
GROUP BY Query1.[price-categ], Query1.[sort-name],
Query1.isbn, Query1.ExpandedTitle, Query1.Category,
Query1.ReportPeriod, Query1.[Hard/Soft]
HAVING (((Query1.[sort-name]) Not Like "*Do Not Use"))
ORDER BY Query1.[price-categ], Query1.[sort-name],
Query1.ExpandedTitle;
 
N

Newbie

Why do you want to convert them to VBA? I believe, someone correct me if I
am wrong, that it is better to use compiled queries where possible.

You can run the queries from VBA .. .

docmd.openquery "queryname"

You could make the compiled queries into general queries and then set a
where clause in the docmd.openquery line in VBA so that the query is valid
for all years for eg.

HTH


Joe W said:
Hello,

I want to convert some of the queries I built in the Access
Query Builder to VBA modules. I have quite a few nested
queries. How can I convert these to VBA modules?

Below is the SQL code for two of the queries. These are
part of a small book sales reporting database. The first
one is for "query 1," the "baseline" query that extracts
data only from tables.

The second SQL example is for a query called "2004 BUSINESS
SUMMARY" that draws data from Query 1.

Thank you for help.


------------------------------------------
SQL CODE FOR QUERY 1

SELECT [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked table].isbn,
[Order-line linked table].description, Sum([Order-line
linked table].amount) AS Sales, Sum([Order-line linked
table].[order-qty]) AS Quantity, [Customer Price
Categories].Field2 AS Category, "6/30/2004" AS
ReportPeriod, [Order-line linked table].["invoice-date"],
pro1_constit.[constit-id], [All Books_1].Title, IIf([All
Books_1]!Title=Null,[Order-line linked
table]![description],[All Books_1]!Title) AS ExpandedTitle,
[All Books_1].[Hard/Soft], [All Books_1].Price, [Order-line
linked table].[promo-code], [Order-line linked
table].[order-type]
FROM (([Order-line linked table] INNER JOIN [Customer Price
Categories] ON [Order-line linked table].[price-categ] =
[Customer Price Categories].Field1) LEFT JOIN pro1_constit
ON [Order-line linked table].[cust-number] =
pro1_constit.[constit-id]) LEFT JOIN [All Books] AS [All
Books_1] ON [Order-line linked table].[item-number] = [All
Books_1].[APA Item Number]
GROUP BY [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked table].isbn,
[Order-line linked table].description, [Customer Price
Categories].Field2, "6/30/2004", [Order-line linked
table].["invoice-date"], pro1_constit.[constit-id], [All
Books_1].Title, IIf([All Books_1]!Title=Null,[Order-line
linked table]![description],[All Books_1]!Title), [All
Books_1].[Hard/Soft], [All Books_1].Price, [Order-line
linked table].[promo-code], [Order-line linked
table].[order-type]
HAVING (((pro1_constit.[sort-name]) Not Like "*Do Not Use")
AND (([Order-line linked
table].["invoice-date"])>=#1/1/2004# And ([Order-line
linked table].["invoice-date"])<=#6/30/2004#) AND
((pro1_constit.[constit-id])<>"46525090" And
(pro1_constit.[constit-id])<>"46806705") AND (([Order-line
linked table].[promo-code])<>"COMP-SO") AND (([Order-line
linked table].[order-type])="C"))
ORDER BY [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked
table].description, [All Books_1].Title;

------------------------------------------------------
SQL CODE FOR QUERY "2004 BUSINESS SUMMARY"

SELECT Query1.[price-categ], Query1.[sort-name],
Query1.isbn, Query1.ExpandedTitle, Sum(Query1.Sales) AS
SumOfSales, Sum(Query1.Quantity) AS SumOfQuantity,
Query1.Category, Query1.ReportPeriod, Query1.[Hard/Soft]
FROM Query1
GROUP BY Query1.[price-categ], Query1.[sort-name],
Query1.isbn, Query1.ExpandedTitle, Query1.Category,
Query1.ReportPeriod, Query1.[Hard/Soft]
HAVING (((Query1.[sort-name]) Not Like "*Do Not Use"))
ORDER BY Query1.[price-categ], Query1.[sort-name],
Query1.ExpandedTitle;
Click to expand...
 
G

Guest

I want to conver the queries to SQL in VBA because the
queries are proprietary, and I want to be able to conceal
them in a compiled MDE file.

This doesn't really answer my question, but thank you for
your input just the same.

Anyone else? Thanks in advance.
 
J

Joe W

That's an interesting suggestion, which I appreciate.

But it isn't as secure as compiling VBA code. There's no
way to crack that.
 
N

Nick Coe \(UK\)

Have a look at DoCmd.RunSQL in Help. Bear in mind that you
can only use it to run action queries.

The SQL you've included in your post doesn't look like an
action query...

If the queries you want to hide are sources for forms.
reports, lists, combo's then you can paste a certain amount
of SQL directly into the Source property.

Alternatively you could set a RecordSource in code with your
SQL as a long string or string variant.

--
Nick Coe (UK)
www.alphacos.co.uk

---

Joe W said:
Hello,

I want to convert some of the queries I built in the Access
Query Builder to VBA modules. I have quite a few nested
queries. How can I convert these to VBA modules?

Below is the SQL code for two of the queries. These are
part of a small book sales reporting database. The first
one is for "query 1," the "baseline" query that extracts
data only from tables.

The second SQL example is for a query called "2004 BUSINESS
SUMMARY" that draws data from Query 1.

Thank you for help.


------------------------------------------
SQL CODE FOR QUERY 1

SELECT [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked table].isbn,
[Order-line linked table].description, Sum([Order-line
linked table].amount) AS Sales, Sum([Order-line linked
table].[order-qty]) AS Quantity, [Customer Price
Categories].Field2 AS Category, "6/30/2004" AS
ReportPeriod, [Order-line linked table].["invoice-date"],
pro1_constit.[constit-id], [All Books_1].Title, IIf([All
Books_1]!Title=Null,[Order-line linked
table]![description],[All Books_1]!Title) AS ExpandedTitle,
[All Books_1].[Hard/Soft], [All Books_1].Price, [Order-line
linked table].[promo-code], [Order-line linked
table].[order-type]
FROM (([Order-line linked table] INNER JOIN [Customer Price
Categories] ON [Order-line linked table].[price-categ] =
[Customer Price Categories].Field1) LEFT JOIN pro1_constit
ON [Order-line linked table].[cust-number] =
pro1_constit.[constit-id]) LEFT JOIN [All Books] AS [All
Books_1] ON [Order-line linked table].[item-number] = [All
Books_1].[APA Item Number]
GROUP BY [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked table].isbn,
[Order-line linked table].description, [Customer Price
Categories].Field2, "6/30/2004", [Order-line linked
table].["invoice-date"], pro1_constit.[constit-id], [All
Books_1].Title, IIf([All Books_1]!Title=Null,[Order-line
linked table]![description],[All Books_1]!Title), [All
Books_1].[Hard/Soft], [All Books_1].Price, [Order-line
linked table].[promo-code], [Order-line linked
table].[order-type]
HAVING (((pro1_constit.[sort-name]) Not Like "*Do Not Use")
AND (([Order-line linked
table].["invoice-date"])>=#1/1/2004# And ([Order-line
linked table].["invoice-date"])<=#6/30/2004#) AND
((pro1_constit.[constit-id])<>"46525090" And
(pro1_constit.[constit-id])<>"46806705") AND (([Order-line
linked table].[promo-code])<>"COMP-SO") AND (([Order-line
linked table].[order-type])="C"))
ORDER BY [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked
table].description, [All Books_1].Title;

------------------------------------------------------
SQL CODE FOR QUERY "2004 BUSINESS SUMMARY"

SELECT Query1.[price-categ], Query1.[sort-name],
Query1.isbn, Query1.ExpandedTitle, Sum(Query1.Sales) AS
SumOfSales, Sum(Query1.Quantity) AS SumOfQuantity,
Query1.Category, Query1.ReportPeriod, Query1.[Hard/Soft]
FROM Query1
GROUP BY Query1.[price-categ], Query1.[sort-name],
Query1.isbn, Query1.ExpandedTitle, Query1.Category,
Query1.ReportPeriod, Query1.[Hard/Soft]
HAVING (((Query1.[sort-name]) Not Like "*Do Not Use"))
ORDER BY Query1.[price-categ], Query1.[sort-name],
Query1.ExpandedTitle;
Click to expand...
 
N

Newbie

ok . . . what about making the first query an append query and then making
the 2nd query look at the table, make the recordsource for the report equal
to the SQL at runtime?
 
G

Guest

Nick,

Unfortunately, you are correct; these are not action
queries.

I think there are too many levels in the trees for binding
to a form or report in a RecordSource parameter.

I think I need to use the CreateQueryDef method, but I
don't know how to use this for nested queries.

Thanks for your help.

-----Original Message-----
Have a look at DoCmd.RunSQL in Help. Bear in mind that you
can only use it to run action queries.

The SQL you've included in your post doesn't look like an
action query...

If the queries you want to hide are sources for forms.
reports, lists, combo's then you can paste a certain amount
of SQL directly into the Source property.

Alternatively you could set a RecordSource in code with your
SQL as a long string or string variant.

--
Nick Coe (UK)
www.alphacos.co.uk

---

Joe W said:
Hello,

I want to convert some of the queries I built in the Access
Query Builder to VBA modules. I have quite a few nested
queries. How can I convert these to VBA modules?

Below is the SQL code for two of the queries. These are
part of a small book sales reporting database. The first
one is for "query 1," the "baseline" query that extracts
data only from tables.

The second SQL example is for a query called "2004 BUSINESS
SUMMARY" that draws data from Query 1.

Thank you for help.


------------------------------------------
SQL CODE FOR QUERY 1

SELECT [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked table].isbn,
[Order-line linked table].description, Sum([Order-line
linked table].amount) AS Sales, Sum([Order-line linked
table].[order-qty]) AS Quantity, [Customer Price
Categories].Field2 AS Category, "6/30/2004" AS
ReportPeriod, [Order-line linked table].["invoice- date"],
pro1_constit.[constit-id], [All Books_1].Title, IIf([All
Books_1]!Title=Null,[Order-line linked
table]![description],[All Books_1]!Title) AS ExpandedTitle,
[All Books_1].[Hard/Soft], [All Books_1].Price, [Order-line
linked table].[promo-code], [Order-line linked
table].[order-type]
FROM (([Order-line linked table] INNER JOIN [Customer Price
Categories] ON [Order-line linked table].[price-categ] =
[Customer Price Categories].Field1) LEFT JOIN pro1_constit
ON [Order-line linked table].[cust-number] =
pro1_constit.[constit-id]) LEFT JOIN [All Books] AS [All
Books_1] ON [Order-line linked table].[item-number] = [All
Books_1].[APA Item Number]
GROUP BY [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked table].isbn,
[Order-line linked table].description, [Customer Price
Categories].Field2, "6/30/2004", [Order-line linked
table].["invoice-date"], pro1_constit.[constit-id], [All
Books_1].Title, IIf([All Books_1]!Title=Null,[Order-line
linked table]![description],[All Books_1]!Title), [All
Books_1].[Hard/Soft], [All Books_1].Price, [Order-line
linked table].[promo-code], [Order-line linked
table].[order-type]
HAVING (((pro1_constit.[sort-name]) Not Like "*Do Not Use")
AND (([Order-line linked
table].["invoice-date"])>=#1/1/2004# And ([Order-line
linked table].["invoice-date"])<=#6/30/2004#) AND
((pro1_constit.[constit-id])<>"46525090" And
(pro1_constit.[constit-id])<>"46806705") AND (([Order- line
linked table].[promo-code])<>"COMP-SO") AND (([Order- line
linked table].[order-type])="C"))
ORDER BY [Order-line linked table].[price-categ],
pro1_constit.[sort-name], [Order-line linked
table].description, [All Books_1].Title;

------------------------------------------------------
SQL CODE FOR QUERY "2004 BUSINESS SUMMARY"

SELECT Query1.[price-categ], Query1.[sort-name],
Query1.isbn, Query1.ExpandedTitle, Sum(Query1.Sales) AS
SumOfSales, Sum(Query1.Quantity) AS SumOfQuantity,
Query1.Category, Query1.ReportPeriod, Query1.[Hard/Soft]
FROM Query1
GROUP BY Query1.[price-categ], Query1.[sort-name],
Query1.isbn, Query1.ExpandedTitle, Query1.Category,
Query1.ReportPeriod, Query1.[Hard/Soft]
HAVING (((Query1.[sort-name]) Not Like "*Do Not Use"))
ORDER BY Query1.[price-categ], Query1.[sort-name],
Query1.ExpandedTitle;
Click to expand...


.
Click to expand...
 
G

Guest

The problem is that there are many layers of queries.
There may be seven or more nesting layers. (Not really
efficient, I know. But I'm new at this.) But the
creation of all those additional tables could impose some
severe performance penalties.
 
N

Newbie

ok . . . . what about using subquerys?
The problem is that there are many layers of queries.
There may be seven or more nesting layers. (Not really
efficient, I know. But I'm new at this.) But the
creation of all those additional tables could impose some
severe performance penalties.
Click to expand...
 
T

Tim Ferguson

I think I need to use the CreateQueryDef method, but I
don't know how to use this for nested queries.
Click to expand...

I don't really see what is so special about them being nested. Up to a
point, you can put the subordinate sql into a sub-select

SELECT Something FROM Somewhere
WHERE SomeNumber IN
( SELECT ANumber FROM SomewhereElse
WHERE Overdue IS TRUE
)

After some level the query becomes too complex for the engine, or too slow
to wait for -- then it is probably time to look at using a temporary make-
table or something else.

In any case, I am suspicious about the whole approach. A query with seven
or eight levels of subordination to me suggests either (a) SQL that could
be written better or (b) a database design that could be better. The real
SQL mavens are over on m.p.a.adpsqlserver so you could ask them about (a).

I'm also pretty phazed by this "proprietary query" stuff. Anyone with
access to the table structure can see (with the appropriate knowledge and
skill, obviously) how to put a query together. You said that you put them
together in the query design grid, so it is clearly not rocket science. You
might be better served, if you are worried that someone is going to get
rich by stealing your design work, by securing the database with Access
security and removing the read-design permissions from the users. This also
prevents them from creating queries.

I'd also humbly suggest that you could save yourself a lot of work by using
sensible (read: legal) names for your database tables and fields.

Best wishes


Tim F
 
M

Marshall Barton

Joe said:
I want to convert some of the queries I built in the Access
Query Builder to VBA modules. I have quite a few nested
queries. How can I convert these to VBA modules?

Below is the SQL code for two of the queries. These are
part of a small book sales reporting database. The first
one is for "query 1," the "baseline" query that extracts
data only from tables.

The second SQL example is for a query called "2004 BUSINESS
SUMMARY" that draws data from Query 1.
Click to expand...
[snip]

In order for a query to refer to another query, the first
query needs to be stored as a QueryDef, so I think you
should leave them where they are and find another way to
hide/protect them from prying eyes.
 
J

Joe W

Any other suggestions on how to hide/protect them?

What's wrong with storing a query as a QueryDef?

Thanks in advance.
 
J

Joe W

Thanks very much. And your constructive criticism is well
taken. I am new at this and obviously still have much to
learn.

Thanks again.
-----Original Message-----
Click to expand...

[snip]
 
M

Marshall Barton

You could use security to prevent users from getting to your
query defs, but a sophisticated user will be able to crack
that. Or, if the users are not particularly sophisticated,
just hide them in the db window.

You can try the subquery approach, but that's going to get
ridiculously complex very quickly and, in some cases, will
prevent a report from running.

There's nothing wrong with using QueryDefs for queries.
After all, that's what they're for, but a QueryDef's SQL
property is not at all hidden.

If bloat is not a significant issue, you could keep the
queries' sql strings in your code and only place them in the
appropriate QueryDef's SQL property at run time. but then
you'll need to be careful to erase it when the app is closed
and even then a hacker could crash your app and then use
another mdb to retrieve the SQL.

I guess the question, like all security issues, is - How far
do you want to go with it?
--
Marsh
MVP [MS Access]



Joe said:
Any other suggestions on how to hide/protect them?

What's wrong with storing a query as a QueryDef?
-----Original Message-----
[snip]

In order for a query to refer to another query, the first
query needs to be stored as a QueryDef, so I think you
should leave them where they are and find another way to
hide/protect them from prying eyes.
Click to expand...
Click to expand...
 
J

Joe W

Thanks, this is very informative.

Forgive my ignorance, but I'm new at this. Wouldn't a
QueryDef's SQL be hidden if I were to compile the MDB file
into an MDE file? Or isn't the QueryDef's SQL part of the
VBA code that is compiled?

Thanks again.
 
M

Marshall Barton

Joe said:
Forgive my ignorance, but I'm new at this. Wouldn't a
QueryDef's SQL be hidden if I were to compile the MDB file
into an MDE file? Or isn't the QueryDef's SQL part of the
VBA code that is compiled?
Click to expand...


No, the only thing an MDE does is remove your VBA source
from your application, which was your original reason for
moving the SQL into a module.

You can easily set the Record Source of a form or report
using code at runtime. Queries used this way are not the
issue. It's the queries that are referenced by other
queries that need to be saved as a QueryDef.

Maybe you can get an adequate level of security by leaving
those (sub)queries as QueryDefs and keeping the rest in your
code???
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Linked Tables Append Query(ies) 4
VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
Expression Builder basic question on syntax / Or / how to incorporatea SQL query into a textbox cont 16
Preferred way to do sort order - column in table, or specified in query? 3
How to program Subform as select list 2
Slow VBA-Generated SQL Involving Totals Query 9
Coding in form for a2003 0
Complex for me but may be a very simple query for you experts. 2

Top