Creating Formula in Excel to reference Access DB without hitting "refresh"

[snuffnchess]

I have some raw data in an Access DB of Zip Codes, and then reference numbers associated with those codes.

Right now, I have the zip code data in excel, but I am trying to pull it out of excel so that we can get the file size down, and the file can run smoother.

The formula we are using (to reference the info currently in excel) is as follows

LOOKUP(2,1/(($A17>=Origin_LB*1)*($A17<=Origin_UB*1)*($B17>=Destination_Zip_LB*1)*($B17<=Destination_Zip_UB*1)),ZoneGND)

to compare origin and destination zip codes to return a number listed in Column E.

I need this calculation to occur where excel goes to access finds the correct number and returns it to a cell in Excel, instantly when the zip codes are entered into A17 and B17. However I do not want to load the entire table into excel because of the amount of data it consumes.

Is this possible???

[alansidman]

Access Databases are capable of handling up to 2Gigs of data. Alternatively, you could link the Excel sheet to Access and then it would work as if it were an embedded table, but would not take up data storage.

[snuffnchess]

ideally what I want excel to do is go into the database, find just the one value that it needs, and return that value..... Not load the entire DB into excel, and then have it go and reference the data in excel. (note this could be done with an SQL server instead of Access if needed)

I do not know if what I want to do is possible though, especially without hitting the "refresh" button

[alansidman]

If you wish to query Access from Excel, then maybe this solution will work for you. Since you have not shared your files, I am only guessing at solutions, but from what you describe this may be a direction you wish to go.

http://datapigtechnologies.com/blog/...ry-from-excel/

[snuffnchess]

That is very close to what I would want to do.... just without pressing (in this case) a "Run Parameter Query" button.... and have it just load automatically.

[alansidman]

Since this is Mike Alexander's code, you may want to ask him. But a suggestion may be to put the code in a Worksheet Change event with your input cell as your target.