Thread: PostgreSQL back end to Excel ap

PostgreSQL back end to Excel ap

From
"K. Kelly Close"
Date:
I am in the early design stages of an Excel application that needs a
database back end.  The Excel front is primarily due to client
preferences (oh, how they love Excel) and the many
spreadsheet-appropriate calculations, manipulations, and graphing that
will be done with the data within the application.  Using Access to hold
the data has been our solution in the past and since I am actually being
given the opportunity to suggest alternatives this round, I am hoping I
can use PostgreSQL instead and use this as one first step toward knowing
how to develop a non-ms bound solution to these types of "spreadsheet
models" we are often being asked to develop (discussion of alternate
front ends is a topic for a different time!).

So, given that, what I am asking here is simply for some advice from
users who have done this - link PostgreSQL data into Excel - on what
solutions tend to work best.  The ones I know about now involve ODBC
(looks very well supported on the PostgreSQL end), ADO (have used this
with Access) and ADO.Net (haven't used this, but wonder if it might be
worthwhile looking at for future compatibility with Office 2007...ugh).
My application will need to pull from and write back to the database
frequently, the database will be stored locally on the computer running
the application, it will need to be fairly easy for clients to install.

Thanks so much for your thoughts!

~Kelly

--

Kelly Close
Hydrosphere Resource Consultants, Inc.
1002 Walnut, Ste 200
Boulder, CO  80302

(303) 443-7839
http://www.hydrosphere.com


Re: PostgreSQL back end to Excel ap

From
Glenn Davy
Date:
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

Re: PostgreSQL back end to Excel ap

From
"Shoaib Mir"
Date:
For ADO using VBScript you can use it like this:

dim conn, rst, cmd

set conn = server.createobject("ADOdb.connection")

conn.open "Data Source=testing;location=192.168.0.2;UserID=username;password=password;"

Set cmd = Server.CreateObject("ADODB.Command")

cmd.ActiveConnection = conn

cmd.CommandText = "select empno from emp"

set rst = server.createobject("ADOdb.recordset")

Set rst= cmd.Execute

do until rst.eof

<<data processing code goes here>>

loop

rst.Close
conn.Close

Hope this helps...

Regards,
Shoaib

On 12/7/06, Glenn Davy <glenn@tangelosoftware.net> 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

Re: PostgreSQL back end to Excel ap

From
"K. Kelly Close"
Date:
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

Re: PostgreSQL back end to Excel ap

From
Richard Broersma Jr
Date:
> > 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/

Just be careful with oledb.  It is my understanding is that the oledb project doesn't have the
same support that the ODBC does.  I believe that it doesn't have all of the functionality that an
oledb driver should.

http://archives.postgresql.org/pgsql-general/2006-07/msg00109.php
http://archives.postgresql.org/pgsql-general/2006-07/msg00117.php

Regards,

Richard Broersma Jr.

Re: PostgreSQL back end to Excel ap

From
Kelly Close
Date:
Thanks for the tip.  It looks like there are different oledb drivers for Pg for ADO vs. ADO.NET compatability, and my guess is that is what was causing this problem, but I'm really new to this, so it's just a hunch.  Something to definitely be watchful for!

~Kelly

Richard Broersma Jr wrote:
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/     
Just be careful with oledb.  It is my understanding is that the oledb project doesn't have the
same support that the ODBC does.  I believe that it doesn't have all of the functionality that an
oledb driver should.

http://archives.postgresql.org/pgsql-general/2006-07/msg00109.php
http://archives.postgresql.org/pgsql-general/2006-07/msg00117.php

Regards,

Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate      subscribe-nomail command to majordomo@postgresql.org so that your      message can get through to the mailing list cleanly