Postgres VB6 connection

Posted on 2009-02-12
Last Modified: 2012-05-06
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.
Question by:Cristi_E
    LVL 2

    Expert Comment

    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.

    LVL 4

    Expert Comment

    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

    LVL 4

    Expert Comment

    BTW. IF you log on as postgres (the default superuser logon,) you can drop the practice database.
    LVL 4

    Author Comment

    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.
    LVL 2

    Accepted Solution

    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.  (  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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    VBA open file from excel cell 4 29
    countPairs challenge 7 36
    Paint/Redraw window while dragging 16 44
    MsgBox 2 28
    This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now