how to create code from a saved query.

[wdsnews]

Wow, this is a lot to ask, but an example would help me tremendously.

In Paradox for Windows I could create a Query from the assistant and then
role it into my code. So far all of my queries in Access have been built
from the GUI. Now I would like to create a form where our users could enter
data in fields or click checkboxes, then I want to use the entries on the
form to generate a query. Finally, I'd like to launch a form or report that
uses the results of that query. (I realize this example could be served
with filters, but my goal is to learn code-based queries.)

As an example, suppose there were a 'Date' text field and a 'Withdrawn'
checkbox on a form named 'build-a-query' and I wanted to query my Student
table for students who are withdrawn before that date by clicking a
'find-em' button, what would the code look like in the action of that
button?

thank you for your help and your pointers.

 

[Douglas J. Steele]

Depending on exactly what it is you're trying to do, you may not have to do
any coding. You can specify parameters as criteria in your queries: to refer
to a control on a form, you use Forms![NameOfForm]![NameOfControl] as the
criteria. Once you've filled in the values, have a command button that
issues the command Me.Refresh

BTW, it's possible to switch to the SQL view from the graphical query
builder. In Access 2003 and earlier, you do this by selecting SQL View from
the View menu when the query's open in Design view. In Access 2007, the View
icon at the far left-hand side of the Design tab is a combo box that lets
you select SQL View.

 

[wdsnews]

Thanks Douglas. I have several goals. One goal is to become as proficient
with Access as I was with Paradox for Windows. With PfW there is a
Query/EndQuery structure that allows me to code field-by-field the query I
want. It makes it very easy to see multiple complex entries at a time. In
addition to being able to see multiple entries, it's also easier to see
because I can add comments and arrange formatting.

The SQL View doesn't allow me to do that. The GUI only shows me one field
at a time, and only after I choose 'Build'. Even then it lacks the
commenting and formatting that any good programmer should use.

The school district has specified a 'common' data format with 164 fields.
Depending on which report we need to generate, I have to hunt down the
content of each field, (sometimes very complex in a single field), verify
the coding and logic, or make changes. Of course as we all know, anything
that interferes with a chain of thought is a detriment.

The strategy I'm considering is building a new table with a record for each
field of the 164 field 'common' format. Then I can add comments to the
table, enter the SQL string for that field, specify if it applies to a
particular report, etc.

I'd like to extract the appropriate SQL strings from that table, add the
variables needed, and concatenate them all into a final SQL Query that can
be submitted to the backend.

Currently, I haven't got a clue how to build an Access Query in code, submit
it, and use the result for a CSV export or for a report.

Depending on exactly what it is you're trying to do, you may not have to
do any coding. You can specify parameters as criteria in your queries: to
refer to a control on a form, you use Forms![NameOfForm]![NameOfControl]
as the criteria. Once you've filled in the values, have a command button
that issues the command Me.Refresh

BTW, it's possible to switch to the SQL view from the graphical query
builder. In Access 2003 and earlier, you do this by selecting SQL View
from the View menu when the query's open in Design view. In Access 2007,
the View icon at the far left-hand side of the Design tab is a combo box
that lets you select SQL View.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Wow, this is a lot to ask, but an example would help me tremendously.

In Paradox for Windows I could create a Query from the assistant and then
role it into my code. So far all of my queries in Access have been built
from the GUI. Now I would like to create a form where our users could
enter data in fields or click checkboxes, then I want to use the entries
on the form to generate a query. Finally, I'd like to launch a form or
report that uses the results of that query. (I realize this example
could be served with filters, but my goal is to learn code-based
queries.)

As an example, suppose there were a 'Date' text field and a 'Withdrawn'
checkbox on a form named 'build-a-query' and I wanted to query my Student
table for students who are withdrawn before that date by clicking a
'find-em' button, what would the code look like in the action of that
button?

thank you for your help and your pointers.

 

[Douglas J. Steele]

If you've got 164 fields in a single table, it's almost guaranteed that the
table is improperly normalized. Having more than, say, 20 fields in a
properly normalized table is rare.

Yes, SQL in Access doesn't allow comments to be inserted. Since I've never
used PfW, I have no idea what you mean by seeing "multiple complex entries
at a time".

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas. I have several goals. One goal is to become as
proficient with Access as I was with Paradox for Windows. With PfW there
is a Query/EndQuery structure that allows me to code field-by-field the
query I want. It makes it very easy to see multiple complex entries at a
time. In addition to being able to see multiple entries, it's also easier
to see because I can add comments and arrange formatting.

