How to search Access table based on entry in cell in Excel?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi guys and gals,

Can someone please let me know if any code exists which would allow a user
to search an Access table for a value which is entered into an Excel
spreadsheet?

I'm pretty new to Access so still trying to find my way around.

Thanks in advance,
Will
 
Hi Will,

One simple way is to reference the cell in a subquery. First create a
new query that returns the fields you want from your table, save it and
switch it into SQL view. Here's an example from my test database:

SELECT AddrNew.FirstName, AddrNew.LastName
FROM AddrNEW
;

Then create another new query. Don't select any tables, but switch it
straight to SQL view. Then type in a SQL query that will return the
value of the worksheet cell you want. Here's an example, showing the
syntax you'll need. Stick with the default field name of "F1", but
change the folder, filename, worksheet name and cell reference to suit.

SELECT F1
FROM [Excel 8.0;HDR=No;database=C:\TEMP\AC.xls;].[Sheet1$A1:A1]
;

Once you've got this one working, copy it (except for the terminal ";")
and paste it into a new WHERE clause in the first query. Here's the
completed version from my test database. ID is the name of the Access
field that has to match the value of the worksheet cell. Note that the
subquery must be in paretheses.

SELECT AddrNew.FirstName, AddrNew.LastName
FROM AddrNEW
WHERE ID = (SELECT F1
FROM [Excel 8.0;HDR=No;database=C:\TEMP\AC.xls;].[Sheet1$A1:A1]);
 
Are you doing this from an MS Access project? Ot is this something you
want to do in Excel e.g. enter a value in a cell, look it up in a
remote database and return the result to Excel?

Jamie.

--
 
Back
Top