Importing Access 97 Database into 7.1.3

You have to use Access to export into PGSQL 7.1.3, after defining an ODBC
alias. So you can import, export and have links with PGSQL.

Michel BELLON
LCIE - Informatique appliqu�e
33 (0)1 40 95 60 35

I had the same problems.

Exporting a table using access means access will analyse your table
and try to figure out the best fitting sql. In my case it also left
out primary key definitions and indexes.

Try this:

Create the table-definitions in postgresql. Or export only the
definitions to postgresql and then edit them so that they are 100%
okay. You can leave most of the indexes out until you've got all your
data imported of course.

Then, in your access-database, create ODBC-links to your newly created
[still empty] tables. Since they have the same name as your
access-tables, you will get somthing like
MyFirsttable [the access-table]
MyFirsttable1 [the odbc-link to pg]
MySecondtable
MySecondtable1

Then create append-queries in the access-queries-windows.
The SQL-query should be something like SELECT INTO Myfirsttable1
SELECT * from MyFirsttable

Do that for all your tables. If you want you can create a macro that
executes them all, one by one. Start the macro.... Wait/Go home....
And Presto!

Arian.

On Thu, 25 Oct 2001 04:08:53 +0000 (UTC), pgsql@happyhacker.com
("Aaron Rouse") wrote:

I have the need to import an Access 97 database into PGSQL 7.1.3 It has
been a long time since I had to do this and back when I did, I just used the
PGAdmin tool which worked fine at the time with the exception of not
converting AutoNumber data types to Serial, but to Int datatypes. I do not
see a way to import Access DBs with the new PGAdmin II tool and so far the
only way I am finding to import the database is to import each table one by
one via a ODBC connection and the export feature in Access. This method
still gives the same problem with the Autonumber datatype being converted to
Int and I am assuming it leaves out any indexes or default values as well.
Is there another way to import this database that I might be missing?

Thanks for any help.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

_______________________
Arian Prins
_______________________
prinsarian
apetale
zonnet
dot
nl

Thanks, I was hoping there was another way than this or that perhaps I was
going about it the wrong way in Access. Since as I mentioned in my first
post, it does seem rather cumbersome to do each table, one by one. I did
last night try using the SQL2k Ent manager and it allowed me to do all
tables from Access into PGSQL. However it did error out on a couple of the
fields due to length of the data. The problem with the datatypes not
migrating over will still have to be resolved, unfortunately I am guessing
this has to be done manually since I saw now SQL of altering a datatype. I
also noticed that the old PGAdmin would still connect to the 7.1.3 DB and
its migration wizard would open, but I did not try it beyond that since it
would have just put me in the same ballpark that the SQL2k DTS did.

----- Original Message -----
From: "BELLON Michel" <Michel.Bellon@lcie.fr>
To: "Aaron Rouse" <pgsql@happyhacker.com>; <pgsql-general@postgresql.org>
Sent: Thursday, October 25, 2001 2:45 AM
Subject: RE: [GENERAL] Importing Access 97 Database into 7.1.3

You have to use Access to export into PGSQL 7.1.3, after defining an ODBC
alias. So you can import, export and have links with PGSQL.

Michel BELLON
LCIE - Informatique appliqu�e
33 (0)1 40 95 60 35

-----Message d'origine-----
De: Aaron Rouse [SMTP:pgsql@happyhacker.com]
Date: jeudi 25 octobre 2001 04:58
�: PostgreSQL
Objet: [GENERAL] Importing Access 97 Database into 7.1.3

I have the need to import an Access 97 database into PGSQL 7.1.3 It
has
been a long time since I had to do this and back when I did, I just used
the
PGAdmin tool which worked fine at the time with the exception of not
converting AutoNumber data types to Serial, but to Int datatypes. I do
not
see a way to import Access DBs with the new PGAdmin II tool and so far

the

only way I am finding to import the database is to import each table one
by
one via a ODBC connection and the export feature in Access. This method
still gives the same problem with the Autonumber datatype being

converted

to
Int and I am assuming it leaves out any indexes or default values as

well.

Aaron,

I spent a bit of time developing an Access app that may help called pgUpt
(http://dspace.dial.pipex.com/boylesa/pgupt/pgupt.shtml) that may help with
some of this. It aims to upsize all tables, provide an option to enforce RI
using triggers, creates indexes ... The tool was primarily designed for v 6
and some of the RI Triggers are now better implemented using DRI as Foreign
Key constraints.

I've not uploaded any updates to it for ages but I could send you an up to
date version if it if it would be of any use (the up to date version
includes code to autogenerate the Foreign Key constraints).

Regards

Steve Boyle
boylesa@dial.pipex.com

""Aaron Rouse"" <pgsql@happyhacker.com> wrote in message
news:030301c15d00$cedfa130$0301a8c0@win2kpro...

I have the need to import an Access 97 database into PGSQL 7.1.3 It

has

been a long time since I had to do this and back when I did, I just used

the

PGAdmin tool which worked fine at the time with the exception of not
converting AutoNumber data types to Serial, but to Int datatypes. I do

not

see a way to import Access DBs with the new PGAdmin II tool and so far the
only way I am finding to import the database is to import each table one

by

one via a ODBC connection and the export feature in Access. This method
still gives the same problem with the Autonumber datatype being converted

to

How can I import from access to pgsql?, import, not export...

I can export very well, but can I import?

Saludos,

Basil Briceno
~~~~~~~~~~~~~
bbh@wdev.org
icq 42077316
+52 81942180
~~~~~~~~~~~~~
"Man is free at the moment he wishes to be".
--Voltaire

On Thu, 25 Oct 2001, BELLON Michel wrote: