RE: VBA to connect to postgresql from MS Access

["Relyea, Mike" <Mike.Relyea@xxxxxxxxx>]

 

 

From: Łukasz Jarych [mailto:jaryszek@xxxxxxxxx]
Sent: Monday, June 04, 2018 12:30 AM
To: Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx >> PG-General Mailing List <pgsql-general@xxxxxxxxxxxxxx>
Subject: Re: VBA to connect to postgresql from MS Access

 

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  

 

 

 

 

 

If you’re using a 32 bit version of MS Access, you need to use the 32 bit ODBC driver not the 64 bit driver.  Try changing the connection string from sConnString = "DRIVER={PostgreSQL Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;" to sConnString = "DRIVER={PostgreSQL Unicode};SERVER=localhost;DATABASE=AccessTest;PORT=5432; UID=postgres;PWD=1234;"

 

Mike