Excel - Access interfacing

[Darragh]

Hi all.

Wondering if anyone could point me in the direction of resources that
could assist with using a spreadsheet grid in excel as the interface
for dropping values into an Access table. I'm not having much luck
searching the internet.

Basically, I'm after something like this pitiful graphic demonstrates
| 1 | 2 | 3 | 4 | 5 |
----------------------------------
Criteria | X | | | | |
----------------------------------

Basically, when a user puts an X or somehow marks a box in an excel
spreadsheet, this translates to something like this in an access table

Criteria Name | Score

 

[Guest]

You can interface by clicking on Access menu FILE - Get External Data - Link
table.

But why not just add a criteria parameter to your query. Open the query in
design view.
In the criteria row of the grid for your field enter this --
[Enter your criteria]

It will prompt you for the input when the query is run.

 

[Jamie Collins]

You can interface by clicking on Access menu FILE - Get External Data - Link
table.

But why not just add a criteria parameter to your query. Open the query in
design view.
In the criteria row of the grid for your field enter this --
[Enter your criteria]

It will prompt you for the input when the query is run.

You won't get the prompt in Excel

Jamie.

--

 

[Jamie Collins]

You can interface by clicking on Access menu FILE - Get External Data - Link
table.

But why not just add a criteria parameter to your query. Open the query in
design view.
In the criteria row of the grid for your field enter this --
[Enter your criteria]

It will prompt you for the input when the query is run.You won't get the prompt in Excel

[/QUOTE]

....but the OP could use VBA on the Excel side to get the parameter
value e.g.

Excel.Application.MATCH("x", Sheet1.Range("A2:E2"))

then choose your method of execution: an ADO command object (no dynamic
SQL required), ADO connection object requiring dynamic SQL e.g.

sSQL = "EXECUTE KarlsParamQuery " & Excel.Application.MATCH("x",
Sheet1.Range("A2:E2")) & ";"
conADO.Execute sSQL

Unfortunately, an Access/Jet parameter query cannot be invoked using
MSQuery; dynamic SQL could be used (for which the SQL syntax needs to
be ODBC: curly braces, etc) but you need to use VBA and why bother to
use MSQuery in VBA?!

Jamie.

--

 

[Jamie Collins]

the OP could use VBA on the Excel side to get the parameter
value

They could use Jet SQL to get the value from Excel e.g.

SELECT SWITCH(
F1 = 'x', 1,
F2 = 'x', 2,
F3 = 'x', 3,
F4 = 'x', 4,
F5 = 'x', 5,
TRUE, 0) AS excel_result
FROM [Excel
8.0;DATABASE=C:\Workbookname.xls;HDR=NO;].[Worksheetname$A2:E2];

Jamie.

--