VB questions re: Postgresql, Connect...etc

Thanks Jeff, and all, for suggestions:

_http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-visualbasic_
(http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-visualbasic)


I have Visual Basic 6.0

The code at this page looks like exactly what I need.

I anticipate my problems as follows:

1:) A reference in the VB project to Microsoft ActiveX Data Objects I know
that I must somehow go to tools and check off certain tools for the project so
that the VBA code will be able to make the odbc connection. I am assuming
that the above "Microsoft ActiveX Data

Objects" will somehow guide me to checking the correct box and adding the
correct object(s) to the project.

2:) I always have difficulties with statements like this:
cn.Open "DSN=<MyDataSourceName>;" & _
"UID=<MyUsername>;" & _
"PWD=<MyPassword>;" & _
"Database=<MyDatabaseName>"

I know, for example that my user name will be neil and my password will be
"password" , based upon an exercise I did in my book on Postgresql. I assume
that my database name will be "bpsimple" which is the name of the database
created by the example, for user
"neil". I know that I can go into windows, into the ODBC administration,
and create a new DSN name, and I shall be offered some choices like "system",
"user", "file",.... but the DSN name is where I run into confusion and
trouble. It will SAVE the DSN name someplace under the name of my choosing, and I
know that MSAccess displays such names to me during the dialogue to link to an
external datasource.

3.) Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset

I know that, if I do not add the correct objects/modules to my vb project,
and get the DSN correct, that I shall have problems with Dim cn as New
ADODB.Connection.

But I shall begin to experiment with all these things, for the script
example is exactly what I need, IF I can get it to work and talk to the Postgresql
server through odbc under windows.

Hence, I shall create a simple .exe project in VB with a window and a
button. I shall endeavor to add the objects to that project with pertain to
"Microsoft ActiveX Data Objects".

For example, here comes the maddening part for me right now. I have launched
VB, and started a project and placed a button on the form, but NOW, I must
remember from tutorials what to click on to ADD the Microsoft ActvieX Data
Objects

I just went to Projects, added a Dataenvironment , am looking at the
Datalink Properties, have chosen PostgreSQL, added user name and password, which is
postgres and my password.... it verifies that the connection works, but it
does not work with the INITIAL CATALOG TO USE field, nor am i quite certain what
to put in such a field. WAIT....!!!
now, upon further inspection, I PAGE DOWN and see that there is a POSTGRES
OLE provider... so obviously I should choose that (i think)...so NOW it asks
for DATASOURCE and LOCATION... so, do I put "localhost" or some ip address...
or what.....
SOOO... I go back to my ODBC PostgreSQL choice,... and see that connection
works.... AND, if I take the choice that says BUILD CONNECTION STRING, then it
returns:

DRIVER={PostgreSQL};DATABASE=bpsimple;SERVER=localhost;PORT=
5432;UID=neil;PWD=password;ReadOnly=0;Protocol=6.4;FakeOidInd
ex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;Con
nSettings=;Fetch=100;Socket=8192;UnknownSizes=0;MaxVarcharSiz
e=254;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=
1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;UnknownsAsLon
gVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;ExtraSysTa
blePrefixes=dd_;LFConversion=1;UpdatableCursors=0;DisallowPrema
ture=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSide
Prepare=0


So this represents great learning progress for me, because for the first
time, I have found a way to build that complex string, which might be needful in
VBA script. But, now I must understand how to translate all of this into a
DSN name for the vba script example, DSN=<MyDataSourceName> Now, the
DATASOURCE NAME seems to be PostgreSQL in the Data Link Properties dialogue, and yet,
in the ODBC windows administrator,
FILE DSN, I have a definition which I saved as "bpsimple"

So, how to fill in properly:

'Open the connection
cn.Open "DSN=bpsimple;" & _
"UID=neil;" & _
"PWD=password;" & _
"Database=bpsimple"

????????????????????????????????????????

or should it be:

'Open the connection
cn.Open "DSN=PostgreSQL;" & _
"UID=neil;" & _
"PWD=password;" & _
"Database=bpsimple"

??????????

I shall experiment over and over with Windows ODBC to define a DSN and store
it in different places, until I get it to work. And I shall post my problems
to these groups. Thanks for the help!

=============== code example

Sub Main()
Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset

'Open the connection
cn.Open "DSN=<MyDataSourceName>;" & _
"UID=<MyUsername>;" & _
"PWD=<MyPassword>;" & _
"Database=<MyDatabaseName>"

'For updateable recordsets we would typically open a Dynamic

recordset.
'Forward Only recordsets are much faster but can only scroll forward

and
'are read only. Snapshot recordsets are read only, but scroll in both
'directions.
rs.Open "SELECT id, data FROM vbtest", cn, adOpenDynamic

'Loop though the recordset and print the results
'We will also update the accessed column, but this time access it

through
'the Fields collection. ISO-8601 formatted dates/times are the safest

IMHO.
While Not rs.EOF
Debug.Print rs!id & ": " & rs!data
rs.Fields("accessed") = Format(Now, "yyyy-MM-dd hh:mm:ss")
rs.Update
rs.MoveNext
Wend

'Add a new record to the recordset
rs.AddNew
rs!id = 76
rs!data = 'More random data'
rs!accessed = Format(Now, "yyyy-MM-dd hh:mm:ss")
rs.Update

'Insert a new record into the table
cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random

data');"

'Refresh the recordset to get that last record...
rs.Refresh

'Get the record count
rs.MoveLast
rs.MoveFirst
MsgBox rs.RecordCount & " Records are in the recordset!"

'Cleanup
If rs.State <> adStateClosed Then rs.Close
Set rs = Nothing
If cn.State <> adStateClosed Then cn.Close
Set cn = Nothing
End Sub
=================end code example