Thread: ODBC problem with Microsoft Word and Excel

ODBC problem with Microsoft Word and Excel

From
"Karl Denninger"
Date:

I’ve loaded the most recent ODBC driver off the Postgresql FTP site.

 

The DBMS is functioning, has other applications (libpq, etc) operating perfectly, and the “Test” functionality from the “Data Sources” setup shows that I can connect to the database desired when I declare the connection.

 

However, when I attempt to go into either WORD (to use it as a mail merge source) or EXCEL (just for grins to pull data from there) I connect to “ODBC DSN”, click “PostgreSQL30W” (the driver that was set up in the Data Sources panel), I immediately get “Unable to get list of tables from the data source”.

 

What’s going on?!

 

-- Karl Denninger  karl@denninger.net

Diving?  See http://www.scubaforum.org

 


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date: 3/1/2007 2:43 PM

Re: ODBC problem with Microsoft Word and Excel

From
George Weaver
Date:
Karl Denninger found that:
 
> when I attempt to go into either WORD (to use it as a mail merge source) or EXCEL (just for grins to pull data from there) I >connect to “ODBC DSN”, click “PostgreSQL30W” (the driver that was set up in the Data Sources panel), I immediately get >“Unable to get list of tables from the data source”.

This might help: http://archives.postgresql.org/pgsql-odbc/2004-12/msg00054.php
 
Regards,
George
 

Re: ODBC problem with Microsoft Word and Excel

From
Richard Broersma Jr
Date:
> This might help: http://archives.postgresql.org/pgsql-odbc/2004-12/msg00054.php


The attached link refers to .udl(s).  UDL(s) only work with OLE-DB drivers not ODBC drivers,
correct?  In this case the posters is trying to get the ODBC driver to work correctly.

Regards,
Richard Broersma Jr.

Re: ODBC problem with Microsoft Word and Excel

From
"Karl Denninger"
Date:
Correct.

The part I don't understand is that I enabled logging and it appears to be
returning the table list!  Immediately after I attempt the connect from
either EXCEL or WORD I pulled the logfiles.

The ODBC logfile contains the following:

conn=02B03A90, PGAPI_DriverConnect( in)='DSN=PostgreSQL30W;',
fDriverCompletion=3
DSN info:
DSN='PostgreSQL30W',server='genesis.denninger.net',port='5432',dbase='
test',user='karl',passwd='xxxxx'
onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable=
'0'
          conn_settings='',conn_encoding='OTHER'
          translation_dll='',translation_option=''
Global Options: Version='08.02.0200', fetch=100, socket=4096,
unknown_sizes=0, m
ax_varchar_size=254, max_longvarchar_size=8190
                disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=
1 NAMEDATALEN=64
                extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='
OTHER'
    [ PostgreSQL version string = '8.2.3' ]
    [ PostgreSQL version number = '8.2' ]
conn=02B03A90, query='select oid, typbasetype from pg_type where typname =
'lo''
    [ fetched 0 rows ]
    [ Large Object oid = -999 ]
    [ Client encoding = 'UTF8' (code = 6) ]
conn=02B03A90,
PGAPI_DriverConnect(out)='DSN=PostgreSQL30W;DATABASE=test;SERVER
=genesis.denninger.net;PORT=5432;UID=karl;PWD=xxxxxxxxxxx;SSLmode=disable;Re
adOn
ly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSyste
mTab
les=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=254;
MaxL
ongVarcharSize=8190;Debug=1;CommLog=1;Optimizer=1;Ksqo=1;UseDeclareFetch=0;T
extA
sLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStm
t=0;
ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=1;DisallowPrematu
re=0
;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCase
Iden
tifier=0;XaOpt=1'
conn=02B03A90, query='select relname, nspname, relkind from
pg_catalog.pg_class
c, pg_catalog.pg_namespace n where relkind in ('r', 'v') and nspname not in
('pg
_catalog', 'information_schema', 'pg_toast', 'pg_temp_1') and n.oid =
relnamespa
ce order by nspname, relname'
    [ fetched 14 rows ]
conn=02B03A90, PGAPI_Disconnect


There are indeed 14 rows (tables), and the debug output says there were 14
rows fetched!

The query is also ok; I ran it by hand on the server, and got the list of
tables cleanly with no diagnostics.

What's going on?

Same error out of either Excel or Word (mailmerge setup)

-- Karl Denninger  karl@denninger.net
Diving?  See http://www.scubaforum.org

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Richard Broersma Jr
Sent: Friday, March 02, 2007 2:40 PM
To: George Weaver; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] ODBC problem with Microsoft Word and Excel

