Connecting MS Access and CONCEPT via ODBC

pridefulauburnData Management

Dec 16, 2012 (4 years and 7 months ago)

133 views

Connecting MS Access and CONCEPT via ODBC
Introduction
This document describes how to enable data sharing via ODBC between two network-connected
computers, one running the CONCEPT model and the other running Microsoft Access.
For the sake of simplicity, “$PGDATA” will be used throughout this document to refer to the
directory where PostgreSQL data is stored. If you've not set the PGDATA environment variable,
substitute the path to that directory wherever you see “$PGDATA”.
On the CONCEPT side
If postmaster is running, stop it by typing
pg_ctl -D $PGDATA stop
as the PostgreSQL superuser.
Open the file $PGDATA/pg_hba.conf for editing. This file controls what PostgreSQL users, from
which hosts, and to which databases, connections will be accepted. The configuration data is at the
end of the file, after some basic documentation. A full discussion of all that can be done here is
beyond the scope of this document. Instead, consider the examples provided by the last 3 pairs of
lines in the sample below. Alternatively, skip to the next to last paragraph of this section for the
simplest, least secure configuration.
Versions of PostgreSQL earlier than 8.0 use an IP/netmask scheme to control host access. In
version 8.0,this information is combined in the CIDR-ADDRESS field, where an IP address followed
by a /32 means all four parts of the client's IP address must match the address before the slash.
A /24 means the first 3 parts must match and so on until /0 means nothing needs to match.
Note that the user accounts that matter here are not system user accounts, but the accounts
created within PostgreSQL (see
http://www.postgresql.org/docs/8.0/static/sql-createuser.html
)
.
# User guest from anywhere connection
- In this example, the PostgreSQL user “guest” can
connect to all databases from any computer. This is not as permissive as it sounds since users are
still subject to the permissions maintained within the database. Unless you create a PostgreSQL
user and grant him permissions (see
http://www.postgresql.org/docs/8.0/static/sql-grant.html
),
even if he is allowed connection privileges in pg_hba..conf, he won't be allowed to see or change
anything in the database.
The guest user approach works great for lots of typical database applications, but you probably
don't want to do it with CONCEPT. Unlike a typical database application, the model routinely drops
and re-creates a lot of tables. Permissions on tables are dropped when the tables are, and are not
automatically re-granted when the tables are re-created.
# Allow user conceptuser from an IP address beginning with 192.168.1
- This would allow the
PostgreSQL user named “conceptuser” to connect to all databases from a computer with an IP
address starting with 192.168.1. Assuming conceptuser is the name of the account you use to run
CONCEPT, this is probably a more useful approach. As conceptuser (re-)creates tables while
running the model, he automatically receives access privileges to the data in them.
# Allow conceptuser access to project beta3 from a specific IP address
- This allows the user
“conceptuser” access to the specific database (or CONCEPT project) beta3, and only from the
computer with IP address 192.168.1.189.
Note that all these examples use authentication method “trust”. The various authentication
mechanisms that are available in PostgreSQL are also beyond the scope of this document, except to
note you should avoid using anything interactive for the account you will use to run CONCEPT.
You'll end up being frequently prompted to provide a password, and each time processing will halt
until you do.
Once you figure out what gives an acceptable balance between usability and security, add the lines
to $PGDATA/pg_hba.conf that implement what you want. For setting up and testing purposes, you
might consider the following totally unsecured configuration line:
host all conceptuser 192.168.1.1/0 trust
Once you've saved your changes, restart postmaster with the
-i

