RE: VBA to connect to postgresql from MS Access

[Relyea, Mike]

From: Łukasz Jarych [mailto:jarys...@gmail.com]
Sent: Monday, June 04, 2018 12:30 AM
To: Adrian Klaver <adrian.kla...@aklaver.com>
Cc: pgsql-gene...@postgresql.org >> PG-General Mailing List 
<pgsql-gene...@postgresql.org>
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