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

2018-06-02 20:52 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 06/02/2018 11:15 AM, Łukasz Jarych wrote:
>
>> Anyone?
>>
>
> https://www.postgresql.org/message-id/1527872841.3939.24.cam
> el%40bret.machinemanagement.com
>
>
>> Jacek
>>
>> 2018-06-01 14:21 GMT+02:00 Łukasz Jarych <jaryszek(at)gmail(dot)com <mailto:
>> jaryszek(at)gmail(dot)com>>:
>>
>> Hi Guys,
>>
>> When i have linked table based on DSN odbc connection to postgresql ,
>> i am going to :
>>
>> VBA editor, in immediate window inputing:
>>
>> ?CurrentDb.TableDefs("TableName").Connect
>> i have very strange connection string:
>>
>> 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*
>>
>>
>>
>> When i was searching code for this in internet i found only:
>>
>> DRIVER={PostgreSQL Unicode(x64)};DATABASE=AccessT
>> est;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"
>>
>> but this is not working, why? I do not know what these CA, BO, BI
>> strange paramaters.
>>
>> I want to use VBA to relink all tables and use DSN less connection
>> string.
>>
>> Please help,
>> Best,
>> Jacek
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>