Solved

displaying ad-hoc queries from ADP project

Posted on 2004-10-12
3
382 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now