Solved

displaying ad-hoc queries from ADP project

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

815 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

8 Experts available now in Live!

Get 1:1 Help Now