Excel query based on cell value

[Hanz77]

I’ve seen this question asked several time, but I have yet to find a solution that fits my needs.

I have an Access database containing several tables with over 300,000 rows each. This database is nothing more than a place to store raw data. Within Excel I have gone through the process of importing external data using the following SQL.

SELECT tbl_RawData.AcctNumber, tbl_RawData.[Phone Number], tbl_RawData.[Customer Number], tbl_RawData.Address, tbl_RawData.City, tbl_RawData.State, tbl_RawData.[Zip Code], FROM tbl_RawData WHERE (((tbl_RawData.AcctNumber)="123456"));

As written, this code pulls all data with an account number of 123456 into Sheet2. If I want to change the account number, I have to manually edit the query and then refresh the data. The manual process works, but there has to be a better way of doing this. Ideally, I would like to have the ability to update the account number in cell B2 within Sheet1 so that the data in Sheet2 is automatically updated. The automatic update should be easy enough with an event procedure and I should be able to figure that out. Any suggestions on how to get the SQL to reference cell B2 that is located in Sheet1?

For what it is worth I have tried to replace my WHERE statement with WHERE (((tbl_RawData.AcctNumber)=" & Range("B2").Value & "). This did not work, hence the reason for my post.

Any assistance is appreciated.

 

[Fazza]

Hi.

Parameterised query tables do exactly what you want without the need for code.

This example might help http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters

Or google for other examples of parameterised Excel query tables.

Maybe a simple way to set this up when you already have a query is to go from the worksheet containing the query into the VBE (Visual Basic Editor) ALT-F11 and CTRL-G to the immediate window. If you type in the following it will return the SQL you have (pre Excel 2007, I don't use Excel 2007)

? activesheet.querytables(1).sql

If that is OK you can edit this and assign it back as the sql property is read/write.

Or alternatively use, untested

activesheet.querytables(1).sql=replace$(activesheet.querytables(1).sql,"123456","?")

HTH, Fazza