Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL back end to Excel ap

From: "K(dot) Kelly Close" <kkellyclose(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: PostgreSQL back end to Excel ap
Date: 2006-12-08 04:06:41
Message-ID: 4578E4D1.3020905@hydrosphere.com (view raw or flat)
Thread:
Lists: 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(at)hydrosphere(dot)com

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

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2006-12-08 06:25:28
Subject: Re: PostgreSQL back end to Excel ap
Previous:From: Lenorovitz, JoelDate: 2006-12-07 19:44:55
Subject: Help with Update Rule on View - 2nd Attempt

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group