Thread: VBA to connect to postgresql from MS Access
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
DRIVER={PostgreSQL Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"
I want to use VBA to relink all tables and use DSN less connection string.
Le 01-06-18 à 14:21, Łukasz Jarych a écrit : > 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=AccessTest;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. Why is this not working, we cannot answer without the error message you get. You probably use the wrong driver name. As for the CA, BO, etc... they are abreviations of the keywords. You can use either the keywords or the abreviations in your connection string, and they are all explained here : https://odbc.postgresql.org/docs/config-opt.html -- Arnaud
connection..ADO, DAO, .NET
I don't hard code connection strings anymore, but I did when I first started.
Nowadays I call a registry function to put the drivers on a specific pc into a listbox
and have a setup module which then builds the connection string on the fly from the
select listbox value.
The below sample may not work, I haven't tested it, but the basic elements are there to help
you start thinking about how it does work.
The DRIVER= element of the connection must have the name of the driver installed on your
pc. Usually they can be found under the ODBC administrator tool, found in Control Panel, Administrative Tools,
ODBC Data Source Administrator. If you're creating a DSN-LESS connection, then click the Drivers tab and scroll to the
Postgresql drivers
The DRIVER= element must exactly match the name of the driver in the [Drivers] tab under the ODBC administrator
tool. In the code example below, the driver name on my pc is PostgreSQL ANSI
The other elements are well documented
Server= (an ip address or hostname of the database server you are connecting to)
Port= (Can generally be left at 5432...the postgresql default port)
Database= (is the case sensitive name of the postgresql database you are connecting)
UID= (postgresql user name to make the connection under)
PWD= (the password the connecting user)
If you are still having problems, there is the pg_hba.conf file which may block access.
You can read about that here. https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
try creating a simple form, add a button, and have the button click event call the below
function AFTER you have modified the elements to suit your database name, server etc
hopefully some of this info will help make sense
Function pg_db_open() as boolean
Dim conNew As New ADODB.Connection
Dim cs as string
cs = "DRIVER=PostgreSQL ANSI; Server=your-server-ip; Port=5432; Database=your-database-name; UID=username; PWD=password"
On Error GoTo conError
conNew.ConnectionString = cs
conNew.Open
Set conNew = Nothing
pg_db_open = True
Exit Function
conError:
Set conNew = Nothing
pg_db_open = False
End Function
On Fri, 2018-06-01 at 14:27 +0200, Arnaud L. wrote:
Le 01-06-18 à 14:21, Łukasz Jarych a écrit : > 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=AccessTest;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. Why is this not working, we cannot answer without the error message you get. You probably use the wrong driver name. As for the CA, BO, etc... they are abreviations of the keywords. You can use either the keywords or the abreviations in your connection string, and they are all explained here : https://odbc.postgresql.org/docs/config-opt.html
-- Bret Stern Machine Management Industrial and Commercial IT Services 707-775-9792 (cell-text-direct) |
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=AccessTest;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
On 06/02/2018 11:15 AM, Łukasz Jarych wrote: > Anyone? https://www.postgresql.org/message-id/1527872841.3939.24.camel%40bret.machinemanagement.com > > Jacek > > 2018-06-01 14:21 GMT+02:00 Łukasz Jarych <jaryszek@gmail.com > <mailto:jaryszek@gmail.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=AccessTest;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.klaver@aklaver.com
Public Sub InitConnect()On Error GoTo ErrHandlerDim dbCurrent As DAO.DatabaseDim qdf As DAO.QueryDefDim rst As DAO.RecordsetDim cnn As ObjectSet cnn = CreateObject("Adodb.Connection")sConnString = "DRIVER={PostgreSQL Unicode(x64)};DATABASE=AccessTest;SERVER=localhost;PORT=5432;UID=postgres;PWD=1234;"cnn.Open sConnStringSet dbCurrent = DBEngine(0)(0)Set qdf = dbCurrent.CreateQueryDef("")With qdf.Connect = sConnString.SQL = "select CURRENT_USER;"Set rst = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)End With'' InitConnect = TrueExitProcedure:On Error Resume NextSet rst = NothingSet qdf = NothingSet dbCurrent = NothingExit SubErrHandler:''InitConnect = FalseMsgBox Err.Description & " (" & Err.Number & ") encountered", _vbOKOnly + vbCritical, "InitConnect"Resume ExitProcedureResumeEnd 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*
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@gmail.com <mailto:jaryszek@gmail.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=AccessTest;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.klaver@aklaver.com
Attachment
From: Łukasz Jarych [mailto:jaryszek@gmail.com]
Sent: Monday, June 04, 2018 12:30 AM
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-general@postgresql.org >> PG-General Mailing List <pgsql-general@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
On 06/03/2018 09:30 PM, Łukasz Jarych wrote: > Thank you Adrian, > > in answer to response in link: > > This connection string is not working for me. How it is not working? Do you get error messages? > And my conf file is in attachment. > > Only this strange DSN less conn string is working: I am not following the below does use a DSN. What are the parameters for the DSN PostgreSQL35W? > > 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 -- Adrian Klaver adrian.klaver@aklaver.com
Problem was with connection string and 32 bit computer.
My access is 32 bit so i should have odbc 32 bit driver for postgresql...
Best,
Jacek
On 06/03/2018 09:30 PM, Łukasz Jarych wrote:Thank you Adrian,
in answer to response in link:
This connection string is not working for me.
How it is not working?
Do you get error messages?And my conf file is in attachment.
Only this strange DSN less conn string is working:
I am not following the below does use a DSN.
What are the parameters for the DSN PostgreSQL35W?--
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
Adrian Klaver
adrian.klaver@aklaver.com