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: (view raw, whole thread or download thread mbox)
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:
> I believe the very same approach could be used with PostgreSQL from 
> Excel VBA using this OLEDB provider in place of the Microsoft Jet:
> 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  Since Office 2003 does 
> not support direct access of objects, and PostgreSQL probably 
> does not yet have a provider for, 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?

Kelly Close
Database and GIS Specialist
Hydrosphere Resource Consultants, Inc
(303) 443-7839
(303) 442-0616 (fax)

Visit our web site!


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-2017 The PostgreSQL Global Development Group