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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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



Rob

"PAH" <PAH@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8FD8D3C1-E088-47D8-A1F3-D0202F860E30@xxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • Re: Question on Pass-through Query
    ... I am in the process of creating a SQL Server as a back-end ... database and an Access database as a front-end database. ... Do I have to make every query in Access as pass-through query, ...
    (microsoft.public.access.queries)
  • Re: SQL And Access Project Security Question
    ... use the VIEW_METADATA option (see SQL Books Online ... >front-end application to an Access Project connecting to SQL server and have ... >If a user has read only permissions of a table at the ... and I want the user to be able to add/edit records via an Access Form ...
    (microsoft.public.sqlserver.security)
  • Question on Pass-through Query
    ... I am in the process of creating a SQL Server as a back-end database ... force to have the transactions processed in SQL Server, instead of in Access, ... Do I have to make every query in Access as pass-through query, ...
    (microsoft.public.access.queries)
  • RE: view or pass-through
    ... The main benefit you get by using sql view over pass thrugh is that you can ... use the view with the stored procedure you will write in the sql server, ... but you cant use the pass-through query in the stored ... > pass-through query when using SQL server as the backend? ...
    (microsoft.public.access.queries)
  • SQL 2008 ODBC connection issue
    ... We have an Access front end that connects to SQL Server 2008 backend. ... we use a table in an Access form, it only allows us to enter in 255 ...
    (microsoft.public.sqlserver.programming)