[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2793
  • Last Modified:

Postgres VB6 connection

I need a sample vb6 code to see how to connect to a postgres database, or other useful resources to help me with that problem.
  • 2
  • 2
1 Solution
I understand this isn't a complete answer but...

When I installed Postgres on my Windows machine, it created a "system dsn"

Control Panel-->Administrative Tools-->Data Sources  Then click on the system DSN tab.  If there isn't a data source defined for your database, you can click "ADD" and add one.  Once you have a DSN defined that is half the battle.  I'll try to post a link or two to tutorials later tonight (my time) unless someone beats me to it.

hi cristi
try this code
strSQL = Driver={PostgreSQL ANSI}; Server=localhost; PORT=5432; " _ 
      & "Database=" & gstrPGDB & "; UID=user; PWD=pass"
debug.print strSQL
connPG.Open strSQL  ' <----- Add a break point here, and verify that strSQL contains 
                    '        the exact database name. 
Set rsPractice = New ADODB.Recordset 'Practice file
rsPractice.Cursorlocation = adUseServer
strSQL = "SELECT * FROM public.practice"
rsPractice.Open strSQL, connPG, adOpenStatic, adLockPessimistic
debug.print "rsPractice.EOF = " & rsPractice.EOF
debug.print rsPractice.Fields!address1

Open in new window

BTW. IF you log on as postgres (the default superuser logon,) you can drop the practice database.
Cristi_EAuthor Commented:
I'm not able to find any documentation for the functions exposed by pgoledb.dll (i don't know what methods and what properties have the objects).

I would like a complete example to indicate me what references should i add in my VB6 project.

I have installed Postgres 8.3 and i use Visual Basic 6.

I realized that the ODBC connection is extremely slow for databases having the size of 2GB. I need to replace MS Access, but the ODBC connection is out of discution. I practically need a substitute for DAO 3.6.

Ideal would be to connect to a Postgres database located on a Linux Server with a VB6 program using its IP and port  (so preferable without installing Samba), but no way through ODBC.

Thank you.
I realize that ODBC can be slower for large databases but I'm not sure what the difference would be on a database that is well-indexed.  It might be worth the time to benchmark it just to see.

I got the code below from the PostgreSQL forums.  (http://www.postgresqlforums.com)  As for connecting to a database on a Linux box I'm not sure the syntax but I would first try using the IP address of it instead of "localhost"  (see below).  You will also have to check the PORT to make sure it is running on that port for Linux.
    Attribute VB_Name = "Module1"
    Sub Main()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
      'Open the connection
      cn.Open "Driver={PostgreSQL ANSI};" & _
              "Server=;" & _
              "Port=5432;" & _
              "UID=<Your Username>;" & _
              "PWD=<Your Password>;" & _
      'Open cursor
      rs.Open "SELECT field1, field2, field3 FROM mytable ", cn, adOpenForwardOnly, adLockReadOnly
      'Loop though the recordset and print the results
      While Not rs.EOF
        Debug.Print rs!field1 & "," & rs!field2 & "," & rs!field3
      If rs.State <> adStateClosed Then rs.Close
      Set rs = Nothing
      If cn.State <> adStateClosed Then cn.Close
      Set cn = Nothing
    End Sub

Open in new window


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now