Monday, June 20, 2005
Rule 3: Data Access
Don't use Query tables. They tend to leave lots of copies of themselves around. It can be quite difficult editing the queries.
Instead, write some generic ADODB code that uses a record set. A query can then just be a worksheet, an SQL string, and a reference to the parameters stored on a sheet.
The query clears the worksheet, runs the query, returns the data to the worksheet, and names the results as a range with the same name as the worksheet.
The worksheets should probably be hidden with the debug on / debug off macro.
The key to the data should be the first column to help with vlookup working as a function.
The generic code should be split out as an XLA file and referenced in.
Instead, write some generic ADODB code that uses a record set. A query can then just be a worksheet, an SQL string, and a reference to the parameters stored on a sheet.
The query clears the worksheet, runs the query, returns the data to the worksheet, and names the results as a range with the same name as the worksheet.
The worksheets should probably be hidden with the debug on / debug off macro.
The key to the data should be the first column to help with vlookup working as a function.
The generic code should be split out as an XLA file and referenced in.
Subscribe to Posts [Atom]