hello everyone,
i want to migrate my database which is in MDB format (Microsoft Accces Database) to the postgresql database in linux, i have the application ready which will perform the task, what kind of system settings, configruations shall i do to accomplish the task
waiting eagerly for the reply
thanks in advance

hello everyone,
I am stuck up due to a problem
How do i connect to the MDB file in linux? through a java application
what drivers shall i install to accomplish the task

waiting eagerly for the reply
thanks in advance

Is the file stored on your linux system or is it accessible from a server? I've had to do some research on this same topic for uni. We have to access a mdb file on a windows machine from our Linux server. Check up on "mdbtools" to see if it suits your needs.

I assume you are familiar with postgreSQL that you have the daemon running and created an empty database etc etc.

The 1st step would be to take your access create table queries and transscribe them to postgresql as postgresql uses different field sizes (look in manual under data types)
Example:
CREATE TABLE "trans" (
"no" bigint DEFAULT nextval('"trans_no_seq"'::text) NOT NULL,
"shift_no" bigint,
"stamp" timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone,
"type" text,
"void" boolean DEFAULT 'f'::bool,
Constraint "trans_pkey" Primary Key ("no")
);


then create all your tables with their respective relations.

Backup your structure into a dump file.

Then migrate your data:
copy or export the data for each table and create a SQL file (Do NOT use Windows Notepad; as it screws up the lineends and make them unreadable in Linux); using the following format:

COPY "tablename" FROM stdin;
DataforField1[TAB]DataforField2[TAB]DataforField3[TAB]DataforField4 etc
\.
Example:
COPY "trans" FROM stdin;
108008 1775 2004-11-01 06:13:40.598339 Exchange f
108009 1775 2004-11-01 06:19:33.724516 Exchange f
108010 1775 2004-11-01 06:26:18.479672 Cash f
108011 1775 2004-11-01 06:26:36.295438 Cash f
\.

Keep in mind:
Date Time fields can easely be confused by the databse use yyyy-mm-dd
change your Yes/no True/False fields into t for true and f for talse (-1 and 0 will not work either)
postgre allows you to setthe number for an autonumeric field, so do not worry keeping your consecutives.

Upload the files to your server(you can use Secure File Transfer) to a place where the postgres user has access (iE /tmp)

then do:
bash-2.05b$ psql [database] -f /tmp/[Filename]
once for each file

Carefully watch the output for error messages and do the necesary changes to tables or data if needed.

in the worst case if something bad happens just drop the database and restore it from the dumpfile.

i have the MDB file on the linux machine itself

The 2 identical threads have been merged. In future, please do not post the same question in more than one place.

Oops
MDB files are MS proprietary (I don't know if an open source product has figured out how to read or export its data.

I would recommend you either copy it back to Win or open it through Windows File sharing. Open it in access.

Then follow the steps I posted before.
I also hope that the MDB only holds data and the whole application is not developed on it like forms and stuff.

Good news for access users!

The upcoming version 2 of openoffice.org will have a database similar to Access called Base.

It will have support for opening Access mdbs

neerajchaudhari, you might be able to open the mdb files with the beta.

Hello Neeraj,
I am not sure how I did this a long time back but if you have access to a MS machine, just dump all the tables to pipe delimited file(s) (from the MS machine) and read them back into postgreSQL (on Linux) provided the tables have been defined as explained in one of the earlier replies.

I remember it wasn't very difficult but wish it was much easier.

-Prasad