{"id":18761,"date":"2013-05-08T15:50:52","date_gmt":"2013-05-08T10:20:52","guid":{"rendered":"http:\/\/vskills.in\/certification\/tutorial\/?p=18761"},"modified":"2024-04-12T14:24:21","modified_gmt":"2024-04-12T08:54:21","slug":"use-database-functions","status":"publish","type":"page","link":"https:\/\/www.vskills.in\/certification\/tutorial\/use-database-functions\/","title":{"rendered":"Use Database functions"},"content":{"rendered":"

Use Database functions<\/strong><\/h4>\n

DBFuncs<\/code>\u00a0is an Excel-addin for database querying by userdefined functions. This is opposed to the Excel integrated Microsoft-Query, which is integrated statically into the worksheet and has some limitations in terms of querying possibilities and flexibility of constructing parameterized queries (Microsoft-Query allows parameterized queries only in simple queries that can be displayed graphically).<\/p>\n

Furthermore it includes the possibility for easy filling of so called “data bound controls” (ComboBoxes and Listboxes) with data from queries.<\/p>\n

DBFuncs<\/code>\u00a0has been tested extensively (actually it’s in production) only with Excel XP and Microsoft-SQL Server, other databases (MySQL, Oracle, PostgreSQL, DB2 and Sybase SQL Server) have just been tested with the associated Testworkbook\u00a0DBFuncsTest.xls<\/em>.<\/p>\n

To use that Testworkbook you’ll need the pubs database, where I have scripts available for Oracle, Sybase, DB2, PostgreSQL and MySql on my\u00a0website\u00a0(the Microsoft-SQL Server version can be downloaded\u00a0here).<\/p>\n

There are two principal ways to query data with\u00a0DBFuncs<\/code>:<\/p>\n

    \n
  1. A (fast) list-oriented way using\u00a0DBListFetch<\/code>.
    \nHere the values are entered into a rectangular list starting from the TargetRange cell (similar to Microsoft-Query, actually the\u00a0QueryTables\u00a0<\/code>Object is used to fill the data into the Worksheet).<\/li>\n
  2. A record-oriented way using\u00a0DBRowFetch<\/code>.
    \nHere the values are entered into several ranges given in the Parameter list\u00a0TargetArray<\/code>. Each of these ranges is filled in order of appearance with the results of the query.<\/li>\n<\/ol>\n

    Additionally, some helper functions are available:<\/p>\n