Connecting MS Access to PostgreSQL Server

Question

I'm having trouble with connecting to our postgres server. I was given the code used to connect ms access to our SQL Server. It shall be DSN-less, I have installed an ODBC-driver and now I'm stuck finding the correct connection string to write in access' vba code.

I know it has to be somewhat like this:

stConnect = "ODBC;Driver={PostgreSQL UNICODE};Data Provider=pgsqlODBC;hostaddr=[IP-adress];Port=61000;dbname=TE_TestFirst;user=admin;password=testtest;"

Or maybe like this:

stConnect = "ODBC;Driver={PostgreSQL UNICODE};Data Provider=pgsqlODBC;SERVER=[IP-adress];PORT=61000;DATABASE=TE_TestFirst;UID=admin;PWD=testtest;"

I have read that 64Bit-Pcs need to have been given the "Data Provider". Yet nothing works except the DSN version which we don't want. I've copied countless connection string from various sides, maybe I'm overseeing something?

I look forward to your replies and thank you in advance. Puddingloeffel

You said you want connection string to Access. Why do you have Postgres connection strings in your question? — Andrey Belykh, Dec 13 '17 at 15:04
Ehh no I want to connect to Postgres. I'm using Access as the frondend. I might have expressed myself badly — Puddingloeffel, Dec 13 '17 at 15:19
Have you tried using a file DSN? That actually creates a DSN-less connection in Access — Erik A, Dec 13 '17 at 15:22
I don't think so. I'm not sure what this is, but if it is something every user needs to have to make this connection work from their computer, then I'm afraid I can't use it — Puddingloeffel, Dec 13 '17 at 15:28
@Puddingloeffel Try using it. It's a file with the DSN connection information stored inside it. If you use it to create a connection, it actually creates a DSN-less connection like you want. — Erik A, Dec 13 '17 at 15:34
Okay I will do so! Can you tell me more about this file? What it must look like, what information it needs or how I used it once I got everything? — Puddingloeffel, Dec 13 '17 at 15:42
Just create it using the ODBC data source manager. It will auto-fill with the necessary info. Use the linked table manager to use the DSN. — Erik A, Dec 13 '17 at 16:08
Most probably your driver name is incorrect. like erik said, use the datasource (odbc) manager to create a file dsn and then you can copy into your conStr. — Krish, Dec 13 '17 at 17:09
Thank you so much. My superior said I might understand connection strings better once I make it work with a dsn file. Creating the file was super easy as @Erik von Asmuth said — Puddingloeffel, Dec 14 '17 at 08:15
Note that, now you've connected the file, you can print the proper connection string using a little VBA: Just type `?CurrentDb.TableDefs("MyLinkedTable").Connect` in the immediate window. Remove the `ODBC;` and that's your connection string. Replace the name of your linked table in that command, of course. — Erik A, Dec 14 '17 at 08:20

score 2 · Answer 1 · answered Dec 15 '17 at 10:45

I have found a way to create a User-DSN within a MS Access module, which then I use to connect to the server. It is no very pretty code, but it works. For those who look for a similar solution:

    'Creating User DSN
    strAttributes = "Description=I did this myself" & Chr(13) & "Server=[Servername/ID]" & Chr(13) & "Database=TE_TestFirst" & Chr(13) & "Username=admin" & Chr(13) & "Password=***" & Chr(13) & "Port=61000"
    DBEngine.RegisterDatabase "DSNnew", "PostgreSQL Unicode", True, strAttributes
    MsgBox "DSN has been created", vbInformation

    Connection String
    stConnect = "ODBC;DSN=DSNnew;Server=[IP-adress];Port=61000;Database=TE_TestFirst;Uid=admin;Pwd=****;"

As you can see the information is embedded twice, which I really don't like but for the moment it is okay. You can check your created DSN with the ODBC Administrator.

You can find the original code here

Also this code is the way we get the tables from the server to MS Access. Although the example is about SQL Server, it works fine with PostgreSQL as well! (As I see now this side also provides a solution about creating DSNs)

score 1 · Answer 2 · answered Dec 13 '17 at 15:59

1

Something like this:

stConnect = "Driver={PostgreSQL Unicode(x64)};Servername=dbtest;Port=5432;UID=postgres;Database=postgres;Password=****";

answered Dec 13 '17 at 15:59

Andrey Belykh

2,578
4
32
46

It didn't work work for me, but thank you very much for your share :') Maybe it will help somebody else – Puddingloeffel Dec 14 '17 at 08:14
If you use 32 bit Access, try `Driver={PostgreSQL Unicode}` – Andrey Belykh Dec 14 '17 at 14:38
Oh wow thank you... in an internet full of information, this was the first useful response I could find. – Hambone Jul 31 '23 at 20:16

Puddingloeffel · Answer 3 · 2017-12-15T08:07:17.613

I haven't been able to solve the matter with a connection string that is DSN-less, but with a DSN file like @Erik van Asmuth and @krish KM said.

The DSN file is located in my Documents folder (ODBC Administrator saved it automatically in this folder. You can move it anywhere, but then you have to write the path instead of the file name)

I open it via VBA Code in MS Access with the following connection String:

stConnect = "ODBC;FILEDSN=PGUnicode32;Server=[IP-adress];Port=61000;Database=TE_TestFirst;Uid=admin;Pwd=****;"

Access got a connection and loads the table TE-TestFirst. Thank you guys again. I will now try to connect via connection string without the file

score 1 · Accepted Answer · answered Jan 26 '18 at 11:12

1

In the end we did the following:

Using connection string "PostgreSQL Unicode(x32)" written like shown above + using a different script to connect to

It now works fine and DSN-less :)

answered Jan 26 '18 at 11:12

Puddingloeffel

163
2
9

Could you please share the code you used to create a DSN less connection with your postgres server? I am having trouble connecting access to my postgres database and i would really appreciate your help on this – dodoelhos Oct 25 '20 at 09:03

score 0 · Answer 5 · answered Jul 31 '23 at 20:15

Something changed with Office 365; it seems it used to be much easier than it is now. I found it maddening, and the only real hint was from @Andrey-Belykh response.

Here's what I found that actually worked.

Under External Data->Linked Table Manager, click add. The following dialog pops up:

Select the bottom option, Custom.

Then this maddening unhelpful dialog with zero documentation (or online help shows up):

To know what you put, you need the exact description of the ODBC driver -- existing ODBC connections are a big help to this. And the description needs to be within curly braces:

So in this case the connection string starts with:

DRIVER={PostgreSQL Unicode(x64)};

Data source path can simply be "ODBC," and after the DRIVER= statement you can put in your actual connection string:

From there the normal dialog pops up with every table and view.

I hope this helps someone and saves them the time I just wasted trying to figure this out.

Connecting MS Access to PostgreSQL Server

5 Answers5