RE: VBA to connect to postgresql from MS Access - Mailing list pgsql-general

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