displaying ad-hoc queries from ADP project

Hi there Acces guru's,

Using ADP - which has views only and not queries, I need to display a query.  Not a form or a report - just a query.  I have code to create the SQL statement.  How do I run a temporary query based on the SQL statement?

I hope that was clear!

/yitz
LVL 1
yitz99Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Krys_WilsonConnect With a Mentor Commented:
I would probably use a Stored Procedure then.  You have to have complete SQL for a stored procedure to work but that shouldn't be a problem.

Something like:
*****************************
Create Procedure "StoredProcedure1"
/*
      (
            @parameter1 datatype = default value,
            @parameter2 datatype OUTPUT
      )
*/
As
      SELECT * FROM MyTable
      ORDER BY MyColumn

      return

*****************************

Hopefully that will keep its formatting after I submit this.  That will basically be what you see when you create a stored procedure.  Plus they are faster than traditional queries.

The code for the button would look something like this.

DoCmd.OpenStoredProcedure "MySPName", acNormal, acEdit

Let me know if that works for you.

Chris
0
 
Krys_WilsonCommented:
Yitz,

Its really as simple as creating a query in a regular access MDB.

Go to views, and create new.

Then when in the design view goto View -> Show Panes -> SQL

Then sicne you already have the SQL statement, just paste it in and save the view.  Then just run the view (Double click from the database window or View -> Datasheet View.

HTH

Chris
0
 
yitz99Author Commented:
Hi Chris,

Thanks, but no thanks.  

The idea is for the user to hit the button and get the query - no copying, pasting, or creating.  

The query is for the day.  I guess that I could do that in the SQL -- "where startdate='" & date() & "'"

But it also needs to be sorted, and views do not allow "order by" statements.  

If there is no way to output a straight query, then I guess that I could use a form and display it in data view.

Cheers
/yitz
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.