Linux

Ask something
Discussion Locked

how to manage MS access on linux?

0 helpful
16 replies

by mostafa_ismayel

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How are you all?
I hope your all fine
I need an advice
Is there a program to manage MS Access database on linux or a way to convert it to another format that can be managed on linux
i have a MS database contains my work for more than six months and i'm learning how to use linux now but i can't manage my MS database on it.

16 replies

by apotheon

1. This should really have been posted in the Technical Q&A section (there should be a link way up at the top of your page for it, between "Discussions" and "White Papers").

2. Check out MDB Tools (tools for working with the Microsoft DataBase format), at: http://mdbtools.sourceforge.net/

If you're on Debian GNU/Linux, you should be able to get it by typing [b]apt-get install mdbtools[/b] at the command line.

by jaqui

or take it into msaccess and export as sql inserts and import into mysql or postgresql
though you would need to design a front end for the queries you run against it.

by johnzoet

MS Access is 2 things in one product:
- a database
- a development environment

The database is easily ported to far better
databases like McKoi, HSQLDB, MySQL or
Postgresql.
The hard part is the code you have already
written.
Unfortunately you will have to rewrite the lot.
This is the price you have to pay for lockin to
Windows only technology.
Take some time to learn Java and port the code
to Java using Swing and JDBC.
As an added bonus you can:
- run your application on any Java platform
- run McKoi or HSQLDB as an embedded database
- run your system on a LAN or over the internet
- do not pay licensing fees to M$
- do not pay licensing fees to anybody at all

by Member 34171447

If you're contemplating the effort to convert your database, you may want to look closely at HSQLDB, the successor to Hypersonic SQL.

http://hsqldb.sourceforge.net/

I describe this as an "exciting" option because HSQLDB is reported to be the engine behind the new database component in OpenOffice.org ver. 2.0. If integration with a high quality office suite would be an advantage for you, then check out OOo.

Developer Alphas for ver. 2.0 are currently available for download (I'm a "civilian" who's been using them for several months). The first Beta is scheduled for release this month, with the final version targeted for March.

by kaspencer

I would advise keeping the front end objects (Forms, Reports etc) in an Access database and transferring the data objects (Tables, Queries/[Views] etc) into a MySQL on your Linux server.

There are publicly availabe tools for creating your MySQL database from your Access tables, or you could do it the "hard" way by exporting your Access tables to CSV files and building your MySQL tables from scratch.

Although the database schema functions in Access are probably a bit easier to use then MySQL, you will find it easy to link the tables into your MySQL back end from your Access front end.

If you are more adventurous, you could use the ADO library in MS Access rather than simply linking the tables by ODBC.

Kenneth Spencer

by chris

We are in the process of getting rid of MS Access. We are a small business with 30 staff using a single quoting/invoicing/inventory system. We have found that lack of performance on reasonably good hardware is the biggest issue as our database grows. The way Access handles record locking on shared databases is another issue for us. Cost hasn't been the reason we are moving away from Access, performance and reliability is.

We are currently migrating from MS Access. We have outgrown Access as our database has hit the 500Mb mark. We have had great success with Postgresql running on RH Enterprise with MS Access front end on the workstations. We have had a few problems, in particular, our database is now case sensitive. Stage 2 is to get rid of the Access front end.

We are currently working on a web browser based front end/JBoss Application Server/Postgresql setup. We have been very impressed with the performance when it is running on the same hardware as MS Access. Just need funding from the Finance Department to go on with the project.

Regards,
Chris

by tgueth9

All the others require that you convert your Access programs. The simple solution is to set up a Linux server as a file server and use SAMBA (usually with the Linux server) to configure the Linux server to be a file server for Windows network. Then, move the mdb files (usually best to only move the "backends" if you split the database) to the Linux file server, map a drive to the Linux folder where the mdb files were put, and you now can run just like you would with Windows computers.

by apotheon

The question wasn't about storing it on Linux, but about [b]managing[/b] it on Linux. If the Linux machine in question is just going to act as a passive fileserver, your response is 100% correct. If it's going to be used to manage the database, on the other hand, either some Access-compatible unix software needs to be found or it needs to be converted to another db format.

Considering that Access databases produce a tremendous sucking sound, though, it's usually advisable to convert the database to another format anyway, when practical. That's [b]especially[/b] the case if it's going to be managed on a unix system like any flavor of Linux. Thus, the answers that were given.

Your suggestion still requires management from a Windows machine. That's all well and good if you want a more stable and secure setup without the boss knowing he's dealing with a unix machine. On the other hand, if you're going to have someone technically proficient (or even simply not a modern Luddite) handling your database it's probably better to just convert it into a relational database and manage it with something like MySQL.

by mickey6

