ODBC problem with Microsoft Word and Excel

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 HYPERLINK "mailto:karl(at)denninger(dot)net"karl(at)denninger(dot)net

Diving? See HYPERLINK "http://www.scubaforum.org"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

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

> 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(at)denninger(dot)net
Diving? See http://www.scubaforum.org

-----Original Message-----
From: pgsql-odbc-owner(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Richard Broersma Jr
Sent: Friday, March 02, 2007 2:40 PM
To: George Weaver; pgsql-odbc(at)postgresql(dot)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 [(at)yahoo(dot)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(at)denninger(dot)net <mailto:karl(at)denninger(dot)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
>

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

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?query=dsn+less&dg=microsoft.public.word.mailmerge.fields&cat=en_us_ee88ce09-9e34-4b0c-8aba-2e17252d39bc&lang=en&cr=us&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us

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

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 HYPERLINK "mailto:karl(at)denninger(dot)net"karl(at)denninger(dot)net

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

-----Original Message-----
From: pgsql-odbc-owner(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of George Weaver
Sent: Monday, March 05, 2007 9:20 AM
To: pgsql-odbc(at)postgresql(dot)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):

HYPERLINK
"http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?que
ry=dsn+less&dg=microsoft.public.word.mailmerge.fields&cat=en_us_ee88ce09-9e3
4-4b0c-8aba-2e17252d39bc&lang=en&cr=us&pt=&catlist=&dglist=&ptlist=&exp=&slo
c=en-us"http://www.microsoft.com/communities/newsgroups/list/en-us/default.a
spx?query=dsn+less&dg=microsoft.public.word.mailmerge.fields&cat=en_us_ee88c
e09-9e34-4b0c-8aba-2e17252d39bc&lang=en&cr=us&pt=&catlist=&dglist=&ptlist=&e
xp=&sloc=en-us

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

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.