ODBC DSN setup

I have a postgres database setup on a remote server.  The currently working DSN on my windows laptop was set up by someone else.  When I run PERL programs on my laptop which query the database (and work fine) the name of the DSN is 'tdsn'.   Here is what threw me and what I need help with.  On my windows laptop, under
>control panel >administrative tools >Data Sources(ODBC)
  There are three tabs - System DSN, User DSN, File DSN.

Originally, there was both a System DSN and a File DSN named 'tdsn', which I thought controlled the access to my Postgresql database.  However, I have since deleted both of those entries.  Presently, I have no entries under any of the DSN tabs with the name
'tdsn'.  I rebooted.  However, my PERL programs still work fine to query the Postgres database on the remote computer.  

 I knew nothing about regedit or the registry, so I did some reading and can now run regedit.  In it I checked:
HKEY_LOCAL_MACHINE >software > ODBC    ...and I see

  > ODBC.INI     ...which has all of my local System DSN's I use to connect to other Access database files.  Each of which looks like this:
   DSN_name > Engines > Jet

I did a search of my entire harddrive for pg_hba.conf and only found one version under c:\cygwin\usr\share\postgresql
  which I suspect has nothing to do with anything.


I could not find a pg_hba.conf file on the main server which is running the postgres database.

I have an icon on my laptop which connects to an Access database which is linked to the Postgres tables.  When I click on the properties of that file is shows:
 target: \\server\directory1\subdirectory\accessdb.mdb
 
Anyway, I would like to know how to set up an ODBC DSN to access the postgres database on the remote server.  Any insights would be appreciated.

-g




gmanpertAsked:
Who is Participating?
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:
1. Are you sure your Perl code was using DSN and not DSN-less connections?

2. pg_hba.conf file should be on your server where Postgres is running in /var/lib/pgsql/data

0
gmanpertAuthor Commented:
No, I'm not sure... so I'll have to look into how PERl could be running in DSN-less mode.

Still not finding the pg_hba.conf file althought there are about 7 pg_hba.conf.example files.

How would you go about setting up a DSN for PG on a remote computer?

-g
0
m1tk4Commented:
Go to Control Panel -> Admin Tools -> Data Sources (ODBC),

Select "File DSN" tab, Click Add

Select "PostgreSQL" click Next

Enter a DSN name (like MyNewDSN) click Next

Click "Finish"

A dialog "PostgreSQL Connection" will show up, fill in ALL fields, click "Connection"

On page 1, uncheck "Bools as Char", click "Page 2"

On Page 2, check "True is -1", OID/Show Column and Fake Index., click OK

Back in "PG connection dialog", click OK

Back in DSN List, click OK.

----

In access, create new DB,

Go to File/Get External Data/Link Tables

in "Files of Type" choose ODBC.

Select your DSN ("MyNewDSN") and click OK

"PG connection" dialog will prompt you for the password again. Enter it and click again.

Select all tables you need linked, check "Save password", click OK

----

This is it. Just tried myself.


0

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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

m1tk4Commented:
You don't need to create a DSN on the remote computer. The DSN is used ONCE when you link the tables, afterwards, you can just copy the mdb file and it should stay connected. Make sure that pg_hba.conf allows connecting from all computers you are going to be using mdb on and that authentication is set to "password", not "indent".
0
gmanpertAuthor Commented:
Dude - that totally rocks !  At least that solves one problem.

Mucho thanks.
0
m1tk4Commented:
De nada ;) What's the other one?
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.