Solved

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

Posted on 2004-08-17
8
447 Views
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:
     /var/postgresql

Name of PostgreSQL database:
     apache_database
0
Comment
Question by:lin100
  • 4
  • 4
8 Comments
 
LVL 9

Accepted Solution

by:
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:

ftp://ftp13.us.postgresql.org/mirror/postgresql/odbc/versions/msi/psqlodbc-07_03_0200.zip

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:

http://www.developer.com/open/article.php/631251

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?
0
 

Author Comment

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

Linux computer IP:       192.168.55.1
Windows computer IP: 192.168.55.245

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

# TYPE   DATABASE  IP_ADDRESS    MASK          AUTH_TYPE  AUTH_ARGUMENT

    local      all                                                             trust
    host      all              127.0.0.1     255.255.255.255    trust
===============================================

vi /var/postgresql/postgresql.conf

Everything is commented out.
0
 
LVL 9

Expert Comment

by:rjkimble
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              192.168.55.1       255.255.255.0      trust

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

http://www.postgresql.org/docs/7.4/static/client-authentication.html

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.
0
 

Author Comment

by:lin100
ID: 11833104
Hi rjkimble.

I downloaded the ODBC driver at the site below
ftp://ftp13.us.postgresql.org/mirror/postgresql/odbc/versions/msi/psqlodbc-07_03_0200.zip

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 ?
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 9

Expert Comment

by:rjkimble
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:

http://support.microsoft.com/default.aspx?scid=kb;en-us;305599

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

Author Comment

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

1) in file pg_hba.conf:
    host      all              192.168.55.1       255.255.255.0      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)
    Server: 192.168.55.1
    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
 
0
 
LVL 9

Expert Comment

by:rjkimble
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: http://www.experts-exchange.com/Databases/MS_Access/
0
 

Author Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

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.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now