ODBC DSN setup with password


I have an Access database consisting of tables linked to  Postgres database tables via an ODBC DSN.  However, everytime I open the Access database I have to enter a user and password to get to the tables.   I would like not to have to do this, as this is not a security risk for what I'm doing.  I supplied the user and password when I created the DSN but that didn't seem to amount to much.

In short, I would like to know how to setup an ODBC DSN such that I do not have to enter a password everytime I open the database.

-G
gmanpertAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

m1tk4Commented:
You have to check "save password" option in Access when you are linking to these tables. Your password will get saved into the Access mdb file and it's not very secure but that's the only way to do it. You can't just store the password in the DSN - the DSN's are pretty much used only once when you link the table, they are read and the information from them is stored in the .mdb file. To re-read them you need to either remove the tables and reconnect them again or use Linked Tables Manager.
m1tk4Commented:
This option - "Save password" appears at the last stage when you link the tables, when you actually select the available tables to be linked, it's a checkbox in the bottom right corner of the dialog.
gmanpertAuthor Commented:
Actually, that doesn't seem to work.  If I do what you suggest then reopen the database i get the following error:

'ODBC--connection to '{PostgreSQL}server.domain.edu' failed'

-g
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

m1tk4Commented:
Did you enter the password in the PgSQL driver dialog (i.e. after you select the PostgreSQL as the driver)?

That's the way I have it working in about a dozen of Access databases.
gmanpertAuthor Commented:
Run that by me again?  I  entered PWD=password in the DSN file which gets used but that did not help.  I entered the password during the time I linked each of the tables in Access.

It has been so long ago, I can't even remember how I got the original Access DB linked to Postgres.

-g
m1tk4Commented:
Don't enter the PW in the DSN, just type one in as you link them and don't forget to check "save password". Here is an example of DSN I used:

[ODBC]
DRIVER=PostgreSQL
UID=MyUserID
UseServerSidePrepare=0
ByteaAsLongVarBinary=0
BI=0
TrueIsMinus1=1
DisallowPremature=0
UpdatableCursors=1
LFConversion=1
ExtraSysTablePrefixes=dd_
CancelAsFreeStmt=0
Parse=0
BoolsAsChar=1
UnknownsAsLongVarchar=0
TextAsLongVarchar=1
UseDeclareFetch=0
Ksqo=1
Optimizer=1
CommLog=0
Debug=0
MaxLongVarcharSize=8190
MaxVarcharSize=254
UnknownSizes=0
Socket=4096
Fetch=100
ConnSettings=
ShowSystemTables=0
RowVersioning=0
ShowOidColumn=1
FakeOidIndex=1
Protocol=6.4
ReadOnly=0
PORT=5432
SERVER=myserver
DATABASE=mydb


You can edit them directly in Program FIles/Common Files/ODBC/DataSources.
gmanpertAuthor Commented:
unfortunately, when I do this I get the results I posted in my first response, namely the
"ODBC -- connection to ... " error.  Is there any documentation around on how to set up a Postgres DSN on a Windows machine?

-G
m1tk4Commented:
What is the DSN type you are creating? File/System/Etc.
gmanpertAuthor Commented:
At this point I would be happy to create anything which works.  However, I've been trying to create a File DSN.

-g
m1tk4Commented:
Really weird, never had a problem with this. When you rest your mouse cursor over a linked table in Access, what shows up in the hint (Should start with ODBC;DRIVER={Postgres....)

BTW, what's your Access version?

Also, can you post your pg_hba.conf?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
m1tk4Commented:
What was the issue?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.