Moving MS Access Data to Linux
By rickh at 2006-03-04 12:57
A real frustration for many people who would like to start converting from Windows to Linux is the lack of a satisfactory replacement for MS Access. Think what you will of Microsoft, MS Access is a very slick tool for people with simple database structure needs, and quite a professional tool as a front-end GUI for powerful RDBMSs, such as Oracle.
I can't provide a true Access replacement, but, if a person is willing to work a little bit, I think I can demonstrate how to get started on a reasonable facsimile. My goal is to explain step-by-step to people who are not professional DBAs how to convert a simple MS Access database to run in linux using POSTGRESQL and ReKall. You should be able to get through this process without touching the documentation for either ReKall or POSTGRESQL. You'll have plenty of time for that later.
I looked briefly at Kexi, but I don't think its development is quite as far along as ReKall. There is no straightforward conversion that I have seen, but I think this process will encourage people willing to follow it through. I am actually documenting the process as I do it myself with my MP3 collection, currently managed in an MS Access database. I did such a conversion once before a year or so ago when I was using Fedora, but my skills with either ReKall or POSTGRESQL are practically non-existent. I do know Access very well, so if that's your position we should feel quite comfortable together. If you follow the outline carefully, you should avoid much of the frustration associated with experimenting in the dark, and hopefully learn enough along the way to carry on successfully. My distro is Debian, sorry about yours, and the guide is somewhat Debian specific, but I don't think that should slow you down too much.
You need root access, to do some of the setup, but the end user will not need any additional privileges. In following the setup instructions, pay attention to the #'s and $'s preceding the commands.
This HOWTO is not about teaching you to efficiently use or maintain either ReKall or POSTGRESQL, just to get them up and functional enough to get data from an MS Access database. Once that's accomplished we (you) will evaluate how much more, and specifically what, you need to learn. I chose POSTGRESQL because its SQL language is very similar to Oracle's, as is AccessSQL. This should simplify some SQL specific issues as we go along. ReKall is a KDE application, so you'll need large portions of it installed even if (like me) you use Gnome.
Get the software.
Installation packages for both ReKall and POSTGRESQL are available from the Debian repos. (At least 'testing' has them. That's where I got mine.)
# apt-get install rekall
# apt-get install postgresql
Getting Postgresql Working
The default Debian installation of POSTGRESQL does not set up links to libraries so the 'initdb' command (coming up shortly) needs to be run using the complete path. Once you have it installed there will be a POSTGRESQL server running and a POSTGRESQL user named 'postgres' on your system. By Debian's default installation, POSTGRESQL will always be running from now on. It will come up automatically when you boot. For purposes of this project, we are going to assume you'll leave it that way.
You need to establish a POSTGRESQL user with the authority to create new databases, and you need to define a place where the data will be stored. The POSTGRESQL documentation recommends setting up the data storage area in /usr/local/pgsql/data/. That way if you have several users they will all have access to that area.
Our example will assume only one user, joenormal, who wants the data in his home directory. (Understand that he already exists as a Linux user, and he will be added as a POSTGRESQL user.) Setting it up this way means that you would have to run 'initdb' once for each user who plans to develop and maintain databases. All right. Ready. Log in as root.
joenormal@debian:~$ su -
debian:~# su postgres
postgres@debian:/root$ createuser joenormal
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
Welcome to psql 7.4.9, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
There are a few indentations and carriage returns in there that you won't see on the screen ... artistic license. What we have done is is define 'joenormal' as a POSTGRESQL user, and tested to make sure he was recognized as such. So far, so good. Now we'll make a place to store the data.
joenormal@debian:~$ mkdir ./pgsql/
joenormal@debian:~$ mkdir ./pgsql/data
joenormal@debian:~$ su -
debian:~# chown postgres /home/joenormal/pgsql/data/
debian:~# su postgres
postgres@debian:/root$ /usr/lib/postgresql/7.4/bin/initdb -D /home/joenormal/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale ...
. (whole buncha other stuff here)
creating information schema... ok
vacuuming database template1... ok
copying template1 to template0... ok
Success. You can now start the database server using:
/usr/lib/postgresql/7.4/bin/postmaster -D /home/joenormal/pgsql/data
/usr/lib/postgresql/7.4/bin/pg_ctl -D /home/joenormal/pgsql/data -l logfile start
That's it. We are now ready for ReKall. Don't bother trying to use the start-up commands at the end since POSTGRESQL is already running.
A brief housekeeping interlude:
ReKall is not a database. It's just a GUI front end that could be used with several different RDBMS server applications. (For our purposes, POSTGRESQL) Your ReKall database will not be a tidy file that you can pick up and move in it's entirety, like an .mdb file. The name you assign will just be a file with some pretty much indecipherable XML code that tells ReKall where the data really is. Another thing, for purposes of clarity we will use the term RDBMS when referring to the real server (POSTGRESQL). The term 'database' or 'ReKall database' will describe our working environment.
Also, this might be a good time to hop over to windows and get some data ready to move. For this first experience, be gentle with yourself. Open a simple Access database with only one or two tables. Note the table definitions, field names, formats, length, etc. Now, right click on the table name, select 'Export', and send the table data to a delimited text file (*.txt, *.csv, *.tab, *.asc). When you do that, you'll be given the opportunity to save it as a delimited file (yes), and to pick the delimiting character, as well as a 'text qualifier'. I personally like to use the '|' character as the delimiter, since some of my data fields are likely to include commas, etc. I also normally choose no text qualifier. How you choose to set it up is personal, but remember the delimiter you used, and the text qualifier. You'll need them across the way.
Now, you have your data saved in a delimited text file, and you need to make sure that file is someplace you can get to it from Linux. I trust you.
Getting the data to ReKall
ReKall should already be installed on your system, assuming you've been following along religiously, so let's have a look. My install did not put an icon on any menu immediately, although one did eventually show up on the 'Programming' sub-menu. Pure magic, I think. Anyway, if you can't find a button to click, add a launcher to your desktop. The command will be 'rekall' and you should be able to find an official ReKall icon someplace (in the ~/rekall/ directories if nowhere else). You could start it by just typing 'rekall' in a terminal window, but I couldn't get it to release, and lots of unattractive messages kept popping up in there ... Ugly! As MS Access experts and aficionados, we're in for a hard enough time without that.
When you get a window open, click on the 'New Database' icon; you'll recognize it right away. If you're an anti-icon person (some are), File | New Database will accomplish the same thing. ReKall will start a wizard to identify the RDBMS with which you'll be working. Enter the directory in which you want your database placed, maybe /home/joenormal/, and the name you want it to have. Let's name it 'test'. Click along, reading and choosing. RDBMS type will, of course, be 'Rekall PgSQL Driver.' Our User name will probably be 'joenormal', and when you get to 'Select Database', 'joenormal' should be the default. (Understand: 'joenormal' is not the database you are creating now. In this case 'joenormal' is actually the RDBMS. Very confusing.) The next click, 'Finish', should clarify things slightly. It'll take you to a window reminiscent of an Access Database Window with some things that look vaguely familiar.
In the middle pane are two labels, '!files' and 'test' (yourdatabasename). !files means nothing to us now. Read about it in the manual later. Select 'test', then Tables in the left pane. Click the little + sign by 'test' in the new, larger right pane, and double click on 'Create new table'. After naming your table, you are presented with a place to define the first table field. The process of field definition is not quite obvious to a person used to Access, but it's not too difficult. If you can't figure it out at all, give up now. Keep an eye on the formatting defaults on the bottom, they're a little different than Access defaults. I ran into trouble with the fact that all field formats default to 'No Nulls.' When you have the table(s) defined just like your original Access table(s), save, close window, and select 'Copy' from the left panel.
As before, click the little + sign by 'test' in the right panel, and double click on 'Create new copier'. You are now presented with a two panel screen. The left side is the 'From' side, and the Right side is the 'To' side. On the left panel select the 'File' tab and enter information about the text file you exported from an Access table. You need the delimiter character, the text qualifier, if any, and the complete path including the file-name. That last item goes in the box at the bottom labeled 'File/Error.'
On the right panel, the tab, 'Table', should already be selected, and you identify the table into which you want the data copied. If you've done it correctly, you'll see all the field names listed on the left side, and an obvious way to move all or some of them to the right side signifying that they expect to get some data. In the box at the bottom labeled 'Operation,' I recommend that you select 'Update/insert'. I tried 'Insert new,' and crashed the application. Next, hover over the icons on the toolbar and find the one labeled 'Execute copy.' Holding your breath, click on it, and, hopefully, you'll see a little box counting off the records as they are imported. Close the Copier window, and select 'Tables' in the left panel. Right click on your table in the larger, right panel ... select 'Data view' from the drop down menu, and give yourself a big pat on the back.
OK. That's as far as we go together. First a couple support items. The ReKall help files are very good, if somewhat disorganized. It's simply the ReKall Handbook. I need to figure out an easy way to get the whole thing printed on paper. At http://www.rekallrevealed.org/ the on-line manual is based on version 2.3, the next major upgrade. That's helpful as far as deciding if a specific need has a near term solution, or will require additional hard work. Also, at that website are links to get on support lists, which, while not immediate are helpful.
Remember, I'm as new at this as you are, but in the process of getting this far, I have noted a couple problems. If I get through them, I may eventually add a section called, Slightly More Advanced Techniques. Number one, a database created with ReKall can not be accessed by POSTGRESQL, and vice versa. It would be very convenient to be able to make changes in a database from either the RDBMS or ReKall, and see those changes from either source. Perhaps even worse, ReKall does not currently support any kind of action queries. In fact, I haven't figured out any way at all to do mass updates on the data. What I've read suggests that this can probably be accomplished by Python scripts, and ReKall does have significant Python support built in. Unfortunately, I don't know Python ... Add another thing to my 'to learn' list.
The QBE style query builders are crude, but workable. I haven't messed with Forms and Reports yet, but according to the manual, they're acceptable, if not as slick as the ones in Access. If building a GUI for professional quality RDBMS's was easy, there'd be a bunch of them. I'd be interested to hear from people who have real hands-on experience with one they consider better.
All times are GMT -5. The time now is 08:42 AM.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.