Running SQL SELECT statements in Access runtime

[Brian]

[Access 2003]

I have a number of applications deployed to clients that have only Access
runtime throughout the organization. In all cases, I have remote access to
their systems, since I also provide IT support. All are split FE/BE
implementations.

Occasionally, a customer will need a quick, one-time query or report, or I
need to do a query remotely to identify a data anomaly. Since I cannot run a
query directly in Access runtime without embedding it in a form, I have (at
least) two choices (leaving out the option, for the moment, of deploying a
full copy of Access to the server or remote station):

1. Download a copy of the data file to my system and run the query
there--not a good option if the back end is large.
2. Write a form or report just for this, upload it as part of a temporary
front-end, then run the form or report.

I then had an idea: I could add an input box to my hidden development menu
where I could type (or paste) the SQL string manually, and then run it.
Herein lies my question: how can I display the results? I would use a
datasheet, but any type of form, including a datasheet, requires controls for
each field, and short of just making a generic datasheet form with lots of
controls and setting the ControlSource of each at runtime, I cannot think of
any simple way to display on-the-fly query results.

I am probably missing something basic here, but I have just never given this
much thought and figure that someone has ideas superior to those striking me
at the moment. I guess the ideal would be a "dynamic datasheet form", wherein
there would automatically be a control on the form for each field in my query.

If nothing else, I may have to just revert to exporting the results to an
Excel sheet, which I can then open on the remote system with the Excel Viewer.

Other ideas?

 

[David C. Holley]

Add an unbound subform viewed as Datasheet. Create code that takes the SQL
string from the text box, binds the subform to it and then requeries the
subform.

 

[Jonathan]

Just a thought, add a shortcut key in a macro named AutoKeys that opens your
form or hidden development menu. Then you do not need a temporary application.

Luck
Jonathan

Add an unbound subform viewed as Datasheet. Create code that takes the SQL
string from the text box, binds the subform to it and then requeries the
subform.

[Access 2003]

I have a number of applications deployed to clients that have only Access
runtime throughout the organization. In all cases, I have remote access to
their systems, since I also provide IT support. All are split FE/BE
implementations.

Occasionally, a customer will need a quick, one-time query or report, or I
need to do a query remotely to identify a data anomaly. Since I cannot run
a
query directly in Access runtime without embedding it in a form, I have
(at
least) two choices (leaving out the option, for the moment, of deploying a
full copy of Access to the server or remote station):

1. Download a copy of the data file to my system and run the query
there--not a good option if the back end is large.
2. Write a form or report just for this, upload it as part of a temporary
front-end, then run the form or report.

I then had an idea: I could add an input box to my hidden development menu
where I could type (or paste) the SQL string manually, and then run it.
Herein lies my question: how can I display the results? I would use a
datasheet, but any type of form, including a datasheet, requires controls
for
each field, and short of just making a generic datasheet form with lots of
controls and setting the ControlSource of each at runtime, I cannot think
of
any simple way to display on-the-fly query results.

I am probably missing something basic here, but I have just never given
this
much thought and figure that someone has ideas superior to those striking
me
at the moment. I guess the ideal would be a "dynamic datasheet form",
wherein
there would automatically be a control on the form for each field in my
query.

If nothing else, I may have to just revert to exporting the results to an
Excel sheet, which I can then open on the remote system with the Excel
Viewer.

Other ideas?

.