Thread: Porting from MS Access 2007 to PostgreSQL
Hi,
Disclaimer: Not a DBA, nor I am not a DB guy, so please excuse any ignorance in the below.
1. Background
We have a MS Access 2003 database that we are using to manage registration and workshop/accommodation allocation for a conference. The database isn't particularly complicated (around 20 tables or so), nor is the dataset large (largest table has around 13,000 records, most of the others have around 5000 or so records.)
The structure is a bit convoluted though (mostly for historical reasons), and most of the queries we use are quite join-heavy. Some of these seem to take longer than you'd expect them to, for such a small dataset.
The database design is a bit quirky - there's heavy use of varchars for many things, stacks of NULLs everywhere, and not really much use of validation/constraints.
2. MS Access to MySQL
Recently, this was ported over from a pure-Access database to a Access front-end over a MySQL backend on somebody's desktop, mostly to provide multi-user capabilities. I've been told automated tools were used for this, so I assume we weren't using too many MySQL-specific features.
3. MySQL to Postgres
I recently looked at moving this over to a PostgreSQL in a proper server. Postgres was chosen mainly for convenience since we already have a Postgres instance setup there (used for some Django projects).
I tried a MySQL to PostgreSQL conversion using Enterprise DB's Migration Studio, hit an issue with two of the tables complaining about CLOB's...
4. MS Access to Postgres
Anyhow, somebody else suggested it might be better to just go straight from the original MS Access database to PostgreSQL.
My first question is, what is the current recommended procedure for this?
I saw this page from 2001:
and the tool referenced there appears to lead to a 404 page.
I also saw the tools referenced there:
and most of them appear quite dated, from the MS Access 97 era.
Has anybody had any experiencing doing a similar port (Access 2007 to Postgres) recently, what tools did you use, were there any gotchas you hit etc? Or just any general advice at all here?
5. Postgres Specific Features
Once the port is done, I assume they'll probably be some work involved to clean it up, and leverage on some of Postgres's features. In particular, I'm hoping to at least get some decent data validations/constraints in.
The issue is we still need to maintain compatibility, where we can, with the Access frontend.
That and hopefully clean up some of the queries a bit, and try and figure out why some forms in Access are taking so long to load.
Any particularly good books here that you'd recommend? I saw some Postgres-specific books on Amazon, but none seem to have particularly good reviews (or were rather simplistic). Recommendations?
Cheers,
Victor
On Tue, Nov 9, 2010 at 3:22 PM, Victor Hooi <victorhooi@yahoo.com> wrote:
4. MS Access to Postgres
Hmm have you tried Kettle (Pentaho) http://kettle.pentaho.com/
Any particularly good books here that you'd recommend?
Hi Victor Le 9/11/2010 5:22, Victor Hooi a écrit : > Has anybody had any experiencing doing a similar port (Access 2007 to > Postgres) recently, what tools did you use, were there any gotchas you hit > etc? Or just any general advice at all here? We recently migrated from MSAccess 2000 to PostgreSQL. We used this great script : http://www.rot13.org/~dpavlin/projects/sql/exportSQL3.txt (I just realized it is mentionned in your second link) We had to make some changes in the script. I think it was written for on old PostgreSQL version, so some syntax did not come out right. But the script is simple and works well. It won't transfer constraints I think, but it will create all the DDL queries and load all you data in PostgreSQL. Name conversion works fine too. > *5. Postgres Specific Features* > > The issue is we still need to maintain compatibility, where we can, with the > Access frontend. I can't really give you any advice about this, but we kept using Access as our Frontend and everything runs fine. You'll just have to make sure that you have read about the 'boolean <-> integer' problem. This article is a nice start I think : http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html Good luck ! Arnaud
On Mon, Nov 8, 2010 at 11:39 PM, Arnaud Lesauvage <arnaud.listes@codata.eu> wrote: > I can't really give you any advice about this, but we kept using Access as > our Frontend and everything runs fine. > You'll just have to make sure that you have read about the 'boolean <-> > integer' problem. This article is a nice start I think : > http://www.postgresonline.com/journal/archives/24-Using-MS-Access-with-PostgreSQL.html I've played with mapping access [yes/no] datatype to postgresql BOOLEANs for a while. And after all of that *fun*, I've since started mapping postgresql's INTEGER to access's [yes/no] datatype. Life is so much better now because. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
On Monday 08 November 2010 8:22:51 pm Victor Hooi wrote: > Hi, > > Disclaimer: Not a DBA, nor I am not a DB guy, so please excuse any > ignorance in the below. > > *4. MS Access to Postgres* > > Anyhow, somebody else suggested it might be better to just go straight from > the original MS Access database to PostgreSQL. > > My first question is, what is the current recommended procedure for this? > One way I have done this is to use the Make Table Query in Access to create a table in Postgres from one in Access. This assumes you have an ODBC connection set up to Postgres. > Cheers, > Victor -- Adrian Klaver adrian.klaver@gmail.com