RE: VBA to connect to postgresql from MS Access

From: Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com]
Sent: Monday, June 04, 2018 12:30 AM
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Cc: pgsql-general(at)postgresql(dot)org >> PG-General Mailing List <pgsql-general(at)postgresql(dot)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