Is there any particular way to move from MS Access to say PostgreSQL?

I have a situation where the customer has outgrown MS Access (not difficult I know) my app now comes up with errors about too many fields in the queires and also I have to be careful not to get the contraint limitation issue.

I want to move them across to PostgreSQL, I have tested my app with the Npgsql connection and it works fine although has no data in it! ;)

How do I go about "migrating" the data from the Access database to PostgreSQL?

Keeping in mind that the access database has gone through many changes and therefore the new schema created in PostgreSQL is clean, whereas the Access version has any fields that may have been removed or tables of objects that are no longer in the application.

Is it just as simple as copying the data across?

I got a trial of Navicat to play with the import wizard and it seems to be able to import the access database without too much issue (except it imports the Access GUID field as {guid {<the guid>}} which XPO only creates/stores the 36 char guid, haven't worked out how to easily "transpose" the data yet.

Any suggestions?

Regards,

MIchael Proctor 

It appears that if I import the data it seems to work, haven't imported all tables due to the length of time to import them all, don't want to spend alot of time only to find that it doesn't work.

Can anyone confirm if there is any difference going from Access to Postgre from XPO point of view? as in it seems to use the same GUID type OID fields (except in Access it is a Replication ID number type field whereas in Postgre it is Char(34) type)

Anythings I need to watch out for?

Cheers for any sugesstions

Hi Michael,

If the schema of the databases are the same, there'll be no further problems for you.

By the way: PostgreSql uses before PostreSql v8.3 (?) char(36) for guid / uuid. Since the latest version you can use uuid. The main problem is that the latest XPO providers doesn't use the new Npgsql2 provider. So you've to change the source code for this provider to use this column type.

Cool, I am working on a small app now that reads the table in from the Postgre database (as set the first time you run the app and XPO/XAF creates the schema) then reads in the tables from the Access database and then tranfers the database from one to the other.

If it works I might post it here, maybe it could help someone else in the future.

Just a quick question, is there a performance hit for XPO because of it using char(36) instead of a guid datatype?

Regards,

Michael Proctor

For now it is not possible to use guid / uuid as a column type instead of char(36), cause of the fact that XPO uses Npgsql version 1. In this version there was no implementation of uuid / guid and on that time there was no PostgreSql release with uuid support. The uuid support ist just 3 or 4 month old. And the new ADO.NET provider is just released at beginning of october.

There should be a performance hit using uuid instead of char(36) and the uuid consumes less space in the database.

 You will be happy to know that i managed to get my data imported.

I will clean up and comment my "migrain migrating application" and upload it here.

How does it work?

First you need a XAF application that is running an Access database and is giving you headaches because of the limitations on query size, constraint limitation among other things.

You add the npgsql ado.net provider from www.postgresql.com and change your connection in your app to a database within your PostgreSQL server

Run the app once, this should generate a fresh/clean database

Now what does the app do?

Provide it with the file location of your access database along with username and password should it have one
Provide your PostgreSQL Server, Database, Username and Password
When it processes it collects information on tables within your Access and PostgreSQL databases
It then checks for how many tables from Access are within your Postgre Database (this is due to each time you make changes to objects either by removing some or changing fields etc, you end up with alot of orphaned data)
It then goes through what ever tables are present in both databases
Turns off constraint triggers (as when inserting the data the constraints will fail due to the fact that the data is still being imported)
Inserts the new rows
Turns the constraint triggers back on

Seemed to work perfectly, all my data is present, XAF is working happily with all the data I had in Access within my PostgreSQL database!!!!!!!!

So stay tuned will have the application here for anyone else should they wish to move from Access to Postgre, in theory it wouldn't be too hard to change the application to make it move from any database to any database. Just need to know how the database system handles it's datatypes (ie. Access uses number like 6 to signify a numeric, 72 for a guid, 130 for varchar etc, whereas Postgre uses text like (varchar, bpchar, bytea, numeric, int4, int8, float, bool etc.)

If you do use it, drop us a line and let us know how you go... personally I think it would be good for a "mirgration" tool to be included with XAF or probably more correctly XPO, as the flexibility of "changing" databases with a couple of lines of code is brilliant.

Cheers,

Michael Proctor

Michael,

  Looking forward to seeing your code.  Thanks.

Bob

XPO/XAF Access to PostgreSQL Database Migrator

Hey this is my first time I have posted an application.

Basically the scenario is:

You have a XAF/XPO based application that is using an Access Database, then you want to use free PostgreSQL instead as it allows for larger tables, more relations and complex queries.

All you do is changed your XAF/XPO application to use the NPGSQL ADO.NET Provider and setup a database in your PostgreSQL server, run your application once (for XPO to create a blank schema, you don't even have to log in)

Run the migrator and point it to your Access file and your PostgreSQL server.

follow the commands and see how you go.

Anyone from DX have an opinion on my approach, is this an accepted practice or am I breaking the laws of XPO in just doing a data dump?

Cheers,
Michael Proctor