need help in MS query from Access to Excel

[nssolanki]

I have a database from which i import data to my excel sheet through MS Query (MS Access driver *.db) method where the required two (2) parameters are in cell A2 and B1 with output coming in cell B2.

Now i want to replicate this query to all remaining cells i.e. A2:A1000 and B2:BG2., where A2:A1000 are Dates and B2:BG2 are names. Pivot table is not an option as data is larger than Excel's limits.

So, my question is that,
Is there any way where i can push the required two parameters in my query from a cell A2:A1000 and B2:BG2 in a dynamic way through VBA code or something.

Database details::
DBQ=C:\MDB-XL trial\access.MDB;DefaultDir=C:\MDB-XL trial;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;

Access Query Details:::
SELECT Quotes.qCl
FROM `C:\MDB-XL trial\access.MDB`.Quotes Quotes
WHERE (Quotes.qTicker=?) AND (Quotes.qDate=?)


thanks in advance for your help ......

 

[nssolanki]

approx. 30 views and no replies..... please somebody help....
my query may be silly or naive but i am stuck at, how to pass dynamic parameter linked to a referencing cell so that i can drag and copy same query to different cells without creating individual query for each cells.

 

[JHB]

Could you show some sample data, and also show how the output/result should be in Excel?

 

[nssolanki]

Could you show some sample data, and also show how the output/result should be in Excel?

Thenks for reply,

Access database layout is like below,
Tables:
Quotes

Quotes table contains:
Fields:::ate,Ticker,Op,Hi,Lo,Cl,Vo,Oi

now in excel i want this data to flow like this::::
i will write a function say 'fetchdata', thus my parameters would be,

in any cell, "=fetchdata('date','ticker','Cl')" where 'date' and 'ticker' are parameters while 'Cl' is the return value.
now, as far as writing function is concerned, i can handle that part. But i am stuck at how to pass these two parameters from excel to access database.

the basic reasoning is this, for a given 'date' and 'ticker' in a cell find the data from 'Cl' field and return value in that particular cell.

thats it...

kindly reply if you need more clarification, thanks

 

[JHB]

Is it so you want it?
In the Excel function remember to change the path to where you store the database!

OpenDatabase("C:\Access programmer\Database3.MDB")

 

[nssolanki]

Yes i wanted it this way.
Thank you very much