Solved

displaying ad-hoc queries from ADP project

Posted on 2004-10-12
3
418 Views
Last Modified: 2013-12-05
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
0
Comment
Question by:yitz99
  • 2
3 Comments
 
LVL 5

Expert Comment

by:Krys_Wilson
ID: 12289004
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
 
LVL 1

Author Comment

by:yitz99
ID: 12295626
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
 
LVL 5

Accepted Solution

by:
Krys_Wilson earned 101 total points
ID: 12298339
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question