Hello

I would like to be able to create a form (or some sort of
interface) in Excel that would allow the user to select
certain perameters, then send these parameters to Access
and run a select query on a pre-existing table using the
selected parameters. I would then like the query results
sent back to Excel and displayed in a (preferably the
same) form that would allow the Excel user to interact
with the results. (For example, select a record store that
meets the criteria of being in CA and selling DVDs as
well.)

Any help is appreciated. Thanks.

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"



"Rosson Cain" wrote in message
...
Hello

I would like to be able to create a form (or some sort of
interface) in Excel that would allow the user to select
certain perameters, then send these parameters to Access
and run a select query on a pre-existing table using the
selected parameters. I would then like the query results
sent back to Excel and displayed in a (preferably the
same) form that would allow the Excel user to interact
with the results. (For example, select a record store that
meets the criteria of being in CA and selling DVDs as
well.)

Any help is appreciated. Thanks.

Rosson

You can create an External Data table in Excel that uses a parameter query.
Under the Data menu, choose Get External Data - New Database Query.

Choose Data Source - Select Access from the listbox and navigate to and
select the database in question. OK.

Choose Columns - select the table or query and the fields that you want to
include. Next.

Filter Data - do nothing here. Next.

Sort Order - select a field on which you want to sort, or leave blank to use
the order inherent in the table or query. Next.

Finish - select View Data in MSQuery and click Finish.

In MSQuery: Under the View menu, select Criteria. In the criteria pane,
select a field on which you want to filter. Let's say you select two
fields, State and Products. In the Value row, type

[Enter State]

under the State field and

[Enter Product]

under the Products field. When you use the brackets, it's interpreted as a
prompt string - this is what makes it a parameter query.

Under the File menu, choose Return Data to Microsoft Excel. It will now
prompt you to input values for each parameter that you entered. For the
first prompt, type CA. For the second, type DVD.

Back in Excel, it will ask you where to put the data. Click Ok or choose a
different cell and click OK.

Now you should have an External Data table in Excel that shows all records
where the state is CA and the Products is DVD. The External Data Toolbar
should also be showing. Every time you hit the Refresh button on this
toolbar, it will prompt you to enter values for your parameters.

Finally, there is a Parameters button on the toolbar. Click it and look at
the options you have for customizing you parameters. For instance, you can
have the parameter get its value from a cell instead of prompting you.

If you want to re-edit the query, it will not run you through that wizard
again, but will tell you that you have to edit it in MSQuery. The wizard
can't handle parameter queries.

If you have any questions, post back.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Rosson Cain" wrote in message
...
Hello

I would like to be able to create a form (or some sort of
interface) in Excel that would allow the user to select
certain perameters, then send these parameters to Access
and run a select query on a pre-existing table using the
selected parameters. I would then like the query results
sent back to Excel and displayed in a (preferably the
same) form that would allow the Excel user to interact
with the results. (For example, select a record store that
meets the criteria of being in CA and selling DVDs as
well.)

Any help is appreciated. Thanks.