option so it will listen for
connections from remote hosts. Type
pg_ctl -D $PGDATA -l ~/pg_log.txt -o -i start
. See the
pg_ctl man page for details.
On the MS Access side
The instructions below provide menu selection sequences, etc., based on Windows 2000 and Access
2000. Some adjustment may be necessary if you're using other versions.
Obtain and install the
PostgreSQL
ODBC driver. You probably want a version of the driver that self-
installs, so select an appropriate version from
http://www.postgresql.org/ftp/odbc/versions/msi
.
I've been able to connect to version 8.0.1 PostgreSQL databases using psqlodbc versions 7.03.0200
and 8.00.0101. Download the file and unzip it. Running the unzipped file installs the driver.
Once the ODBC driver is installed, you need to create the Windows data source. This is where you
specify the connection information that Windows will use to communicate with a specific
PostgreSQL database.
Click
Start|Settings|Control Panel|
Administrative Tools
|Data Sources (ODBC)
. Click the
SystemDSN
tab in the resulting dialog, then the
Add
button. Scroll to the bottom of the window
and you should see something like the following:
Double-click the
PostgreSQL
entry (added by the psqlodbc driver) to see the following dialog:
This is where the connection to a CONCEPT project/PostgreSQL database is built. You'll need to fill
out one of these for each CONCEPT project you want to be able to connect to via MS Access.
Enter what you like in the Data Source and Description fields, but make these entries meaningful.
They are what you'll see from within MS Access when connecting to PostgreSQL data.
In the Database field, put the name of the CONCEPT Project/PostgreSQL database for this data
source connection. In the Server field put the IP address of the computer where CONCEPT runs. If
you have a nameserver or hosts file so your Windows computer knows the host name of the
CONCEPT computer, you could use host name here instead. In the User Name field, put the name
of the PostgreSQL account you want to use once you're connected (the one whose data access
permissions you want). These 3 entries will be checked against the rules in your pg_hba.conf file.
Leave the Port at the default of 5432, unless you configured PostgreSQL to use a different port.
If you're using the “trust” authentication method, leave the Password field blank. Click
Save
and
OK
to close out the ODBC configuration screen.
Open MS Access and create a new database. Click
File|Get External Data|Link Tables...
In the
resulting dialog, in the
Files of Type
dropdown at the bottom of the dialog, scroll all the way down
to
ODBC databases ()
and click it. In the resulting dialog, click the
Machine Data Source
tab,
and you should see the following:
Notice the line with the Data Source Name and Description field entries from the connection
configuration step. Double-click on the Data Source Name you created there (
PostgreSQL_beta3
in this example
). If all has gone well, you'll see the following dialog with all the tables in the
PostgreSQL database to which you're connecting:
In the dialog above, select which of the tables in the PostgreSQL database you want linked into
your MS Access database, then click OK. For each PostgreSQL table where Access doesn't
recognize a primary key, you'll be prompted for a unique
identifier
. It's safest to click the
Cancel
button. This just tells Access not to worry about a key - you'll still get the link to the table. After
appropriately many clicks of the Cancel button, you should see something like this:
You've now created an Access database with links to these tables in the
PostgreSQL
database. You
can move and rename this .mdb file as desired, or change the names of the tables within the
Access database, without breaking the links. If you create new files in CONCEPT, new links will
not
be automatically created, but you can add them by the same
File|Get External Data|Link
Tables...
process you just completed. You can also repeat the
File|Get External Data|Link
Tables...
step against a different data source to get data from different
PostgreSQL
databases/CONCEPT scenarios in the same Access database.
You can query data in these tables like any other Access tables, subject to your authorizations as
the PostgreSQL user you specified when creating the data source.
You can also use this connection to load MS Access data into PostgreSQL. I haven't been able to
get an Access “make table” query to create a new table in PostgreSQL. Once the tables have been
created in
PostgreSQL,
however, you can populate them with an Access “append” query. If the
column names in the
corresponding
tables are identical, Access will automatically match the source
and target columns.
Troubleshooting
Probably the first thing to check in case of problems is network connectivity. Make sure you can
ping back and forth between the computers involved. Try traceroute (
tracert <target-IP>
on
your Windows system) to see through which computers your network communications must pass.
Firewalls on any of these might need adjustment.
Even after the computers can talk to one another, getting all the necessary parts to play nice
together can be tricky. Fortunately, the error messages and the entries into the logfiles are usually
pretty helpful.
If you typed
pg_ctl -D $PGDATA -l ~/pg_log.txt

start
to start postmaster, the logfile is in your
home directory named pg_log.txt. To turn on logging on the Windows side, click
Start|Settings|
Control Panel|Administrative Tools|Data Sources (ODBC)
. Click the
SystemDSN
tab, and
this time you should see the connection you created earlier. Click on it, then the
Configure...
button. Behind both the
Datasource
and
Global
buttons, are checkboxes for
Commlog
and
Mylog
(with descriptions of where these files will be written). Turn them all on until you get things
working. Turn them all off afterward, otherwise your client machine will write to disk a log of every
record exchanged, which makes for truly horrible performance.
Questions? Problems?
If you have questions, or suggestions for improving this document, my e-mail is
edicks-at-
michigan-dot-gov
. For questions or tips that are likely to be of general interest, don't forget the
CONCEPT listserve.