Share System DSN with users on windows server 2012

[jaryszek]

Hi Guys,

i set up System DSN ODBC connection to postgresql:



And next i want to connect to this odbc via Access from other user account using the same Virtual Machine,
i do not have System DSN tab...:





how can i share DSN with other users?

Interesting fact: As Administrator when i am trying to linked table to postgresql also there is no system DSN tab...

Please help,
Jacek

[jaryszek]

Hmm i think this is not possible...
Only way is to copy register entries to the user account.

Hmm so in this case should i use DSN-less connections?

How this should work?
While opening copy of FE from shared folder on user's account macro should run and check if there are new tables?

Best,
Jacek

[Gicu]

Jacek,

It could be just a 32bit vs. 64 bit issue, as you show a 64 bit DSN. In any case, in my experience you don't need the DSN for every user once you properly connected the front-end to the back-end. The connection information for each linked table is stored in the Connect field in the msysyObjects table. As long the users have the proper permissions to the back-end and their login profile has the appropriate driver installed they should be OK.

Cheers,
Vlad

[jaryszek]

thank you Vlad,

I checked 32 bit driver and 64 bit, 32 bit works only.
And i think issue is within windows server 2012 - there is only Machine source and DSN file to set up odbc connection.



Maybe here is a problem?

Best,
Jacek

[jaryszek]

I am using 32 bit Access and 64 bit postgresql and odbc 32 bit is working fine...

Hmm very strange problem,

Best,
Jacek

[jaryszek]

Anyone>?

Jacek

[Gicu]

Jacek,

Once you link the tables in your first account the connection info should be stored by Access and you shouldn't need to "refresh" the DSN for every user as long as each user profiles has access to the back-end and the driver. Isn't that the case for you? If you do need to share the connection info try to save it as a file DSN instead of system DSN, looks like you have access to the File DSN tab in both accounts.

Cheers,
Vlad

[jaryszek]

Hi Vlad,

thank you for help.

Once you link the tables in your first account the connection info should be stored by Access and you shouldn't need to "refresh" the DSN for every user as long as each user profiles has access to the back-end and the driver. Isn't that the case for you?

Strange, but this is not working

If you do need to share the connection info try to save it as a file DSN instead of system DSN, looks like you have access to the File DSN tab in both accounts.

It works.

I will try with DSN less connection and this trick:
https://www.microsoft.com/en-us/micr...e-connections/

Best,
Jacek

[Gicu]

Hi Jacek,

Have you looked at the Connect field in msysObjects to see if you can spot why is not working for everyone? Are you sharing a Postgressql user/password or are you using trust authentication?

Cheers,
Vlad

[jaryszek]

Hi Vlad,

thank you.

I have DSN connection string on my user Connect field in FE:

ODBC;DSN=PostgreSQL35W;DATABASE=AccessTest;SERVER=localhost;PORT=5432;CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305

And i have error:



Of course there is no PostgreSQL35W DSN set up and this is causing the problem.

But what you said DSN should be automatically moved to user account.

Best,
Jacek

[Gicu]

Jacek,

Do you have the exact same string in msysObjects Connect field?

Cheers,
Vlad

[Gicu]

Have a look at the two screen shots showing the connect string from the properties sheet in table design (the one you used) vs. the connect field from msysyObjects. You can see that that one specifies the driver, user id and password. That is the one Access uses to connect to the back-end and that is where you want to focus your efforts. Do you have the same driver installed and available when you log in as a different user? If using trust authentication is that user set up with permissions to access the back-end?




Cheers,
Vlad

[jaryszek]

Hi Vlad,

the connection string is almost the same:

(I pasted from msysObjects into Validation text field):



msysObjects:


There is no ODBC in msysObjects.
Hmmm.

UPDATE

Thank you Vlad, thanks for your tips and help.
I found the issue.

I had ODBC driver for 64 bit as system DNS. In this tab should be PostgreSQL odbc driver but for 32 BIT.

Thank you!
Jacek