×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Transfering data from MS Access to PostgreSQL

Transfering data from MS Access to PostgreSQL

Transfering data from MS Access to PostgreSQL

(OP)
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?

RE: Transfering data from MS Access to PostgreSQL

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

RE: Transfering data from MS Access to PostgreSQL

(OP)
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)

RE: Transfering data from MS Access to PostgreSQL

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

RE: Transfering data from MS Access to PostgreSQL

(OP)
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?

RE: Transfering data from MS Access to PostgreSQL

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close