LinuxQuestions.org
Latest LQ Deal: Linux Power User Bundle
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 01-09-2004, 12:50 AM   #1
ezra143
Member
 
Registered: Aug 2003
Location: NY
Distribution: RH9, RH8, Slack, Vector
Posts: 497

Rep: Reputation: 31
MYSQL, PostgreSQL, Access


Ok, a friend of mine approached me today asking for some help with an access database he is using. The problem is this: He works in a not for profit organization and has been using a database built in access for some time now. Over the course of a few years, he has built up a record-set somewhere near the 60,000 range in a few of his tables and has realized that his performance and data integrity is slowly, but surely, slipping away. He needs a db server to handle this info for him for little or no cost.... In steps TUX.

Now, i know that access has up-sizing tools for SQL db's, and i also know that they do not work for MySQL or PostgreSQL. But I was wondering if anyone has any experience with an up-sizing tool for linux db's (IE: yes, and it was more of a headache then doing it manually, or yes, it was a huge time savior, or no, i too have looked and have never seen such a thing, therefore i have no experience with it)?

For clarification... I know how to transfer the tables and write the SQl statements...etc, but i would really like to be able to do it in a few clicks so that I do not have to charge him anything (i can write it off come April 2005 as a charitable contribution, then we both benefit) because it did not cut into my other work nor his minimalistic budget.

So, in sum, he and I will be greatly appreciative if anyone can share. TIA
 
Old 01-13-2004, 04:26 PM   #2
Hertattack
LQ Newbie
 
Registered: Jan 2004
Location: Netherlands
Distribution: Gentoo 1.4
Posts: 26

Rep: Reputation: 15
I have used PostgreSQL for a webshop with a lot of products and used access as the backend. An easy way to do this is by using ODBC to connect to the PostgreSQL database. It works for MySQL as well, but i like postgress better.

You should beware of the date / timestamp fields in postgres. If the field has a different type than Access can handle you cannot update existing records.

You should be able to start using it quite easily. The ODBC connector can be downloaded. Do not forget to edit the pg_hba.conf file when using postgres and give the -i to postmaster.

I hope this will help you,
Marcel
 
Old 01-14-2004, 12:21 PM   #3
ezra143
Member
 
Registered: Aug 2003
Location: NY
Distribution: RH9, RH8, Slack, Vector
Posts: 497

Original Poster
Rep: Reputation: 31
yeah, i thnk i may have to do something like that..... i was hoping for a quick export to table type of feature, but i guess i can just maintain the table format and then use SQL statements to connect via ODBC driver. I just like the simplicity of the MS SQL upsizing tool. Thanks
 
Old 01-16-2004, 02:24 PM   #4
jdruin
Member
 
Registered: Jul 2003
Location: Louisville aka Derby City
Distribution: WinXP SP2 and SP3, W2K Server, Ubuntu
Posts: 313

Rep: Reputation: 30
You can export to a delimited file from Access, Excel, etc. Then you can upload these directly to a postgres table that has the correct format of course (IE. field size/type, number of fields, etc.). I have done this successfully with large tables that would have been a nightmare to transfer otherwise. I have even uploaded tables from Excel to Postgres. Some documentation is at:

http://www.sql.org/sql-database/post.../sql-copy.html

Here is an excerpt:

Name
COPY -- copy data between files and tables
Synopsis
COPY table [ ( column [, ...] ) ]
FROM { 'filename' | stdin }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]
COPY table [ ( column [, ...] ) ]
TO { 'filename' | stdout }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ] ]

Inputs



table
The name (possibly schema-qualified) of an existing table.

column
An optional list of columns to be copied. If no column list is specified, all columns will be used.

filename
The absolute Unix path name of the input or output file.

stdin
Specifies that input comes from the client application.

stdout
Specifies that output goes to the client application.

BINARY
Changes the behavior of field formatting, forcing all data to be stored or read in binary format rather than as text. You can not specify DELIMITER or NULL in binary mode.

OIDS
Specifies copying the internal object id (OID) for each row.

delimiter
The single character that separates fields within each row (line) of the file.

null string
The string that represents a NULL value. The default is "\N" (backslash-N). You might prefer an empty string, for example.

Note: On a copy in, any data item that matches this string will be stored as a NULL value, so you should make sure that you use the same string as you used on copy out.


Outputs



COPY
The copy completed successfully.

ERROR: reason
The copy failed for the reason stated in the error message.
 
Old 01-17-2004, 08:07 AM   #5
gruntwerk
Member
 
Registered: Dec 2003
Location: PA
Distribution: fc9
Posts: 89

Rep: Reputation: 15
I think, from access, you should be able to connect to the db on the server and just drag the tables...
 
Old 02-12-2004, 08:26 AM   #6
bartc
LQ Newbie
 
Registered: Feb 2004
Location: Netherlands
Distribution: Debian, Fedora Core
Posts: 1

Rep: Reputation: 0
(I found this forum post while googling for "postgresql access", so this is a bit late in the game, but it might still be useful to someone.)

From http://www.bullzip.com/products/msa2mys/info.php:
Quote:
Access 2 MySQL is a small program that will convert Microsoft Access Databases to MySQL.
I haven't used it yet, so I'm not sure how well it works, but from the feature set I'd say it's exactly what you're looking for. It's also free for non-commercial purposes.
 
Old 02-15-2004, 06:29 PM   #7
Crito
Senior Member
 
Registered: Nov 2003
Location: Knoxville, TN
Distribution: Kubuntu 9.04
Posts: 1,168

Rep: Reputation: 53
The PostgreSQL how-to recommends this prog: http://mdbtools.sourceforge.net
But I haven't tried it myself and the package hasn't been updated in over a year. Kind of a long time for a beta test. Maybe they're just being thorough.
 
Old 02-18-2004, 12:33 AM   #8
jdtiede
Member
 
Registered: Apr 2003
Location: Baton Rouge, LA
Distribution: Kubuntu 12.04, 12.10, 13.04a
Posts: 244

Rep: Reputation: 30
When all else fails, such as upsizing tools, exporting ASCII files and then using COPY to get them into Postgres usually works well. I had tables I had moved to Paradox files when I moved from Access to Delphi, and couldn't get Access to take them back and convert to ASCII so had to use whatever other tools I could find. Postgres is EVER so much more reliable than either Access, dBASE tables, or Paradox tables.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
MySQL x PostgreSQL Thakowbbery Linux - Enterprise 5 09-06-2005 10:30 AM
PHP with MySQL and PostgreSQL ? Boby Linux - Software 2 05-25-2005 06:11 AM
MySQL,PostgreSQL and dbm. cranium2004 Linux - Software 1 03-10-2005 04:48 PM
PostgreSQL vs. MySQL mikeshn Programming 1 07-08-2002 01:23 PM
MySQL of PostgreSQL that is the question? Noerr Linux - Software 17 06-02-2002 04:07 PM


All times are GMT -5. The time now is 01:17 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration