Re: PostgreSQL back end to Excel ap

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(at)hydrosphere(dot)com

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