Re: PostgreSQL back end to Excel ap - Mailing list pgsql-novice

My first attempt to reply to the list did not get thru.  If it finally does, I apologize for the redundancy...

Glenn,

When I say I've used this with Access, what I mean is I have Excel VBA code that uses the ADO object model to connect to an Access database and manipulate and pull in data.  I am sure that the ADO object model can be used from within Access VBA code to get to Excel data as well, but it sounds like you are wanting to go the other direction anyway?

The ADO objects let you build SQL in VBA, against the tables in the database, and pull data back into Excel.  The data, once in Excel, is not "linked" like it is if you use ODBC.  You do all the data manipulation in code, and you have buttons or triggers on the sheets that initiate the code (like if a user changes the value in a particular field which contains a SQL statement parameter, that change triggers code that passes SQL with the new parameter to Access and pulls back new query result - to the user it's completely invisible, and very fast).  If you would like to see some code samples for doing this, I'd be happy to share.  I don't have access to mine right now since I'm at home and they are at work, but this is a decent simple example for connecting and pulling data that belongs to someone else:

http://www.exceltip.com/st/Import_data_from_Access_to_Excel_(ADO)_using_VBA_in_Microsoft_Excel/427.html

I believe the very same approach could be used with PostgreSQL from Excel VBA using this OLEDB provider in place of the Microsoft Jet:

http://pgfoundry.org/projects/oledb/

Do you agree?  I favor this approach over ODBC primarily because of stability - in my experience, the SQL capabilities of MSQuery are limited and ODBC links break, especially when you move a workbook and database to a different computer, or if you keep them on a network drive and then access them from different computers.  Unfortunately, the ADO approach also can cause problems when moving from one computer to the next, when  the ADO library version used on the developer machine is more recent than the version on the users machine.  VBA looks for the library at run-time, and when it does not find the version it wants or newer, it fails.  But it is a very easy fix, unlike the broken ODBC links, which usually mean reverting to an earlier version of the workbook - ick.  But anytime your client calls and says "it crashed" is bad.

I also am mildly concerned about Office 2007 and Vistas not supporting ADO based programming, in favor of ADO.net.  Since Office 2003 does not support direct access of ADO.net objects, and PostgreSQL probably does not yet have a provider for ADO.net, this may be a mute point, but if anyone has any insight on this, I'd appreciate hearing it.

~Kelly

Glenn Davy wrote:

Hi Kelly - sorry to hijack your thread, but I have to know...
when you say: 

ADO (have used this with Access)    

how did you do this? If you can tell me how you did this in access with
ADO (which ive never been able to do) Im pretty sure I can adopt the
approach to excel?

thanks
Glenn

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faq 

-- 
**************************************
Kelly Close
Database and GIS Specialist
Hydrosphere Resource Consultants, Inc
(303) 443-7839
(303) 442-0616 (fax)
kkc@hydrosphere.com

Visit our web site!
http://www.hydrosphere.com