> This might help:
http://archives.postgresql.org/pgsql-odbc/2004-12/msg00054.php


The attached link refers to .udl(s).  UDL(s) only work with OLE-DB drivers
not ODBC drivers,
correct?  In this case the posters is trying to get the ODBC driver to work
correctly.

Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org


%SPAMBLOCK-SYS: Matched [hub.org+], message ok


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date: 3/1/2007
2:43 PM

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date: 3/1/2007
2:43 PM




%SPAMBLOCK-SYS: Matched [@yahoo.com+], message ok

Re: ODBC problem with Microsoft Word and Excel

From
George Weaver
Date:
But Richard Broersma Jr noted:

> The attached link refers to .udl(s).  UDL(s) only work with OLE-DB drivers
> not ODBC drivers,
> correct?  In this case the posters is trying to get the ODBC driver to
> work correctly.

I don't disagree that it would be desirable to be able to get Office
products to "play nice" with the PostgreSQL ODBC driver (or vice versa), if
that's the OP's objective.  On the other hand, if his objective is to pull
data into Word or Excel for the purposes he mentioned (the ends rather than
the means), the .udl approach works.

Regards,
George



Re: ODBC problem with Microsoft Word and Excel

From
David Gardner
Date:
I remember having problems with this. What I did was to move my query to
a pqsql function, and then pass Word the ODBC DSN with a SQL query of
"SELECT * FROM mypgfunction(parameter)". I also ran into a problem where
Word would see the column names correctly, and the row count would be
correct, but all the data fields were blank, I switched to using the
ANSI driver and that resolved it. Take a look at the following VBA code
snipet:

   Dim oWord As Word.Application
   Dim oDoc As Word.Document
   Dim oMM As Word.mailmerge

   'Start Word
   Set oWord = CreateObject("Word.Application")
   oWord.Visible = True

   ' create new document
       Set oMM = oWord.Documents.Add.mailmerge
   End If

   oMM.MainDocumentType = wdFormLetters
   oMM.OpenDataSource Name:="", Connection:="DSN=DSNName",
SQLStatement:="SELECT * FROM mypgfunction(stuff)"

   oMM.ShowWizard 1
   oMM.EditMainDocument

Karl Denninger wrote:
>
> I’ve loaded the most recent ODBC driver off the Postgresql FTP site.
>
>
>
> The DBMS is functioning, has other applications (libpq, etc) operating
> perfectly, and the “Test” functionality from the “Data Sources” setup
> shows that I can connect to the database desired when I declare the
> connection.
>
>
>
> However, when I attempt to go into either WORD (to use it as a mail
> merge source) or EXCEL (just for grins to pull data from there) I
> connect to “ODBC DSN”, click “PostgreSQL30W” (the driver that was set
> up in the Data Sources panel), I immediately get “Unable to get list
> of tables from the data source”.
>
>
>
> What’s going on?!
>
>
>
> -- Karl Denninger  karl@denninger.net <mailto:karl@denninger.net>
>
> Diving?  See http://www.scubaforum.org
>
>
>
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.5.446 / Virus Database: 268.18.5/707 - Release Date:
> 3/1/2007 2:43 PM
>



