Обсуждение: VB and ODBC

I am a new to ODBC and relatively new to VB. I am trying to make a
connection to a postgres 7.0 DB from word via a VB macro. I think I have
installed the ODBC driver correctly (win98) and am trying to make a
connection via the VB module using the following.

Sub ClientOrder()   With ActiveDocument.MailMerge       .MainDocumentType = wdFormLetters       constr =
"DSN=PostgreSQL;"_                & "FIL=RedISAM;"      .OpenDataSource Name:="dbname", _      Connection:=constr,
SQLStatement:="SELECTClient_order_id FROM
 
client_order"   End With
End Sub

This does not work I think because of the .OpenDataSource Name:="dbname". In
other examples (ie access) it points directly the the DB File ie
'c:/somefile.mdb'. How would this work for postgres, is there an equivilant
on the linux box (I think not). Or could someone point me to some examples
of this.

Thanks in advance

Graham

<p><font size="2">-----BEGIN PGP SIGNED MESSAGE-----</font><br /><font size="2">Hash: SHA1</font><p><font size="2">When
usingAccess, because it is essentially a file-based db, rather</font><br /><font size="2">than server-based, you give
thefilename.  With Postgres, you will</font><br /><font size="2">probably not have to give the data source a name, but
youwill have</font><br /><font size="2">to change the parameters in the connection string.  I think that the</font><br
/><fontsize="2">Name parameter is used to give the connection a name internal to the</font><br /><font size="2">script
thatyou're running.  It's only a temporary thing.  The</font><br /><font size="2">connection string should probably be
somethinglike:</font><p><font size="2">constr = "ODBC;DSN=Your_DSN;UID=user;PWD=password"</font><p><font
size="2">Your_DSNneeds to be a DSN that you have already defined in the</font><br /><font size="2">control panel, user
needsto be the database user, and password for</font><br /><font size="2">the password, if it's required.  You may also
getaway with this:</font><p><font size="2">constr =</font><br /><font
size="2">"ODBC;Driver={PostgreSQL};SERVER=Your_Server;DATABASE=Your_Database;UI</font><br/><font
size="2">D=user;PWD=password"</font><p><fontsize="2">which means that you don't have to create the DSN beforehand. 
The</font><br/><font size="2">DATABASE keyword may not be right, but I can't remember what it is</font><br /><font
size="2">offhandfor Postgres.  Each vendor has it's own set of keywords,</font><br /><font size="2">although UID and
PWDseem to be pretty universal, and the ODBC prefix</font><br /><font size="2">is always required for ODBC connections
(remember,DAO doesn't assume</font><br /><font size="2">that you want to use ODBC, you have to tell it).  If you feel
like</font><br/><font size="2">tracing the connection (using the first method), then you can find</font><br /><font
size="2">outwhat the keywords are ;-) and use the second.</font><p><font size="2">Cheers...</font><br /><p><font
size="2">MikeA</font><br/><br /><br /><p><font size="2">>> -----Original Message-----</font><br /><font
size="2">>>From: Graham Vickrage [<a
href="mailto:graham@digitalplanit.com">mailto:graham@digitalplanit.com</a>]</font><br/><font size="2">>> Sent: 27
April2001 12:18</font><br /><font size="2">>> To: pgsql-interfaces@postgresql.org</font><br /><font
size="2">>>Subject: [INTERFACES] VB and ODBC</font><br /><font size="2">>> </font><br /><font
size="2">>></font><br /><font size="2">>> I am a new to ODBC and relatively new to VB. I am trying to make
a</font><br/><font size="2">>> connection to a postgres 7.0 DB from word via a VB macro. I </font><br /><font
size="2">>>think I have</font><br /><font size="2">>> installed the ODBC driver correctly (win98) and am
tryingto make</font><br /><font size="2">>> a connection via the VB module using the following.</font><br /><font
size="2">>></font><br /><font size="2">>> Sub ClientOrder()</font><br /><font size="2">>>     With
ActiveDocument.MailMerge</font><br/><font size="2">>>         .MainDocumentType = wdFormLetters</font><br /><font
size="2">>>        constr = "DSN=PostgreSQL;" _</font><br /><font size="2">>>                  &
"FIL=RedISAM;"</font><br/><font size="2">>>        .OpenDataSource Name:="dbname", _</font><br /><font
size="2">>>       Connection:=constr, SQLStatement:="SELECT Client_order_id</font><br /><font size="2">>>
FROMclient_order"</font><br /><font size="2">>>     End With</font><br /><font size="2">>> End
Sub</font><br/><font size="2">>> </font><br /><font size="2">>> This does not work I think because of the
.OpenDataSource</font><br /><font size="2">>> Name:="dbname". In</font><br /><font size="2">>> other
examples(ie access) it points directly the the DB File ie</font><br /><font size="2">>> 'c:/somefile.mdb'. How
wouldthis work for postgres, is </font><br /><font size="2">>> there an equivilant</font><br /><font
size="2">>>on the linux box (I think not). Or could someone point me to </font><br /><font size="2">>> some
examples</font><br/><font size="2">>> of this.</font><br /><font size="2">>> </font><br /><font
size="2">>>Thanks in advance</font><br /><font size="2">>> </font><br /><font size="2">>>
Graham</font><br/><font size="2">>> </font><br /><font size="2">>> </font><br /><font size="2">>>
---------------------------(endof </font><br /><font size="2">>> broadcast)---------------------------</font><br
/><fontsize="2">>> TIP 3: if posting/reading through Usenet, please send an</font><br /><font size="2">>>
appropriatesubscribe-nomail command to majordomo@postgresql.org</font><br /><font size="2">>> so that your
messagecan get through to the mailing list cleanly</font><br /><font size="2">>> </font><p><font
size="2">-----BEGINPGP SIGNATURE-----</font><br /><font size="2">Version: PGPfreeware 6.5.3 for non-commercial use
<<ahref="http://www.pgp.com" target="_blank">http://www.pgp.com</a>></font><p><font
size="2">iQA/AwUBOumJhXympNV/C086EQL99QCg2AAXwiurv69MuAp5Mjqd8xdYGo8AoNx1</font><br/><font
size="2">cQbDKazTLrPd9hGleKk9b5tV</font><br/><font size="2">=hU5o</font><br /><font size="2">-----END PGP
SIGNATURE-----</font><code><fontsize="3"><br /><br />
_________________________________________________________________________<br/> This e-mail and any attachments are
confidentialand may also be privileged and/or copyright <br /> material of Intec Telecom Systems PLC (or its affiliated
companies).If you are not an <br /> intended or authorised recipient of this e-mail or have received it in error,
pleasedelete <br /> it immediately and notify the sender by e-mail. In such a case, reading, reproducing, <br />
printingor further dissemination of this e-mail is strictly prohibited and may be unlawful. <br /> Intec Telecom
SystemsPLC. does not represent or warrant that an attachment hereto is free <br /> from computer viruses or other
defects.The opinions expressed in this e-mail and any <br /> attachments may be those of the author and are not
necessarilythose of Intec Telecom <br /> Systems PLC. <br /><br /> This footnote also confirms that this email message
hasbeen swept by<br /> MIMEsweeper for the presence of computer viruses. <br />
__________________________________________________________________________<br/></font></code> 

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When using Access, because it is essentially a file-based db, rather
than server-based, you give the filename.  With Postgres, you will
probably not have to give the data source a name, but you will have
to change the parameters in the connection string.  I think that the
Name parameter is used to give the connection a name internal to the
script that you're running.  It's only a temporary thing.  The
connection string should probably be something like:

