RE: VBA to connect to postgresql from MS Access

Thank you Adrian, 

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

 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*

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