Excel Access interface

[Rosson Cain]

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.

 

[Andy Wiggins]

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"

 

[Dick Kusleika]

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.

 

[onedaywhen]

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')}

--

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.

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 Cain]

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')}

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.

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.

.