constr = "ODBC;DSN=Your_DSN;UID=user;PWD=password"

Your_DSN needs to be a DSN that you have already defined in the
control panel, user needs to be the database user, and password for
the password, if it's required.  You may also get away with this:

constr =
"ODBC;Driver={PostgreSQL};SERVER=Your_Server;DATABASE=Your_Database;UI
D=user;PWD=password"

which means that you don't have to create the DSN beforehand.  The
DATABASE keyword may not be right, but I can't remember what it is
offhand for Postgres.  Each vendor has it's own set of keywords,
although UID and PWD seem to be pretty universal, and the ODBC prefix
is always required for ODBC connections (remember, DAO doesn't assume
that you want to use ODBC, you have to tell it).  If you feel like
tracing the connection (using the first method), then you can find
out what the keywords are ;-) and use the second.

Cheers...

MikeA

>> -----Original Message-----
>> From: Graham Vickrage [mailto:graham@digitalplanit.com]
>> Sent: 27 April 2001 12:18
>> To: pgsql-interfaces@postgresql.org
>> Subject: [INTERFACES] VB and ODBC
>>
>>
>> I am a new to ODBC and relatively new to VB. I am trying to make a
>> connection to a postgres 7.0 DB from word via a VB macro. I
>> think I have
>> installed the ODBC driver correctly (win98) and am trying to make
>> a connection via the VB module using the following.
>>
>> Sub ClientOrder()
>>     With ActiveDocument.MailMerge
>>         .MainDocumentType = wdFormLetters
>>         constr = "DSN=PostgreSQL;" _
>>                  & "FIL=RedISAM;"
>>        .OpenDataSource Name:="dbname", _
>>        Connection:=constr, SQLStatement:="SELECT Client_order_id
>> FROM client_order"
>>     End With
>> End Sub
>>
>> This does not work I think because of the .OpenDataSource
>> Name:="dbname". In
>> other examples (ie access) it points directly the the DB File ie
>> 'c:/somefile.mdb'. How would this work for postgres, is
>> there an equivilant
>> on the linux box (I think not). Or could someone point me to
>> some examples
>> of this.
>>
>> Thanks in advance
>>
>> Graham
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: 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
>>

-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>

iQA/AwUBOumJhXympNV/C086EQL99QCg2AAXwiurv69MuAp5Mjqd8xdYGo8AoNx1
cQbDKazTLrPd9hGleKk9b5tV
=hU5o
-----END PGP SIGNATURE-----

_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________


-----Original Message-----
From: Graham Vickrage [mailto:graham@digitalplanit.com]
Sent: 27 April 2001 18:03
To: Michael Ansley (UK); pgsql-interfaces@postgresql.org
Subject: RE: [INTERFACES] VB and ODBC

Thanks for the advice, but I seem to still be having probs.

 

I can't leave out the name param as its required so the only option is to leave it blank. If I put anything else in its place then it comes back with an error that the files does not exist.

 

When I give an empty string as the name there are no errors, so assuming this is working how do I then extract the data from the dataconnectioin and print it in the doc?

 

With perl DBI it might be something like

$query = DBI->connect(etc...) ;

$query->fetchrow();

 

What it the equivelant for VB?