Re: Query In BE database

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Jeff,
My small number of users, located at two different sites use to analyse the
application's canned reports with MS Excel. With the upgrade to MS Access
2007 that functionality is no longer available.

I have created several queries that they can run and output to MS Excel that
I have on a form with command buttons. As additional queries are requested I
planned to provide a drop-down list box that would have all the additional
queries. All I would do is create the query, name it, and add the query name
to a table (BE), which would be the data source for the drop-down list box.
I was hoping to store the queries on BE so I would not have to release a new
FE everytime a query was added.

The code that I provide earlier should have been an "On Change" event.

"Jeff Boyce" wrote:

Dwight

Let me back up a step ... I'm having trouble envisioning a user interface
that would give the users "live" access to the underlying queries. A
"tighter" user interface would give users a way to get their jobs done, but
not expose the internal workings (e.g., queries).

Can you describe more what need you are filling/business problem you are
solving with the approach you've taken?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Dwight" <Dwight@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:438C4799-289D-48ED-9F3D-C70FD7E0A27B@xxxxxxxxxxxxxxxx
Jeff,
Thanks for the awesome information. I can see some great uses for this,
however, I don't think this will work for what I am exactly looking for.

My FE form has a drop-down list box with the source coming from a BE
table.
In the table is a list of all the queries that are available to be
selected.
When selected from the drop-down list box, I what the the query that is
being
stored on the BE to run.

Can I take the code below and add something to it that will tell it to run
the query from the BE?

*********
Private Sub MiscQuery_Click()
On Error GoTo Err_MiscQuery_Click
Dim strQueryName As String
strQueryName = Me.MiscQuery
Select Case chkExcel
Case True
DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, "Misc
Query_" & strQueryName & ".xls", True
Case False
DoCmd.OpenQuery strQueryName, acNormal, acReadOnly
End Select
Exit_MiscQuery_Click:
Exit Sub

Err_MiscQuery_Click:
Select Case Err.Number
Case 2302
MsgBox "A MS Excel file from this query is already open. Please
close the file and try again."
Resume Exit_MiscQuery_Click
Case Else
MsgBox Err.Description
Resume Exit_MiscQuery_Click
End Select

MsgBox Err.Description
Resume Exit_MiscQuery_Click

End Sub
*************

Thanks in advance!

Dwight



"Jeff Boyce" wrote:

Dwight

Garry Robinson wrote an article on this ... here's an excerpt:


What Is A Remote Query ?

It always amazes me how I can keep stumbling across features that I never
ever noticed or read about in the 8 years that I have been programming
Access databases. This article discusses one of those hidden gems that I
discovered when investigating Access Automation. Whilst looking into that
technology, I stumbled across a SQL help topic in Access that described
an
extension that allows you to point your current query to a table in
another
database. This in itself didn't seem all that interesting because I had
been
doing this for years using Linked tables. Then I noticed that you could
also
point your local query to a query that was in another Access database. No
links, no local query definitions, this was starting to get interesting.
Unfortunately the technology doesn't really come under any great heading
so
for the purposes of this article, I have come up with my own term for
these
queries and that is remote queries. Hopefully the discussions will help
you
to use these queries in the correct place once you understand what it is
you
have to do.

The "In Database" Clause Extension To SQL

As you will probably be very familiar with switching from query design
mode
to SQL queries, a remote query can be best described with the following
example of SQL

SELECT [Orders Qry].* FROM [Orders Qry]

IN 'C:\msoffice97\Office\Samples\Northwind.mdb';

This example opens the orders query from my local Northwind database. No
links, no tables.

INSERT INTO Orders ( CustomerID, EmployeeID, OrderDate )

SELECT Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate

FROM Orders IN 'C:\msoffice97\Office\Samples\Northwind.mdb';

This example would transfer the some of the fields in from a remote
location
of Orders table in the Northwind database into a local version of the
same
table.

How Do You Set Up A Remote Query Manually ?

Remote queries are really a no-brainer to setup manually once you have
seen
an example. First open a query and do not select any tables. Choose Menu
View and display Properties. Now replace (Current) in the Source Database
property with the full path to the remote database that you want to
display.
An example of this is show in figure 1 where I have setup to return all
the
rows from the Orders query in the Northwind database. Now that you have
added your database, you can choose the Query menu and choose Show Table.
You will find this same button on your query design toolbar. Now all the
tables and queries will be visible from the remote database as shown in
figure 2.

A word of warning. This manual design of remote queries is terrific for
initial development but you must be very careful if you are transferring
the
application to a new location as it is very likely that the path the
remote
database takes will change and this time you will not have any Add-In
like
the Linked Table manager to manage these changed paths.


Regards

Jeff Boyce
Microsoft Office/Access MVP


"Dwight" <Dwight@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7C1D00A6-42C1-4971-B1BE-519D64703E9D@xxxxxxxxxxxxxxxx
Is it possible to programatically run a query that is located in the
back-end
database. My front-end has a form that allows users to run canned
queries
by
clicking a button on the form.

The FE is loaded on each users PC and the BE is located on a shared
drive.
I am trying to avoid releasing a new FE everytime I have to modify or
add
a
query to the form.

Thanks in advance!






.

Quantcast