The SQL View doesn't allow me to do that. The GUI only shows me one field
at a time, and only after I choose 'Build'. Even then it lacks the
commenting and formatting that any good programmer should use.

The school district has specified a 'common' data format with 164 fields.
Depending on which report we need to generate, I have to hunt down the
content of each field, (sometimes very complex in a single field), verify
the coding and logic, or make changes. Of course as we all know, anything
that interferes with a chain of thought is a detriment.

The strategy I'm considering is building a new table with a record for
each field of the 164 field 'common' format. Then I can add comments to
the table, enter the SQL string for that field, specify if it applies to a
particular report, etc.

I'd like to extract the appropriate SQL strings from that table, add the
variables needed, and concatenate them all into a final SQL Query that can
be submitted to the backend.

Currently, I haven't got a clue how to build an Access Query in code,
submit it, and use the result for a CSV export or for a report.

Depending on exactly what it is you're trying to do, you may not have to
do any coding. You can specify parameters as criteria in your queries: to
refer to a control on a form, you use Forms![NameOfForm]![NameOfControl]
as the criteria. Once you've filled in the values, have a command button
that issues the command Me.Refresh

BTW, it's possible to switch to the SQL view from the graphical query
builder. In Access 2003 and earlier, you do this by selecting SQL View
from the View menu when the query's open in Design view. In Access 2007,
the View icon at the far left-hand side of the Design tab is a combo box
that lets you select SQL View.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Wow, this is a lot to ask, but an example would help me tremendously.

In Paradox for Windows I could create a Query from the assistant and
then role it into my code. So far all of my queries in Access have been
built from the GUI. Now I would like to create a form where our users
could enter data in fields or click checkboxes, then I want to use the
entries on the form to generate a query. Finally, I'd like to launch a
form or report that uses the results of that query. (I realize this
example could be served with filters, but my goal is to learn code-based
queries.)

As an example, suppose there were a 'Date' text field and a 'Withdrawn'
checkbox on a form named 'build-a-query' and I wanted to query my
Student table for students who are withdrawn before that date by
clicking a 'find-em' button, what would the code look like in the action
of that button?

thank you for your help and your pointers.

 

[WDSnews]

Douglas, I like what I've seen in Access so far and I'll bet someone knows
how to code a query in Access. Just because you don't know how, doesn't
mean Acces is bad and unable to do what Paradox has done for multiple
decades. I'd be surprised to learn that Access still can't support SQL via
coding. If you don't know how to code a query, then say "I don't know", or
don't reply at all. Your chastisement is most unhelpful. How can you jump
to conclusions about a 164 field table when I'm asking about 164 field
query?

To answer your question, "multiple complex entries" means 164 fields of a
SQL Query that reference about 30 tables.

So, one big advantage of moving the query from GUI to code, is that it can
be formatted, commented, and understood with much less effort. Formatting
and Commenting are two fundamental skills that every coder should know about
and every code tool should support.

If you've got 164 fields in a single table, it's almost guaranteed that
the table is improperly normalized. Having more than, say, 20 fields in a
properly normalized table is rare.

Yes, SQL in Access doesn't allow comments to be inserted. Since I've never
used PfW, I have no idea what you mean by seeing "multiple complex entries
at a time".

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas. I have several goals. One goal is to become as
proficient with Access as I was with Paradox for Windows. With PfW there
is a Query/EndQuery structure that allows me to code field-by-field the
query I want. It makes it very easy to see multiple complex entries at a
time. In addition to being able to see multiple entries, it's also
easier to see because I can add comments and arrange formatting.

The SQL View doesn't allow me to do that. The GUI only shows me one
field at a time, and only after I choose 'Build'. Even then it lacks the
commenting and formatting that any good programmer should use.

The school district has specified a 'common' data format with 164 fields.
Depending on which report we need to generate, I have to hunt down the
content of each field, (sometimes very complex in a single field), verify
the coding and logic, or make changes. Of course as we all know,
anything that interferes with a chain of thought is a detriment.

The strategy I'm considering is building a new table with a record for
each field of the 164 field 'common' format. Then I can add comments to
the table, enter the SQL string for that field, specify if it applies to
a particular report, etc.

I'd like to extract the appropriate SQL strings from that table, add the
variables needed, and concatenate them all into a final SQL Query that
can be submitted to the backend.

Currently, I haven't got a clue how to build an Access Query in code,
submit it, and use the result for a CSV export or for a report.

