Query In BE database

[Dwight]

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!

 

[Jeff Boyce]

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]

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

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

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!

 

[Jeff Boyce]

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

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

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

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!

 

[Dwight]

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.

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

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

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


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!

 

[John Spencer]

One thing you could do would be to store the SQL string in a memo field
and use that SQL string to execute a query.

Your table might look like
PKId - autonumber
QueryName - a short name for the query
SQLString - the SQL string for the query (a memo field)

When a user picks a specific query to execute, you would copy the
SQLString into a local queryDef and then use the query in your export
routine.

So if you have a defined query named as MyWorkQuery

UNTESTED AIR CODE Snippet follows

Dim qDef as QueryDef
Dim dbAny as DAO.Database

Set dbAny = CurrentDb()
Set Qdef = dbany.QueryDefs("MyWorkQuery")
Qdef.SQL = DLookup("SQLSTRING","TheQueryTable","PKID=" &
Forms!SomeForm!SomeCombox)

Set Qdef = nothing

Then use MyWorkQuery in you export routine.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

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.

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

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



:

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


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!

 

[Jeff Boyce]

Dwight

I, too, was faced with ... "concerned" ... users who wanted to export their
reports to Excel for further (financial) analysis ... right after we were
forcibly upgraded to Access 2007.

John S. offers one solution (see else-thread).

The approach I took was to use the same form/screen the users had been using
before to "order reports", and added an <Export to Excel> command button.
Behind the button, I used VBA to grab the SQL statement of the query for the
selected report, then exported that SQL/query to Excel. The one advantage I
could see is that any change to the underlying query/report does not require
a change to the SQL-code table in John's solution. The disadvantage is that
the approach I used is more complex.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

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.

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

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



:

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


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!

 

[Dwight]

Thank you for all the responses. I will post back with the solution that I
ended up with.

Dwight

Dwight

I, too, was faced with ... "concerned" ... users who wanted to export their
reports to Excel for further (financial) analysis ... right after we were
forcibly upgraded to Access 2007.

John S. offers one solution (see else-thread).

The approach I took was to use the same form/screen the users had been using
before to "order reports", and added an <Export to Excel> command button.
Behind the button, I used VBA to grab the SQL statement of the query for the
selected report, then exported that SQL/query to Excel. The one advantage I
could see is that any change to the underlying query/report does not require
a change to the SQL-code table in John's solution. The disadvantage is that
the approach I used is more complex.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

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.

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


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



:

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


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!