How do I connect to a PostgreSQL database using MS Access 2000 ?

Posted on 2004-08-17
Last Modified: 2009-12-16
How do I connect to a PostgreSQL database that is resided on a
Linux computer using Microsoft Access 2000 on Windows ?

Essentially, I am using Access 2000 as a front-end and PostgreSQL as a back-end.
I am using Access 2000 to facilitate complex query using the graphical method.

Please show it to me step by step how to do it. I think I need to use
ODBC that is built-in to MS Access 2000.

Location of PostgreSQL database:

Name of PostgreSQL database:
Question by:lin100
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4

Accepted Solution

rjkimble earned 50 total points
ID: 11825260
You have to configure the PostgreSQL server on the Linux machine to allow connections from a remote computer using TCP/IP. Have you done so already? In particular, you have to modify the default pg_hba.conf and postgresql.conf files that are installed with the server software.

Once that part is working, you have to create an ODBC data source that connects to the target database. You can download the ODBC driver here:

Unzip that and install the file that's included. Once you have the data source created, you should be able to use Access to query your database. There's a good article at this URL:

However, the links to the PostgreSQL site seem to be outdated. If you need more help, please post some more information about PostgreSQL configuration -- in particular, post copies of pg_hba.conf and postgresql.conf. How familiar are you with PostgreSQL?

Author Comment

ID: 11830592
Hi rjkimble. Thank you for your help. Below are the two files in my
Linux computer.

Linux computer IP:
Windows computer IP:

Windows is obtaining a DHCP address from the Linux computer
Please list the configuration for the two files so that I could just cut and
paste to it and connect to the database.
vi /var/postgresql/pg_hba.conf


    local      all                                                             trust
    host      all        trust

vi /var/postgresql/postgresql.conf

Everything is commented out.

Expert Comment

ID: 11832664
If you want to be able to connect from any machine on your network, add this line to pg_hba.conf:

    host      all          trust

I suggest you read up on PostgreSQL security in order to understand the various security issues involved. For more info, look at:

To enable TCP/IP, add this line to postgresql.conf:

tcpip_socket = true

Be sure to restart your PostgreSQL server after making these changes. If you have any more questions, please post them here.
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 11833104
Hi rjkimble.

I downloaded the ODBC driver at the site below

If I launches Access 2000, what quick test can I perform to see if the ODBC driver is working properly ?

You said
"Once you have the data source created, you should be able to use Access to query your database."

How do you create the dat source ? Is it just create some blank tables and then tried to connect that blank table to PostGres ?

Expert Comment

ID: 11834597
Depending on what Windows version you're running, there should be a control panel icon for data sources. That's where you define ODBC data sources. Make a "system" source if you can. That way all the users on your machine will be able to see it. I hardly ever run Windows, however. Here is a link to a Microsoft article on how to set up an ODBC data source:;en-us;305599

I think the procedure is similar no matter what version of Windows you're running.

Author Comment

ID: 11835263
Hi rjkimble. Here is what I have done.

1) in file pg_hba.conf:
    host      all          trust

2) In file postgresql.conf:
    tcpip_socket = true

3) I did setup a "system" source  in ODBC

    Database: apache (This is the name my database)
    User Name: apache (must login as apache user to use PostGres)
    Port: 5432
    Password: abcd (Is this the password to gain access to the database ? or
                              or is it the password for root ? )

4) Now that I am in Access 2000, how do I access this database ? I do not see anything in Access

Expert Comment

ID: 11835463
3) You must use whatever password the apache user uses to connect.

4) I don't know. I don't use Access, and I don't have it installed anywhere so that I could check it out. I'm sure there must explanations somewhere on how to connect to an ODBC data source using Access. Maybe you should post a question in the MS Access topic area:

Author Comment

ID: 11835516
Thank you  rjkimble  for your help. You got an A!!!!

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Ora2pg 4 95
How can I pick this with sql? 5 241
New to postgre, can't call a function properly 3 120
postgreSQL - query problem 3 92
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: ( 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.
In a recent question ( here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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