[postgis-users] MS Access to PostgreSQL

[Obe, Regina]

James,
 
Depends what you are doing.  We have been using mostly Access 2003/2000,
but I presume it works just as well with Access 2007.  
 
You can migrate the tables by doing simple export table individually to
a PostgreSQL ODBC source or use Ogr2OGr to do a bulk multi-table dump
and just drop the geometry table it creates for each table.  Then just
relink the tables as linked tables.
 
You can rewrite the queries as views or just keep them as queries.
 
Reports can not be exported because they aren't really database objects
as far as PostgreSQL and SQL Server are concerned.
 
Many of our applications have MS Access as the front end and PostgreSQL
or SQL Server or combination of both as the back end and its a fairly
nice combination.  If you don't have any qualms with keeping your Access
front-end, that tends to be
the easiest route to go.
 
Below is are some articles you may find useful that covers some gotchas
and load examples
http://www.postgresonline.com/journal/index.php?/archives/24-Using-MS-Ac
cess-with-PostgreSQL.html
 
http://www.postgresonline.com/journal/index.php?/archives/31-GDAL-OGR2OG
R-for-Data-Loading.html
 
Unfortunately using PostGIS from within MS Access is a little trickier.
For simple spatial queries - e.g. distance queries that just return
attribute data, I use MS Access pass-thru queries.
 
There is also MapWindow if you need to display maps - but haven't had
much success getting it to work last I tried which was about a year or
so ago.  The PostGIS driver for it still seems very low alpha quality,
but it may be worth a try if
you need to show your maps from within MS Access.
 
http://www.mapwindow.org
 
Hope that helps,
Regina

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
James Stott
Sent: Monday, May 12, 2008 6:14 AM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] MS Access to PostgreSQL



Hi all,

I am a bit of a newcomer to PostGIS and wanted to ask a question.

Is it possible to migrate a database from MS Access into PostgreSQL? Can
table structures, queries, rules, indexes and reports be moved over
easily or is it a case of having to recreate everything by hand?

I am using Access 2007.

We would then like to extend the attribute data by adding spatial
information to the records as well.

Many thanks,

James

This Electronic Transmission is intended only for the attention of the
addressee. It may contain privileged and confidential information. If
you are not the person for whom it is intended, you must not copy,
distribute or take any action in reliance on it. If you have received
this electronic transmission in error please notify us immediately by
telephone, delete the transmission and destroy any hard copies. 

Nicholas Pearson Associates has used all reasonable efforts to ensure
that this message and any attachments are free from viruses. 

Nicholas Pearson Associates 

Environmental Planners - Landscape Architects - Ecologists 

30 Brock Street, Bath, BA1 2LN 

Tel: +44 (0) 1225 445548, Fax: +44 (0) 1225 312387 

www.npaconsult.co.uk <http://www.npaconsult.co.uk/> 



-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080512/8abb3474/attachment.html>