Using Excel to Query Access database

[Jayhawktc]

Is it possible to use an excel macro to pull data from access based o
criteria entered into excel by a user?

If possible what would be the best method. The database is calle
"Mults" and the filtering criteria will be entered into cell I33.

Any help would be appreciated, thanks in advance.

Tyle

 

[Dick Kusleika]

Tyler

What kind of data do you want to pull? Entire records that meet that
criteria?

It sounds like you want an External Data Table with a Range type Parameter.
See here for setting up an External Data Table

http://www.dicks-clicks.com/excel/ExternalData.htm#CreateUI

You may not need to use VBA to do this. Once the table is setup, you can
simply refresh it whenever that cell changes. If you do want to use VBA,
see here

http://www.dicks-clicks.com/excel/ExternalData3.htm#CreateVBA

And finally, to set up your Parameter (so that the query uses I33 as the
criteria) see here

http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters

 

[Smalltalk_vb]

You can use DAO, ADO, or Automation. Set a reference to
an Object library, connect to Access using one of the
methods I mentioned about. Using the cell I333, create a
sql string to create your criteria to select the records
from access. After the records ar placed in a recordset,
use the CopyFromRecordset method, of a Range object to
paste the records you want into Excel.

You could even use the SQLExecQuery Function
This should get you started.