Transfering data from MS Access to PostgreSQL

[dmg2206]

I'm trying to transfer an Access database into PostgreSQL. I tried creating a blank PSQL database and transfering the Access DB's table into it using Access's export tables feature. The tables did transfer, but no user has any read or write rights to them, so their totally useless. Any ideas?

 

[rycamor]

Not even the main 'postgres' user (or whichever the user is running the postmaster daemon)? This user should have complete control over all PostgreSQL databases. -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student

 

[dmg2206]

Surprisingly, no. I can't access the data with user "postgres" either. I can see the tables using command "\d", but when I try to run a select I'm told "ERROR: relation 'tablename' does not exist". When I view permission infomation with "\z", I get something similar to the following:

Access permissions for database "dbname"
  Relation  | Access permissions
------------+--------------------
 tablename1 |
 tablename2 |
(2 rows)

 

[rycamor]

Do these tablenames have capital letters in them? If so, you must refer to them in this manner:

SELECT * FROM "CapTableName";

SELECT "CapTableName"."CapColName" FROM "CapTableName";

Normally PostgreSQL it is case non-sensitive, meaning if you do a normal table create statement, you can use capitols to create it, but any query against it can be done using any mxture of upper and lower case. If you want the actual names to BE case-sensitive, you must use quotes around them in the table create statement, and in the queries.

So I assume that when exporting the tables, ODBC uses the "full quotes" method of creating tables, which requires quotes for querying. Just my guess, tho...

If you still can't get this resolved, try doing a dump of the database structure (not data), and show it here. You do this by using the "pg_dump -s databasename". You can pipe this output to a text file by doing "pg_dump -s -f filename databasename". -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student

 

[dmg2206]

It looks like that was the problem. I'm able to access the tables I've imported now. However, I still can't get one table to port over. When I try to export it from the Access database to PostgreSQL, I get an "Overflow" error in Access. Any ideas?

 

[rycamor]

Errrm... by any chance do you have a column in that table of type "money"? Or maybe a column with one of the Access proprietary column types, such as "hyperlink", etc...?

Have you considered exporting it as a CSV file (comma-delimited) or even tab-dlimited, and importing it into PostgreSQL? -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student