Thread: ODBC problem with Microsoft Word and Excel

--
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

> 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.

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

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

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
>

--
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

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

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.