Want to Display a Datasheet Based on Adhoc Query (SQL) PAH 06.12.2005 22:33:04 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 [ at ]employeeID = 10" TIA

Re: Want to Display a Datasheet Based on Adhoc Query (SQL) Philipp Stiefel <phil[ at ]codekabinett.de> 07.12.2005 16:40:15 <PAH[ at ]discussions.microsoft.com> wrote: [Quoted Text] > 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 [ at ]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

Re: Want to Display a Datasheet Based on Adhoc Query (SQL) PAH 07.12.2005 16:57:03 This would work, but it is not very clean....any other ideas? Thanks! PAH "Philipp Stiefel" wrote: [Quoted Text] > 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 >

Re: Want to Display a Datasheet Based on Adhoc Query (SQL) "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 07.12.2005 18:48:35 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" <PAH[ at ]discussions.microsoft.com> wrote in message news:8C4466D1-8E8F-4415-9405-FFB78CA145F1[ at ]microsoft.com... [Quoted Text] > This would work, but it is not very clean....any other ideas? > > Thanks! > PAH > > > "Philipp Stiefel" wrote: > >> 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 >>

Re: Want to Display a Datasheet Based on Adhoc Query (SQL) PAH 09.12.2005 14:11:04 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 [ at ]SPText iii. Runs it via the code: exec ([ at ]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

Re: Want to Display a Datasheet Based on Adhoc Query (SQL) "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> 10.12.2005 14:57:37 Nice approach, having a table storing all the ad-hoc queries. Glad to hear it worked out! Rob "PAH" <PAH[ at ]discussions.microsoft.com> wrote in message news:8FD8D3C1-E088-47D8-A1F3-D0202F860E30[ at ]microsoft.com... [Quoted Text] > 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 [ at ]SPText > iii. Runs it via the code: exec ([ at ]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

Re: Want to Display a Datasheet Based on Adhoc Query (SQL) "joel sitbon" <joel1962[ at ]msn.com> 22.12.2005 21:51:03 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 "Robert Morley" <rmorley[ at ]magma.ca.N0.Freak1n.sparn> wrote in message news:Oyvu8pZ$FHA.272[ at ]tk2msftngp13.phx.gbl... [Quoted Text] > Nice approach, having a table storing all the ad-hoc queries. Glad to > hear it worked out! > > > > Rob > > "PAH" <PAH[ at ]discussions.microsoft.com> wrote in message > news:8FD8D3C1-E088-47D8-A1F3-D0202F860E30[ at ]microsoft.com... >> 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 [ at ]SPText >> iii. Runs it via the code: exec ([ at ]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 > >

Re: Want to Display a Datasheet Based on Adhoc Query (SQL) "joel sitbon" <joel1962[ at ]msn.com> 22.12.2005 21:51:33 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 "PAH" <PAH[ at ]discussions.microsoft.com> wrote in message news:A93203D4-3AD0-49BB-88CF-B6DE1F562A32[ at ]microsoft.com... [Quoted Text] >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 [ at ]employeeID = 10" > > TIA