Depending on exactly what it is you're trying to do, you may not have to
do any coding. You can specify parameters as criteria in your queries:
to refer to a control on a form, you use
Forms![NameOfForm]![NameOfControl] as the criteria. Once you've filled
in the values, have a command button that issues the command Me.Refresh

BTW, it's possible to switch to the SQL view from the graphical query
builder. In Access 2003 and earlier, you do this by selecting SQL View
from the View menu when the query's open in Design view. In Access 2007,
the View icon at the far left-hand side of the Design tab is a combo box
that lets you select SQL View.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Wow, this is a lot to ask, but an example would help me tremendously.

In Paradox for Windows I could create a Query from the assistant and
then role it into my code. So far all of my queries in Access have
been built from the GUI. Now I would like to create a form where our
users could enter data in fields or click checkboxes, then I want to
use the entries on the form to generate a query. Finally, I'd like to
launch a form or report that uses the results of that query. (I
realize this example could be served with filters, but my goal is to
learn code-based queries.)

As an example, suppose there were a 'Date' text field and a 'Withdrawn'
checkbox on a form named 'build-a-query' and I wanted to query my
Student table for students who are withdrawn before that date by
clicking a 'find-em' button, what would the code look like in the
action of that button?

thank you for your help and your pointers.

 

[John W. Vinson/MVP]

Douglas, I like what I've seen in Access so far and I'll bet someone knows
how to code a query in Access. Just because you don't know how, doesn't
mean Acces is bad and unable to do what Paradox has done for multiple
decades. I'd be surprised to learn that Access still can't support SQL via
coding. If you don't know how to code a query, then say "I don't know", or
don't reply at all. Your chastisement is most unhelpful. How can you jump
to conclusions about a 164 field table when I'm asking about 164 field
query?

To answer your question, "multiple complex entries" means 164 fields of a
SQL Query that reference about 30 tables.

So, one big advantage of moving the query from GUI to code, is that it can
be formatted, commented, and understood with much less effort. Formatting
and Commenting are two fundamental skills that every coder should know about
and every code tool should support.

Access is not a flawed implementation of Paradox.

Both programs are advanced and capable database programming
environments - but they are DIFFERENT programming environments!

Insulting one of the volunteers who's trying to help you is perhaps
not the best start of a conversation. You did not mention that this
was a multitable query in your initial post, and Douglas made what I
would consider to be a reasonable conclusion. Neither he nor I knows
anything about your background or level of expertise.

You can in fact construct SQL strings in VBA code and execute them, or
open recordsets based on them. The user interface will be quite
different from what you're used to in Paradox; that does not
automatically imply that it's incapable or inferior, however.

 

[wdsnews]

Wow. I suspected Access was a great product. I like what I've seen in
Access. I never suspected those of us with a dBase and Paradox background
would be treated as second class by the Access community.

Do you realize I'm trying to join your side? I agree with you. Your point
is my point. I suspected it's possible to build a query in code. So I was
very happy to read your message that says, "You can in fact construct SQL
strings in VBA code and execute them, or open recordsets based on them."

But so far, no one has pointed me to any code samples, whitepapers, or help
topics. ...worse than that... You said that because of my Paradox
background that Douglas made a reasonable conclusion that I wasn't designing
my database properly, constructing a 164 field table, when in fact my
question asked about "Queries". I asked about queries.

You also said, Douglas was "trying to help you". But he ignored my question
and chastised me for something I'm not guilty of. ...and here I am being
chastised again.

I know this isn't typical of the Access community. I've been in technology
for over 30 year. I know people in the Access Community are good and
helpful people. For those people, my question remains how to build a query
in code.

 

[mailton]

Wow. I suspected Access was a great product. I like what I've seen in
Access. I never suspected those of us with a dBase and Paradox background
would be treated as second class by the Access community.

Do you realize I'm trying to join your side? I agree with you. Your
point is my point. I suspected it's possible to build a query in code.
So I was very happy to read your message that says, "You can in fact
construct SQL strings in VBA code and execute them, or open recordsets
based on them."

But so far, no one has pointed me to any code samples, whitepapers, or
help topics. ...worse than that... You said that because of my Paradox
background that Douglas made a reasonable conclusion that I wasn't
designing my database properly, constructing a 164 field table, when in
fact my question asked about "Queries". I asked about queries.

You also said, Douglas was "trying to help you". But he ignored my
question and chastised me for something I'm not guilty of. ...and here I
am being chastised again.

I know this isn't typical of the Access community. I've been in
technology for over 30 year. I know people in the Access Community are
good and helpful people. For those people, my question remains how to
build a query in code.