Querying access from Excel

[bthomp]

Hi there

Currently to consolidate some data I import an excel file into access, query against another table and export back to excel.

Is it possible for me to remove one step, and instead of importing the data into access at the first step, just query the excel data against the access table and produce another worksheet with the result ?

im trying to avoid access if poss to keep the simplicity level there.

ideally id like to keep it all in excel, querying one sheet against another (unique numbers involved)

thanks for your help,


Ben

 

[SkipVought]

Ben,

How are the tables (Excel & Access) logically related?
What kind of join?
Any parameter values?

You can use Excel as a database. The simplest would be to query from access and then query excel using the access resultset and the excel table. That can all be done via MS Query.

Writing some ADO or DAO code you could open 2 databases and write some SQL to join the tables from both.



Skip,

http://www.TheOfficeExperts.com

 

[bthomp]

all the entries in all the tables are uniquely identified by company number, a 6 digit number.

the query in access is just a basic select, picking out multiple company data from a table if in another table of company daya they have a "y" in the Direct Debit col.

then that shortend list of company data is exported to excel where the VBA ive written does its work (that bits fine)

ive just thought tho that i could do it in using a long way round, ie;

only hold a list of those on DD and forget the rest, then for each row, pick out the comp number, run thru each row in the unsorted data list, matching comp numbers as it goes and tagging that entry with "DD" in a new col, then once its done it for all the comps on DD, delete all without "DD" in that extra col, and youd have the same list to run the VBA on.....

lol, mebbe thats it !!!

in that case, can i hold a list of data inside an addin ? ie on the workbook the module is in ? then use that list as the comparison data