Re: ODBC problem with Microsoft Word and Excel

From
George Weaver
Date:
Original Message From "Karl Denninger"
>The part I don't understand is that I enabled logging and it appears to be
>returning the table list!  Immediately after I attempt the connect from
>either EXCEL or WORD I pulled the logfiles.
 
I don't think the problem resides on the PostgreSQL side of things but the Office side.  From my experience and from what I can gather Office needs a separate file (.udl .odc, etc) in order to display the results properly.
 
The discussion here might provide some further insight (first topic - Subject: Re: Mail Merge to SQL Server without using an external ODC):
 
 
HTH
George
 
"When you see a new trail, or a footprint you do not know,
        follow it to the point of knowing."
   - Uncheedah, Grandmother of Ohiyesa
 

Re: ODBC problem with Microsoft Word and Excel

From
"Karl Denninger"
Date:

Interesting.

 

If I create a blank “.UDL” file I can then stick in that and it appears to work, but its SEVERELY limited.

 

I can’t get at anything from Word EXCEPT for the single database that I defined in the SQL setup in “Data Sources” – and I can only have one on the machine!  This is a potential problem of course…… there’s a lot more than one database on the SQL server…..

 

But – it DOES appear to work for mail merge this way – its just kludgy as heck.

 

-- Karl Denninger  karl@denninger.net

Diving?  See http://www.scubaforum.org

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [
mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of George Weaver
Sent: Monday, March 05, 2007 9:20
AM
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] ODBC problem with Microsoft Word and Excel

 

Original Message From "Karl Denninger"

>The part I don't understand is that I enabled logging and it appears to be
>returning the table list!  Immediately after I attempt the connect from
>either EXCEL or WORD I pulled the logfiles.

 

I don't think the problem resides on the PostgreSQL side of things but the Office side.  From my experience and from what I can gather Office needs a separate file (.udl .odc, etc) in order to display the results properly.

 

The discussion here might provide some further insight (first topic - Subject: Re: Mail Merge to SQL Server without using an external ODC):

 

 

HTH

George

 

"When you see a new trail, or a footprint you do not know,
        follow it to the point of knowing."
   - Uncheedah, Grandmother of Ohiyesa

 


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.7/710 - Release Date: 3/4/2007 1:58 PM

Re: ODBC problem with Microsoft Word and Excel

From
Hiroshi Inoue
Date:
Karl Denninger wrote:

>  I’ve loaded the most recent ODBC driver off the Postgresql FTP site.

Could you try the snapshot driver at
  http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

regards,
Hiroshi Inoue


Re: ODBC problem with Microsoft Word and Excel

From
"david.paulo"
Date:
Here is what I needed to do
1) Use ANSI odbc driver - this is key  - Unicode does not work - I am using
version 9.03.04.00.  In this example the name of the odbc DSN is LFData.  It
has a username and password embedded in it.
2) It may not be needed for word merge (I am also doing separate queries)
but I registered Microsoft ActiveX Data Objects 6.1 Library from the VBA
Tools menu
3) Within the macro that performs the merge I have the following (xcfPax is
a view that provides the fields for the merge):
    SQLRequest = "select * from xcfPax where matterno = '" & matterRef & "'"

    Doc.MailMerge.MainDocumentType = wdFormLetters
    Doc.MailMerge.Destination = wdSendToNewDocument

    Doc.MailMerge.OpenDataSource _
      Name:="", _
      SQLStatement:=SQLRequest, _
      AddToRecentFiles:=False, _
      Connection:="DSN=LFData", _
      SubType:=wdMergeSubTypeWord2000

    ' Execute the mail merge.
    Doc.MailMerge.Execute
    Doc.MailMerge.DataSource.Close




--
View this message in context:
http://postgresql.nabble.com/ODBC-problem-with-Microsoft-Word-and-Excel-tp2189996p5869049.html
Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.