This snippit (below) is exactly what you need to connect (assuming you have
set up a system DSN after having installed the ODBC driver). Use VB6, Ado
(2.7 for example) and the postgreSQL ODBC and you're off. SQL constructs
are passed through exactly as shown below. I've been doing it before this
database was ported to win and it has worked great all along.
Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
'Open the connection
cn.Open "DSN=PostgreSQL;UID=dave;PWD=password;"
'Open the recordset
rs.Open "SELECT relname FROM pg_class", cn
'Loop though the recordset print the results
While Not rs.EOF
Debug.Print rs!relname
rs.MoveNext
Wend
'Cleanup
If rs.State <> adStateClosed Then rs.Close
Set rs = Nothing
If cn.State <> adStateClosed Then cn.Close
Set cn = Nothing
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: April 30, 2005 1:10 AM
To: [email protected]
Subject: VB ODBC connection to Postgresql, DAO,ADO,RDO,DSN???
I was very pleased today, to be successful in connecting to the windows
postgresql server across our simple p2p connection. (It all had to do with
turning off the windows fire wall, after folks here helped me with the .conf
ip address settings, and listening).� What is nice is that I have sygate
free fire wall, which simply pops up and asks me if I want to allow the
postgres connecting activity. So I may connect and still have a firewall.
�
Now, the next phase of my problem, to make something useful happen.� I can
connect like a breeze with rekall, and edit tables, BUT, I havent the
foggiest notion how to write a python script in rekall to do what I really
want to do with my tables.
�
I dont really understand how rekall will help folks, if they dont have demo
examples to do the sort of thing i am attempting.
�
IF, we had money at work, and could afford Access, then I think I would be
all set, because I see how I can do a lot in Access VBA. But we cant afford
to get it at work. I happen to have it at home, because it came with my
Dell.
�
Now I DO have a copy of Visual Basic 6.x and I can easily connect to
Postgres with the application wizard, and data aware controls.� But what I
really want to be able to do is to have a simple form, with a button, and be
able, within the script of that button, to do sql passthrough to postgres,
and then manipulate the rows returned to me within the VBA.
�
BUT, it is very hard for me to discover the precise grammar for establishing
such a simple connection.�� Below, I am posting what I have collected from
google of snippets which SEEM like the might do what I want....� But if
anyone has a working examle for me, I would be most grateful.� Or if you
know an easy way for me to do what I want in some kind of Pearl for
windows.� I dont need a form painter with navigation buttons.� What I need
is a script that I can put into a look, to look for a certain value in a
table,..... wait for a few seconds, look again, and when it FINDS that row
with that field value,... then do some things, delete that record or mark it
somehow... and go back into more waiting and looking, until the next
row/field value appears.� It could be selecting on a very small table with
just a few rows.� THe idea is that another workstation would add a row to
the table which would cause THIS workstation to read it, and display
something on the screen.�� So... take a look below, and you will see the
sort of thing I am trying to do.
�
I think if I had Powerbasic or Realbasic, then perhaps I could more easily
do what I want, but I just dont have the money right now to run out and buy
a lot of things.�� I also have Visual FOxpro version 5, and I thought I
could work with that, but, it is having trouble connecting to postgres via
odbc...
�
- Thanks
--------------------------=========snippets of VB code
�
�
�
http://archives.postgresql.org/pgsql-odbc/2003-07/msg00041.php
�
Sub PreConnect(strUserName As String, strPassword As String,
strDatabase As String)
�
��� Dim wrkRemote As Workspace, dbsRemote As Database
��� Dim strConnect As String
�
��� strConnect = "ODBC;DSN=PostgreSQL;DATABASE=" + strDatabase
�
+
";SERVER=pgserver;PORT=5432;UID=" + strUserName + ";PWD=" +
strPassword +
";READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=0;SHOWOIDCOLUMN=
�
0;ROWVERSIONING=0;SHOWSYSTEMTABLES=1;CONNSETTINGS="
�
��� Set wrkRemote = DBEngine.Workspaces(0)
��� Set dbsRemote = wrkRemote.OpenDatabase("", False, False,
�
strConnect)
��� dbsRemote.Close���������������� ' Close database but keep connection.
�
End Sub
�
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dn
�
vbpj00/html/sqlado.asp
�
While Not Adodc1.Recordset.EOF
��� Debug.Print Adodc1.Recordset!company
��� Adodc1.Recordset.MoveNext
� Wend
�
Dim m_connect as new adodb.connection
�
M_connect.connectionstring = "File Name c:\myfolder\mydata.mdb"
�
M_connect.open
�
+++++
Dim cn as New ADODB.Connection
Dim rs as New ADODB.Recordset
�
� 'Open the connection
� cn.Open "DSN=PostgreSQL;UID=dave;PWD=password;"
�
� 'Open the recordset
� rs.Open "SELECT relname FROM pg_class", cn
�
� 'Loop though the recordset print the results
� While Not rs.EOF
��� Debug.Print rs!relname
��� rs.MoveNext
� Wend
�
� 'Cleanup
� If rs.State <> adStateClosed Then rs.Close
� Set rs = Nothing
� If cn.State <> adStateClosed Then cn.Close
� Set cn = Nothing
�
++++++++++++++
Dim m_connect as new adodb.connection
� Dim db As new adodb.connection
� Set db = New Connection
� db.CursorLocation = adUseClient
� db.Open
�
"PROVIDER=MSDASQL;dsn=PostgreSQL;uid=neil;pwd=password;data
�
base=bpsimple;"
�
� Set adoPrimaryRS = New Recordset
� adoPrimaryRS.Open "select
�
customer_id,title,fname,lname,addressline,town,zipcode,phone from
�
customer Order by fname", db, adOpenStatic, adLockOptimistic
�
� 'Loop though the recordset print the results
� While Not adoPrimaryRS.EOF
��� Debug.Print adoPrimaryRS!lname
��� adoPrimaryRS.MoveNext
� Wend
�
�
�
�
�
Private Sub adoPrimaryRS_MoveComplete(ByVal adReason As
�
ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As
�
ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
� 'This will display the current record position for this recordset
� lblStatus.Caption = "Record: " &
�
CStr(adoPrimaryRS.AbsolutePosition)
End Sub
�
Private Sub adoPrimaryRS_WillChangeRecord(ByVal adReason As
�
ADODB.EventReasonEnum, ByVal cRecords As Long, adStatus As
�
ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
� 'This is where you put validation code
� 'This event gets called when the following actions occur
� Dim bCancel As Boolean
�
�
�
Private Sub cmdAdd_Click()
� On Error GoTo AddErr
� With adoPrimaryRS
��� If Not (.BOF And .EOF) Then
����� mvBookMark = .Bookmark
��� End If
��� .AddNew
��� lblStatus.Caption = "Add record"
��� mbAddNewFlag = True
��� SetButtons False
� End With
�
� Exit Sub
AddErr:
� MsgBox Err.Description
End Sub
�
Private Sub cmdDelete_Click()
� On Error GoTo DeleteErr
� With adoPrimaryRS
��� .Delete
��� .MoveNext
��� If .EOF Then .MoveLast
� End With
� Exit Sub
�
Private Sub cmdRefresh_Click()
� 'This is only needed for multi user apps
� On Error GoTo RefreshErr
� adoPrimaryRS.Requery
� Exit Sub
RefreshErr:
� MsgBox Err.Description
End Sub
�
Private Sub cmdEdit_Click()
� On Error GoTo EditErr
�
� lblStatus.Caption = "Edit record"
� mbEditFlag = True
� SetButtons False
� Exit Sub
�
EditErr:
� MsgBox Err.Description
End Sub
Private Sub cmdCancel_Click()
� On Error Resume Next
�
� SetButtons True
� mbEditFlag = False
� mbAddNewFlag = False
� adoPrimaryRS.CancelUpdate
� If mvBookMark > 0 Then
��� adoPrimaryRS.Bookmark = mvBookMark
� Else
��� adoPrimaryRS.MoveFirst
� End If
� mbDataChanged = False
�
End Sub
�
Private Sub cmdUpdate_Click()
� On Error GoTo UpdateErr
�
� adoPrimaryRS.UpdateBatch adAffectAll
�
� If mbAddNewFlag Then
��� adoPrimaryRS.MoveLast������������� 'move to the new record
� End If
�
� mbEditFlag = False
� mbAddNewFlag = False
� SetButtons True
� mbDataChanged = False
�
� Exit Sub
UpdateErr:
� MsgBox Err.Description
End Sub
�
Private Sub cmdClose_Click()
� Unload Me
End Sub
�
Private Sub cmdFirst_Click()
� On Error GoTo GoFirstError
�
� adoPrimaryRS.MoveFirst
�
�
�
� adoPrimaryRS.MoveLast
�
�
�
� If Not adoPrimaryRS.EOF Then adoPrimaryRS.MoveNext
� If adoPrimaryRS.EOF And adoPrimaryRS.RecordCount > 0 Then
��� Beep
���� 'moved off the end so go back
��� adoPrimaryRS.MoveLast
� End If
�
� If Not adoPrimaryRS.BOF Then adoPrimaryRS.MovePrevious
� If adoPrimaryRS.BOF And adoPrimaryRS.RecordCount > 0 Then
��� Beep
��� 'moved off the end so go back
��� adoPrimaryRS.MoveFirst
� End If
�
====================end of snippets of VB code
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
