VB questions re: Postgresql, Connect...etc

Thanks Jeff, and all, for suggestions:

 

 

 

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