Hi. Does anyone know of an excel function that allows one to specify a full set of arguments (name of access file, table name, field values) and return a db record to excel? It seems that "dget" only works on ranges or pivot tables w/in excel. I haven't found a way to query access other than by using data, get external data, etc... Any help appreciated...

This should be possible by writing your own Excel function, with VBA code behind that builds the query and makes the ODBC calls according to the parameters supplied.

Beware, the query building could be a little tricky. You might need to know some VB text processing (concatenating variable and fixed strings for example) in order to create the SQL for the query. If you don't know SQL (the database standard query language), use the Access query builder and then choose to view the query SQL. Then go back to VB to come up with a means of constructing this in your spreadsheet function.

I don't have any examples to hand, but there must be someone out there with a little time on their hands who can help...

i've just set up a data query in excel to query an access database - when asked for the criteria (instead of = and 400 say, enter = and say [parameter value required], the [] are important); this should allow you to enter a value while the query is running

when you've saved the query, you can run it from a macro (though you'll need to turn on relative addressing whilst recording the macro)

this is the first time i've done this and avoids writing your own vba code and yet is quite quick and versatile to run

Good general point to note here - the macro recorder is a very good way to produce quick and dirty examples of VBA code. It can also be useful to establish how to do something in code that you can do from the GUI.

Go on, have a play...