ODBC DSN setup

Posted on 2006-04-19
Medium Priority
Last Modified: 2012-05-05
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.


Question by:gmanpert
  • 4
  • 2
LVL 15

Expert Comment

ID: 16490630
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


Author Comment

ID: 16490829
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?

LVL 15

Accepted Solution

m1tk4 earned 2000 total points
ID: 16491327
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.

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

LVL 15

Expert Comment

ID: 16491354
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".

Author Comment

ID: 16492113
Dude - that totally rocks !  At least that solves one problem.

Mucho thanks.
LVL 15

Expert Comment

ID: 16492260
De nada ;) What's the other one?

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Suggested Courses
Course of the Month9 days, 8 hours left to enroll

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question