Thread: 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. Is there another way to import this database that I might be missing? Thanks for any help.
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. > 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
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. > > 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 >
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
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 > 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
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: > 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. > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >