Re: VBA to connect to postgresql from MS Access

[Łukasz Jarych <jaryszek@xxxxxxxxx>]

Thank you Adrian, 

in answer to response in link:

This connection string is not working for me. 

Ma macro is:

Public Sub InitConnect()

On Error GoTo ErrHandler

    Dim dbCurrent As DAO.Database

    Dim qdf As DAO.QueryDef

    Dim rst As DAO.Recordset

    Dim cnn As Object

    

    Set cnn = CreateObject("Adodb.Connection")

  sConnString = "DRIVER={PostgreSQL Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"

  

  cnn.Open sConnString

    

    Set dbCurrent = DBEngine(0)(0)

    Set qdf = dbCurrent.CreateQueryDef("")

    

    With qdf

        .Connect = sConnString

        .SQL = "select CURRENT_USER;"

        Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)

    End With

   '' InitConnect = True

ExitProcedure:

    On Error Resume Next

        Set rst = Nothing

        Set qdf = Nothing

        Set dbCurrent = Nothing

    Exit Sub

ErrHandler:

    ''InitConnect = False

    MsgBox Err.Description & " (" & Err.Number & ") encountered", _

        vbOKOnly + vbCritical, "InitConnect"

    Resume ExitProcedure

    Resume

End Sub

And my conf file is in attachment. 

Only this strange DSN less conn string is working:

 ODBC;DSN=PostgreSQL35W;DATABASE=AccessTest;SERVER=localhost;PORT=5432;*CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4*

Why? 

Best,

Jacek  

2018-06-02 20:52 GMT+02:00 Adrian Klaver <adrian.klaver@xxxxxxxxxxx>:

On 06/02/2018 11:15 AM, Łukasz Jarych wrote:

Anyone?

https://www.postgresql.org/message-id/1527872841.3939.24.camel%40bret.machinemanagement.com

Jacek

2018-06-01 14:21 GMT+02:00 Łukasz Jarych <jaryszek@xxxxxxxxx <mailto:jaryszek@xxxxxxxxx>>:

    Hi Guys,

    When i have linked table based on DSN odbc connection to postgresql ,
    i am going to :

    VBA editor, in immediate window inputing:

    ?CurrentDb.TableDefs("TableName").Connect
    i have very strange connection string:

        ODBC;DSN=PostgreSQL35W;DATABASE=AccessTest;SERVER=localhost;PORT=5432;*CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4*



    When i was searching code for this in internet i found only:

        DRIVER={PostgreSQL Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"

    but this is not working, why? I do not know what these CA, BO, BI
    strange paramaters.

    I want to use VBA to relink all tables and use DSN less connection
    string.

    Please help,
    Best,
    Jacek

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx

Attachment: postgresql.conf
Description: Binary data