pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql vs. Access97, TransferDatabase
7+ messages / 6 participants
[nested] [flat]
* Postgresql vs. Access97, TransferDatabase
@ 1998-11-24 18:12 Gilley, Charles H. <Charles.Gilley@glenayre.com>
1998-11-24 19:14 ` Re: [INTERFACES] Postgresql vs. Access97, TransferDatabase Byron Nikolaidis <byronn@insightdist.com>
1998-11-24 19:29 ` [INTERFACES] Postgresql vs. Access97, TransferDatabase Patricia Rodriguez-Tome <tome@ebi.ac.uk>
1998-11-25 23:01 ` Re: [INTERFACES] Postgresql vs. Access97, TransferDatabase David Hartwig <daybee@bellatlantic.net>
0 siblings, 3 replies; 7+ messages in thread
From: Gilley, Charles H. @ 1998-11-24 18:12 UTC (permalink / raw)
To: pgsql-interfaces
I'm trying to move an existing access database to Postgres.
I've tried cut/paste, export/import, all without success. Trying the
transfer database approach, I receive the following error:
Error creating the table; ERROR type name lookup of lo failed (#17).
From the odbc log file, Access is trying to create a series of fields that
I do not know where they are coming from:
..."Gen_Note" int4,"s_Generation" int4,"s_GUID" bytea,"s_Lineage" lo,...
Maybe someone has dealt with this issue before.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] Postgresql vs. Access97, TransferDatabase
1998-11-24 18:12 Postgresql vs. Access97, TransferDatabase Gilley, Charles H. <Charles.Gilley@glenayre.com>
@ 1998-11-24 19:14 ` Byron Nikolaidis <byronn@insightdist.com>
2 siblings, 0 replies; 7+ messages in thread
From: Byron Nikolaidis @ 1998-11-24 19:14 UTC (permalink / raw)
To: Gilley, Charles H. <Charles.Gilley@glenayre.com>; +Cc: pgsql-interfaces
Gilley, Charles H. wrote:
> I'm trying to move an existing access database to Postgres.
> I've tried cut/paste, export/import, all without success. Trying the
> transfer database approach, I receive the following error:
>
> Error creating the table; ERROR type name lookup of lo failed (#17).
>
>
You need to create the "lo" datatype in the postgres database. See the Faq at
our website for more info.http://www.insightdist.com/psqlodbc
Byron
^ permalink raw reply [nested|flat] 7+ messages in thread
* [INTERFACES] Postgresql vs. Access97, TransferDatabase
1998-11-24 18:12 Postgresql vs. Access97, TransferDatabase Gilley, Charles H. <Charles.Gilley@glenayre.com>
@ 1998-11-24 19:29 ` Patricia Rodriguez-Tome <tome@ebi.ac.uk>
2 siblings, 0 replies; 7+ messages in thread
From: Patricia Rodriguez-Tome @ 1998-11-24 19:29 UTC (permalink / raw)
To: pgsql-interfaces
Hello everybody,
Thanks to all the previous mails (questions + answers) that went these lasts
days on this list I had (almost) no problems in installing the ODBC driver.
Now I want to transfert also an Access db to postgres.
I am doing it manually - ie table per table using the Save/As export in the
file menu
It is quite tedious. Has anybody found a faster way of doing it?
I am extremely unfamiliar with PCs and would like to keep my ignorance as
intact as I can ;-)
The final purpose is to build a CORBA server on top of the database, and I
rather do that on Unix + PostgresSQL!
Thanks for any ideas
regards
Pat R.Tome
--
=======================================================================
Dr. Patricia Rodriguez-Tome | URL: http://www.ebi.ac.uk
R & D Coordinator
The EMBL Outstation, Hinxton - The European Bioinformatics Institute
Wellcome Trust genome Campus, Hinxton | Tel: +44 (0)1223 494 409
Cambridge CB10 1SD, UK | Fax: +44 (0)1223 494 468
========================================================================
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] Postgresql vs. Access97, TransferDatabase
1998-11-24 18:12 Postgresql vs. Access97, TransferDatabase Gilley, Charles H. <Charles.Gilley@glenayre.com>
@ 1998-11-25 23:01 ` David Hartwig <daybee@bellatlantic.net>
2 siblings, 0 replies; 7+ messages in thread
From: David Hartwig @ 1998-11-25 23:01 UTC (permalink / raw)
To: Gilley, Charles H. <Charles.Gilley@glenayre.com>; +Cc: pgsql-interfaces
"Gilley, Charles H." wrote:
> I'm trying to move an existing access database to Postgres.
> I've tried cut/paste, export/import, all without success. Trying the
> transfer database approach, I receive the following error:
>
> Error creating the table; ERROR type name lookup of lo failed (#17).
>
> >From the odbc log file, Access is trying to create a series of fields that
> I do not know where they are coming from:
>
> ..."Gen_Note" int4,"s_Generation" int4,"s_GUID" bytea,"s_Lineage" lo,...
>
> Maybe someone has dealt with this issue before.
It looks like you have an OLE column (large object) in your table. Follow the
instruction contained in our FAQ for creating the "lo" data type on the server
side: http://www.insightdist.com/psqlodbc
^ permalink raw reply [nested|flat] 7+ messages in thread
* re: [INTERFACES] Postgresql vs. Access97, TransferDatabase
@ 1998-11-25 20:55 Gilley, Charles H. <Charles.Gilley@glenayre.com>
1998-11-26 04:52 ` Re: [INTERFACES] Postgresql vs. Access97, TransferDatabase Michael Meskes <meskes@usa.net>
0 siblings, 1 reply; 7+ messages in thread
From: Gilley, Charles H. @ 1998-11-25 20:55 UTC (permalink / raw)
To: pgsql-interfaces
Okay, to provide some helpful feedback to others.... I readily admit that
I am still a novice at this, so feel free to chime in and correct any
misunderstandings that I may have.
First, I've been trying to transfer a large dataset from access to postgres
using a variety of methods:
0) copy command - never could get this to work. The copy command requires
postgres superuser access for backend processing, which I did not have.
There is an alleged \copy command that I could not work either - it kept
processing it as a connect request.
1) Cut and paste from access table to linked table:
This is very slow and tedious, as one writer indicates. Further, it is
quite sensitive
to data type and field name issues. Just because Access allows you to
do something doesn't mean postgres or odbc will.
2) Macro to TransferDatabase - if your table will map PERFECTLY 1:1 to the
postgres table, this may work.
3) Export table - I believe this is the same as cutting/pasting except that
it
attempts to create the table on the backend as well. I did not have
much
success with this either, due to the hidden fields associated with
replication.
4) SQL - this is what I finally used to update the postgres database. Once
I had
the tables defined in an acceptable manner, I created an update query:
INSERT INTO tblproducts1
SELECT productid AS productid, ... BarCode10 AS BarCode10
FROM tblproducts
The simplest form of this insert is:
INSERT INTO tblproducts1 SELECT * FROM tblproducts;
but this only works if your tables map PERFECTLY. using the
field AS field format keeps Access and the ODBC from thinking too
much. I also gave up trying to use the drag/drop gui in the query
builder.
5) See item 0. Because I could not get the privs or otherwise of the copy
command
to operate properly, I considered creating a utility to generate a
series of insert into
commands, parsing a comma delimited file as a data source. I believe I
will still
craft this (since I have more data to load). This would allow me to zip
a text file
up and ftp it to my server location. If I get this working, I'll be
happy to provide it
to anyone who wants it (maybe post the source on a postgres site?).
Beware: comma delimited files produced by Access simply will not be
imported
by postgres. For example, a null text field is '' (two single quotes).
Access does not
generate this.
Suggestions:
+ Read the postodbc faq. one of my problems was a missing type - I just
needed
to create it.
+ Watch out for databases that have been replicated. I have yet to locate
the
extra fields and remove them. These will give you a large amount of
grief.
+ Watch out for 'bad' characters in field names.
+ Use the psqlodbc.log file judiciously.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] Postgresql vs. Access97, TransferDatabase
1998-11-25 20:55 re: [INTERFACES] Postgresql vs. Access97, TransferDatabase Gilley, Charles H. <Charles.Gilley@glenayre.com>
@ 1998-11-26 04:52 ` Michael Meskes <meskes@usa.net>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Meskes @ 1998-11-26 04:52 UTC (permalink / raw)
To: pgsql-interfaces
On Wed, Nov 25, 1998 at 03:55:37PM -0500, Gilley, Charles H. wrote:
> Okay, to provide some helpful feedback to others.... I readily admit that
> I am still a novice at this, so feel free to chime in and correct any
> misunderstandings that I may have.
>
> First, I've been trying to transfer a large dataset from access to postgres
> using a variety of methods:
>
> ...
> 1) Cut and paste from access table to linked table:
Don't do this. It will run forever. Granted I never tried with postgres but
I tried with Oracle once some months ago and I'm pretty sure it will still
run hadn't I interrupted it. :-)
> 3) Export table - I believe this is the same as cutting/pasting except that
> it
No, it is not the same. It is way faster.
Michael
--
Dr. Michael Meskes, Manager of the Western Branch Office, Datenrevision GmbH
work: Cuxhavener Str. 36, D-21149 Hamburg, Michael.Meskes@datenrevision.de
home: Th.-Heuss-Str. 61, D-41812 Erkelenz, Michael.Meskes@usa.net
Go SF49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [INTERFACES] Postgresql vs. Access97, TransferDatabase
@ 1998-11-26 09:55 Dave Page <dpage@vale-housing.co.uk>
0 siblings, 0 replies; 7+ messages in thread
From: Dave Page @ 1998-11-26 09:55 UTC (permalink / raw)
To: pgsql-interfaces
Hi,
You could also try using pgAdmin (http://www.vale-it.demon.co.uk) which
includes data import and export wizards and a datasource(/Access .mdb)
migration wizard.
Regards,
Dave.
--
Dave Page, Network & Systems Manager, The Vale Housing Association Ltd.
dpage@vale-housing.co.uk
http://www.vale-housing.co.uk (Work)
http://www.vale-it.demon.co.uk/ (Home of pgAdmin)
Beer can be a permanent solution - but only if you have enough of it!
> -----Original Message-----
> From: owner-pgsql-interfaces@postgreSQL.org
> [mailto:owner-pgsql-interfaces@postgreSQL.org]On Behalf Of Gilley,
> Charles H.
> Sent: 25 November 1998 20:56
> To: pgsql-interfaces
> Subject: re: [INTERFACES] Postgresql vs. Access97, TransferDatabase
>
>
> Okay, to provide some helpful feedback to others.... I
> readily admit that
> I am still a novice at this, so feel free to chime in and correct any
> misunderstandings that I may have.
>
> First, I've been trying to transfer a large dataset from
> access to postgres
> using a variety of methods:
>
<SNIP>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1998-11-26 09:55 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-24 18:12 Postgresql vs. Access97, TransferDatabase Gilley, Charles H. <Charles.Gilley@glenayre.com>
1998-11-24 19:14 ` Byron Nikolaidis <byronn@insightdist.com>
1998-11-24 19:29 ` Patricia Rodriguez-Tome <tome@ebi.ac.uk>
1998-11-25 23:01 ` David Hartwig <daybee@bellatlantic.net>
1998-11-25 20:55 re: [INTERFACES] Postgresql vs. Access97, TransferDatabase Gilley, Charles H. <Charles.Gilley@glenayre.com>
1998-11-26 04:52 ` Michael Meskes <meskes@usa.net>
1998-11-26 09:55 Re: [INTERFACES] Postgresql vs. Access97, TransferDatabase Dave Page <dpage@vale-housing.co.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox