SQL.REQUEST Function for XL - Very Useful!!!

[Bowers74]

I would like to inform you about an Excel function not many know about that helps you to return smaller amounts of data from a database:

The SQL.REQUEST() function gives you SQL Query functionality directly in Excel!

The use of this Function is a little in depth, so I am not going to try to explain it all in this thread.

If you want to know more about this function then go to the following website:

http://www.bygsoftware.com/examples/sqlrequest.html

You can download an example Workbook at this link:

http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.zip

This is a great example and definately worth looking into. ;-)

You will need to activate or install the ODBC Add-in (XLODBC.XLA) to be able to use this function.


For those of you who have Excel XP, you need to know that the Excel ODBC Add-in is no longer included with Office XP, but you can download it from MS at this link:

http://download.microsoft.com/download/excel2002/xlodbc/2002/w98nt42kmexp/en-us/xlodbc.exe

I have experienced that this function works great for returning smaller amounts of data from Access databases. I say smaller amounts, but you can also return relatively larger amounts as well. But be warned, as with every Query process, the larger the data, the longer it takes to return the data.

I use this function to create "VLOOKUP()'s" from Access database. It has also come in really handy when creating reference tables for pivot tables and charts as well!


Enjoy, and have fun!


I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[jsteph]

Mike,
Very good quick & dirty function. A note about the example, it's referring to a non-standard Install path for Office, but once I changed that it worked fine.
--jsteph

 

[Bowers74]

Has anyone else found this interesting?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MichaelRed]

interesting ... but in a STRANGE way. I would not think to use Excel from Ms. A. to do a "lookUp" function for use in Access. Perhaps this is not what you are doing -in detail, but it appears to the implication from posting the Excel method in Access queries & Jst SQL.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Bowers74]

I do realize what forum this is. I was not trying to imply anything other than the fact that I have seen NUMEROUS questions in this forum requesting information on how to pull data from an Access DB using Excel.

I figured that someone might find it interesting to know that there are other ways to do this than using an Excel Query or hard-coding DOA or ADO.

I do apologize profusely that I have offended any of the members of this forum by posting an EXCEL based Helpful Tip in the ACCESS QUERIES & JET SQL even though it does deal with QUERYING of data from ACCESS into Excel using SQL.

By the way, the FAQ for this that I posted is located at faq68-4089, but don't worry it is posted in the MICROSOFT OFFICE FORUM forum68 where it belongs!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[MichaelRed]

Not offended, just -as I noted- 'strange'- to me.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[wnolze]

Hi All,
I also find the SQL.REQUEST() function pretty useful. However, I discovered an issue within my worksheet where I would really appreciate any help on:
When I use SQL.REQUEST as an array function returning a high volume of data from my Oracle DB, not all the data are returned.
Example: SQL.REQUEST should return 1000 rows.
I highlight the appropriate area in my worksheet and execute SQL.REQUEST as array function.
Result: Only the first n-rows are filled with data.
All rows thereafter hold the value #n/a.
I wonder whether there is any parameter (XLODBC, Oracle ODBC Driver) that limits the amount of data returned by a query.
Thanks for your help.
/wolfgang