There are a few steps to take:
1 make sure Apache, PHP and mysql are installed on the Linux PC.
2 export the data to a sql script file for later import
3 (optional but helpful) download and install webmin from www.webmin.com it will allow easy admin of most all linux functions thru a web interface.
3b (if 3 was followed) make only the database thru webmin then enter the databse and use the sql import tool with the sql script you exported in step 2.

there is complete support in PHP for most any of the major databases including access and the SQL's.

there is a utility vb2php (do a google search) that will work well if you export and save all of your access code to Vbscript and convert it to PHP.

with very little changes you can move the data to mysql and turn your application into a web interface reachable from any OS.

our network has linux, MS Windoze(98,Win2k and XP), MAC and SCO workstations and this has worked very well for all.

Hope the help someone

by apotheon

That's definitely one very good approach to it. While I don't always think PHP is "the way to go", it certainly provides for a far more flexible, secure, and stable system with (My/Postre/whatever)SQL than using VB with Access or MS SQL Server will ever give you, and unlike the MS software it won't lock all of your non-Microsoft clients down to minimal functionality at best by lack of compatibility intentionally designed in.

by jaqui

the only problem is that you need to secure the web server and service properly.

use ssl and nocache.
with a tight user authentication.
or you are leaving everything exposed to publc scrutiny.

by Member 34304663

Here's the background to my problem:

My client has an Access XP database front-end with Access tables residing on a server directory for the 'back-end'. The database performs three primary funtions; Memership Tracking, Trip Planning and Accounting (General Ledger, General Journal, Payables, Receivables, Trip Costing and Financial Reporting/Analysis). It was originally written in Access 97 to replace a small mainframe application that was being retired due to Y2K issues (and the owner 'getting out of the business'). It has since grown from a single primary user, to an inter-office application that serves anywhere from three to twelve concurrent users, with some reporting done through and intranet web site. I am the third developer of this application and have nurtured it from a rough beginning to its current state. Stability, security, performance and the desire to share more of the information stored in the database on a public web site are all issues that have forced my client to commit to scaling this application up.

The decision was made to use open source alternatives whereever possible. Here's the path I have chosen to solve a similar problem to yours:

1.Convert the access tables to MySQL running on Linux (RHES4) by using the conversion program Acces-to-MySQL (www.convert-in.com). This does a nice job of converting data-types and data, and made this part of the project a snap.

2.Next, I connected the Access front-end to the MySQL server with the MyODBC connector. I had to rewrite portions of the application to use the MyODBC connector to achieve compatibility. This has taken a bit of work, ODBC use in Access has some quirks that I have not encountered before (Say it with me, ?Google is your friend!?)

3.Since the plan is to get away totally from Access (and all its baggage), this is a stop-gap measure. Since it's the most frequently used aspect, I'm currently rewriting the Memership Tracking portion of the database as a web-app using Java with Apache technologies (Apache Web Server, Tomcat, JSP/JSF, JDBC).

4.After the Membership portion is complete, I will be re-writing the rest of the Access front-end in Java using Swing and Netbeans as the core of the project. This is the part of the project that I anticipate will take the most time. Since my client is committed to getting away from using proprietary technologies whereever possible (they have Windows and Mac OSX desktops, and are evaluating Linux as alternatives), they want to be sure they won't run into the same problems they had with mainframe.

So far, the web-app portion has been coming along fine, and, concurrently, the Swing portion of the application for the Trip Planning app is going faster than I had anticipated (I need to start a blog!).

Anyway, this is one persons solution to what I'm sure will be a growing problem. Good luck with yours!

by apotheon

That's an admirable endeavor you've got going there. Will you be releasing the software source code as FOSS (GPL, BSD license, CCD CopyWrite, whatever)?

It sounds like you've really got your work cut out for you, but it seems your handling it well. If you get a blog going to chronicle this, please let me know. Posting to this thread about it should be sufficient for letting me know about it.

by Member 34304663

It will be up to my client with regard to releasing any or all of it as open source (I would most likely go the GPL route). There is definite merit in releasing at least the accounting portion and some of the more 'common' functions from the application to open source. The rest of the app is very specific to there business processes and most likely would not have much relevance elsewhere. But I've yet to make the case to my client.

I hope to setup a blog at some point in the not to distant future, but I barely have time for anything other than my clients project. I will definitely post a reply to this thread when I do.

by clarkg

Rekall is a nice database front-end that will work with almost any database or with ODBC. Open Office 2.0 Beta has a nicely improved "Base" program that can work with Access or ODBC.

With some effort, either should give you a nice, new Linux access to your old Access data.

note: my Rekall came with my SuSE 9.2 distro. You may have to download it at:

http://www.rekallrevealed.org/

Hope this helps.

This discussion is closed to new replies.