Обсуждение: PostgreSQL back end to Excel ap
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
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
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
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:
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
> > 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.
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:
~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