Want to Display a Datasheet Based on Adhoc Query (SQL)

[PAH]

I am trying to build a user interface (form) that will allow a user to view a
datasheet based on a SQL code entered (Select, Union, Exec Stored Procedure
etc.) in an .adp.

In an .mdb this can be done by creating a passthrough query that is saved,
and then run using "DoCmd.OpenQuery queryname".

In an .adp, the commands "DoCmd.OpenStoredProcedure spname" and
"DoCmd.OpenView" exist, but do not allow arguments to be automatically passed.

I would like to run a statement like "Select * from employee E where E.LName
= 'Smith'" without having to save it as a specific view...

or --- "exec StoredProcWorkDone @employeeID = 10"

TIA

 

[Philipp Stiefel]

I am trying to build a user interface (form) that will allow a user to view a
datasheet based on a SQL code entered (Select, Union, Exec Stored Procedure
etc.) in an .adp.

In an .mdb this can be done by creating a passthrough query that is saved,
and then run using "DoCmd.OpenQuery queryname".

In an .adp, the commands "DoCmd.OpenStoredProcedure spname" and
"DoCmd.OpenView" exist, but do not allow arguments to be automatically passed.

I would like to run a statement like "Select * from employee E where E.LName
= 'Smith'" without having to save it as a specific view...

or --- "exec StoredProcWorkDone @employeeID = 10"

You could create an Form that is in datasheet-view with a
lot of textbox controls on it. At runtime you assign your
SQL-Statement to the Recodsource-Property of that form,
iterate through the Fields-Collection of the Recordset and
set the ControlSource of the textboxes to the fieldnames
and hide all controls that are not needed for the result of
the current query.

Cheers
Phil

 

[PAH]

This would work, but it is not very clean....any other ideas?

Thanks!
PAH

 

[Robert Morley]

Here's another way which is also not very clean, but might get you where you
want to go. It assumes that only one view will be open by any given user at
a time, so it's limiting in that sense, though you *might* be able to work
around that by adding a date field and always querying the most recent
date...but I'll keep it simple for the time being.

Create a table with two fields, UserID (with a default value of user_name()
or suser_sname() or whatever you prefer) and SQLText. Store your ad-hoc SQL
code for each user in the SQLText field. Then use OpenStoredProcedure to
execute a parameter-less SP which opens the table, looks up the SQLText for
the user who's calling the SP, then execute the code found in the associated
row.

Along the same lines, you can also create views in your database that get
saved under the user's name instead of dbo (i.e., mydb.rmorley.tmpview), but
that requires granting permissions to create views in general (which is
usually not desirable), and can be awkward to access from an ADP...the
table-based approach is probably easier.


Rob

 

[PAH]

Robert:
Thank you for your recommendation! It worked out well.

Here is the solution I finally settled on. My system is Win Server 2003,
SQL Server 2000, MS Access 2003, using Active Directory.

1. Use two SQL tables.
a. One to store all the ad hoc queries / stored procedure calls
that a user can call
b. One that stores the ad hoc query based on user name (user name
can be determined via a call to the SQL system call: system_user
2. The Access form.
a. Allows user to select from a list the query to run (populated
from the ad hoc query table.
b. Stores the selected query into the table based on user name.
c. Calls a stored procedure using: DoCmd.OpenStoredProcedure
"RunAdHoc", acViewNormal.
i. The stored procedures finds out the user name
ii. Retrieves the text of the desired query into @SPText
iii. Runs it via the code: exec (@SPText)
d. A datasheet is displayed.

This method is similar to using a pass-through query in an .mdb.
Thanks again for all the help! -- PAH

 

[Robert Morley]

Nice approach, having a table storing all the ad-hoc queries. Glad to hear
it worked out!



Rob

 

[joel sitbon]

Friend,

joel-ange sitbon has invited you to join GreenZap and get $50 WebCash to
spend online. Sign up for a FREE GreenZap account and get $50 to spend at
hundreds of the world's premier merchants, many of whom are offering
incredible upfront discounts. Click on the link below to go to GreenZap and
signup! All thanks to joel-ange sitbon.

It's Zappening in the GreenZap Storez.
http://www.greenzap.com/joel1962

If you do not want to receive these emails in the future click the link
below:
http://www.greenzap.com/optout_invite.asp

 

[joel sitbon]

Friend,

joel-ange sitbon has invited you to join GreenZap and get $50 WebCash to
spend online. Sign up for a FREE GreenZap account and get $50 to spend at
hundreds of the world's premier merchants, many of whom are offering
incredible upfront discounts. Click on the link below to go to GreenZap and
signup! All thanks to joel-ange sitbon.

It's Zappening in the GreenZap Storez.
http://www.greenzap.com/joel1962

If you do not want to receive these emails in the future click the link
below:
http://www.greenzap.com/optout_invite.asp