You could create a 'stored procedure' in the database. Below is an
example (depending on permissions, you may actually get away with
executing this DDL from MS Query's SQL window!):

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

For MS Query, here's what should appear in the SQL window to run the
above procedure with parameters:

{Call MyStoredProc('01 JAN 2001', '01 JAN 2004')}

--

"Dick Kusleika" wrote in message ...
Rosson

You can create an External Data table in Excel that uses a parameter query.
Under the Data menu, choose Get External Data - New Database Query.

Choose Data Source - Select Access from the listbox and navigate to and
select the database in question. OK.

Choose Columns - select the table or query and the fields that you want to
include. Next.

Filter Data - do nothing here. Next.

Sort Order - select a field on which you want to sort, or leave blank to use
the order inherent in the table or query. Next.

Finish - select View Data in MSQuery and click Finish.

In MSQuery: Under the View menu, select Criteria. In the criteria pane,
select a field on which you want to filter. Let's say you select two
fields, State and Products. In the Value row, type

[Enter State]

under the State field and

[Enter Product]

under the Products field. When you use the brackets, it's interpreted as a
prompt string - this is what makes it a parameter query.

Under the File menu, choose Return Data to Microsoft Excel. It will now
prompt you to input values for each parameter that you entered. For the
first prompt, type CA. For the second, type DVD.

Back in Excel, it will ask you where to put the data. Click Ok or choose a
different cell and click OK.

Now you should have an External Data table in Excel that shows all records
where the state is CA and the Products is DVD. The External Data Toolbar
should also be showing. Every time you hit the Refresh button on this
toolbar, it will prompt you to enter values for your parameters.

Finally, there is a Parameters button on the toolbar. Click it and look at
the options you have for customizing you parameters. For instance, you can
have the parameter get its value from a cell instead of prompting you.

If you want to re-edit the query, it will not run you through that wizard
again, but will tell you that you have to edit it in MSQuery. The wizard
can't handle parameter queries.

If you have any questions, post back.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Rosson Cain" wrote in message
...
Hello

I would like to be able to create a form (or some sort of
interface) in Excel that would allow the user to select
certain perameters, then send these parameters to Access
and run a select query on a pre-existing table using the
selected parameters. I would then like the query results
sent back to Excel and displayed in a (preferably the
same) form that would allow the Excel user to interact
with the results. (For example, select a record store that
meets the criteria of being in CA and selling DVDs as
well.)

Any help is appreciated. Thanks.

Great - thanks guys! Is it also possible run a module
or/and macro that is created in Access directly from Excel?

I have a macro that runs a series of queries and modules
that returns an excel file as output. Is there any way
that I could run this procedure directly from Excel and
return the output to Excel without the user ever seeing
the Access portion.

The parameters for the Access model are going to be passed
in through entry boxes in Excel.

Thanks again!


-----Original Message-----
You could create a 'stored procedure' in the database.
Below is an
example (depending on permissions, you may actually get
away with
executing this DDL from MS Query's SQL window!):

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

For MS Query, here's what should appear in the SQL window
to run the
above procedure with parameters:

{Call MyStoredProc('01 JAN 2001', '01 JAN 2004')}

--

"Dick Kusleika"
wrote in message
...
Rosson

You can create an External Data table in Excel that
uses a parameter query.
Under the Data menu, choose Get External Data - New
Database Query.

Choose Data Source - Select Access from the listbox and
navigate to and
select the database in question. OK.

Choose Columns - select the table or query and the
fields that you want to
include. Next.

Filter Data - do nothing here. Next.

Sort Order - select a field on which you want to sort,
or leave blank to use
the order inherent in the table or query. Next.

Finish - select View Data in MSQuery and click Finish.

In MSQuery: Under the View menu, select Criteria. In
the criteria pane,
select a field on which you want to filter. Let's say
you select two
fields, State and Products. In the Value row, type

[Enter State]

under the State field and

[Enter Product]

under the Products field. When you use the brackets,
it's interpreted as a
prompt string - this is what makes it a parameter query.

Under the File menu, choose Return Data to Microsoft
Excel. It will now
prompt you to input values for each parameter that you
entered. For the
first prompt, type CA. For the second, type DVD.

Back in Excel, it will ask you where to put the data.
Click Ok or choose a
different cell and click OK.

Now you should have an External Data table in Excel
that shows all records
where the state is CA and the Products is DVD. The
External Data Toolbar
should also be showing. Every time you hit the Refresh
button on this
toolbar, it will prompt you to enter values for your
parameters.

Finally, there is a Parameters button on the toolbar.
Click it and look at
the options you have for customizing you parameters.
For instance, you can
have the parameter get its value from a cell instead of
prompting you.

If you want to re-edit the query, it will not run you
through that wizard
again, but will tell you that you have to edit it in
MSQuery. The wizard
can't handle parameter queries.

If you have any questions, post back.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Rosson Cain"
wrote in message
...
Hello

I would like to be able to create a form (or some
sort of
interface) in Excel that would allow the user to
select
certain perameters, then send these parameters to
Access
and run a select query on a pre-existing table using
the
selected parameters. I would then like the query
results
sent back to Excel and displayed in a (preferably the
same) form that would allow the Excel user to interact
with the results. (For example, select a record store
that
meets the criteria of being in CA and selling DVDs as
well.)

Any help is appreciated. Thanks.
.

Rosson

There may be a better way to do this, but to run code in Access from Excel,
you can use something like this:

Sub test()

Dim acApp As Access.Application

Set acApp = New Access.Application

acApp.OpenCurrentDatabase "C:\Dick\db1.mdb"

acApp.DoCmd.RunMacro "Macro1"

acApp.CurrentDb.Close

acApp.Quit

Set acApp = Nothing

End Sub

For this method, you need to create a macro in Access that calls a function
in a Module. I don't know how to call module code directly, but there's
likely a way. Also, a Macro can only run a function in a module, not a sub.
So you'd have to create function that calls whatever subs you want.
Finally, you would need to set a reference the Access object model in Excel.

I don't know how you're outputting an Excel sheet from Access, so I don't
know what to tell you about opening that workbook once it's created. If I
were doing it, I would do all the code in Excel. This just seems like a lot
of work to get data from Access to Excel.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Rosson Cain" wrote in message
...
Great - thanks guys! Is it also possible run a module
or/and macro that is created in Access directly from Excel?

I have a macro that runs a series of queries and modules
that returns an excel file as output. Is there any way
that I could run this procedure directly from Excel and
return the output to Excel without the user ever seeing
the Access portion.

The parameters for the Access model are going to be passed
in through entry boxes in Excel.

Thanks again!


-----Original Message-----
You could create a 'stored procedure' in the database.
Below is an
example (depending on permissions, you may actually get
away with
executing this DDL from MS Query's SQL window!):

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

For MS Query, here's what should appear in the SQL window
to run the
above procedure with parameters:

{Call MyStoredProc('01 JAN 2001', '01 JAN 2004')}

--

"Dick Kusleika"
wrote in message
...
Rosson

You can create an External Data table in Excel that
uses a parameter query.
Under the Data menu, choose Get External Data - New
Database Query.

Choose Data Source - Select Access from the listbox and
navigate to and
select the database in question. OK.

Choose Columns - select the table or query and the
fields that you want to
include. Next.

Filter Data - do nothing here. Next.

Sort Order - select a field on which you want to sort,
or leave blank to use
the order inherent in the table or query. Next.

Finish - select View Data in MSQuery and click Finish.

In MSQuery: Under the View menu, select Criteria. In
the criteria pane,
select a field on which you want to filter. Let's say
you select two
fields, State and Products. In the Value row, type

[Enter State]

under the State field and

[Enter Product]

under the Products field. When you use the brackets,
it's interpreted as a
prompt string - this is what makes it a parameter query.

Under the File menu, choose Return Data to Microsoft
Excel. It will now
prompt you to input values for each parameter that you
entered. For the
first prompt, type CA. For the second, type DVD.

Back in Excel, it will ask you where to put the data.
Click Ok or choose a
different cell and click OK.

Now you should have an External Data table in Excel
that shows all records
where the state is CA and the Products is DVD. The
External Data Toolbar
should also be showing. Every time you hit the Refresh
button on this
toolbar, it will prompt you to enter values for your
parameters.

Finally, there is a Parameters button on the toolbar.
Click it and look at
the options you have for customizing you parameters.
For instance, you can
have the parameter get its value from a cell instead of
prompting you.

If you want to re-edit the query, it will not run you
through that wizard
again, but will tell you that you have to edit it in
MSQuery. The wizard
can't handle parameter queries.

If you have any questions, post back.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Rosson Cain"
wrote in message
...
Hello

I would like to be able to create a form (or some
sort of
interface) in Excel that would allow the user to
select
certain perameters, then send these parameters to
Access
and run a select query on a pre-existing table using
the
selected parameters. I would then like the query
results
sent back to Excel and displayed in a (preferably the
same) form that would allow the Excel user to interact
with the results. (For example, select a record store
that
meets the criteria of being in CA and selling DVDs as
well.)

Any help is appreciated. Thanks.
.