reproducing MSAccess db for Linux

[klemm]

Hi

 

I am about to end a big dilemma I have

I haven't found (jet) any equivalent for MS Access in Linux.

 

So the second choice is to to reproduce the database somehow under Linux.

Now what to use. I really haven't been working with anything else than msAccess.

 

The database (under MSAccess) consists of ~40 user forms / 60 queries / 15 tables of its own and 30 tables linked to Informix database.... And a lot of visual basic code (about 100 or more functions). it serves on extracting and analyzing some data from the backbone database of a big ERP systems. (and I made this)

 

Hmm.. and I want to port it to Linux.

Actually there is no big driver behind that, except the one that I want to do it. Also maybe to prove to myself that MSAccess is not the only way to quickly and easily build a database application.

Well I know nothing about C or any other language.

 

What you folks suggest ?

 

Klemm

[fuzzylizard]

You will most likely not find an equivalent to the frontend/backend support you get with Access. On the otherhand, Access does not really do either of these well.

 

Suggestions:

1. Use mysql as the database

2. for the front end, check these products:

SQuirreL SQL - http://squirrel-sql.sourceforge.net/

Aqua Data Studio - http://www.aquafold.com/

knoda - http://www.knoda.org/

* this is probably the one that you will want to check out first as it most closely matches the functionality of Access.

[schussat]

I will echo fuzzylizard's recommendations on using mysql. However, you might also look into mdbtools, which provides some tools for using Access databases on linux. I haven't looked into it for a while, but it has a pretty active set of users and developers, and while I don't think it will support your user forms, it may function as an intermediate step.

[klemm]

an other database as a backend is a normal (natural) choice

As the only place what the user sees in the current database are the forms or as sayd here: GUI

 

So in this I see that knoda is defenately the closest where it can almost be built on the form level. I am already dloading it to see what it is capable of.

 

Aquafold is a nise app. and serves more like a database admistrators tasks.

 

Well, not a easy job.

When you would start a database app. from a scratch then what would be the startpoint or what applications youd use for building the GUI, while using for instance MySql in the backend.

[fuzzylizard]

Personally, I would use a web server and php. Or, if that is not an option, then maybe Java. Although, knoda does look like an interesting app.

[johnnyv]

Apache with mod_php accessing a mysql/pgsql database (you could use adodb for abstracting database access so you can easily swap databases in the future).

Thats about the fastest way to code a working app that i can think of especially if you need clients on windows/linux/mac etc a browser app is easily created/updated.

 

Still its a lot of new stuff to learn good luck

[Gowator]

Hmmm the old easy in and hell to get out MS paradigm.

 

Your problem is the forms, reports and functions...

And ... you have plenty of options depending on your development plans and how quickly you want to do it.

 

One method is to kjust dump the table data into another database.

 

Rather bizarely the easiest way to do this is probably in Access. Access isn\t particularly good at being connected to by anything except a MS IIS server!!

 

The way I do it (in oracle but mysql will work just as well) is to creat a DSN etc. in windows and connect Access to the other database you just made.

 

Then grant the ODBC user full privelidges, esp CREATE TABLE and make a complete copy table by table but as links. (i.e. You actually create them in the new db)

 

Create the table simply, just use CREATE TABLE XXXX AS SELECT * FROM YYYY

 

Access has the most weird permissions structure so you are going to need to do permissions etc afterwards anyway.

 

Then point your forms at the new ODBC tables in the new database (oracle/mysql etc)

Iron out bugs like permissions etc. and you still have an access front end to keep the thing working as you gradually replace the forms.

 

Then, its the hard part... creating the forms in whatever....

 

One shortcut is to use PERL, specifically PERL::DBI and DBD::Oracle or DBD::MySql

"Well I know nothing about C or any other language. "

Well, Perl is pretty easy ... its not an elegant language from a programmers point of view but it gets the job done and is no harder than visual basic... its definately a good first language to learn..

 

 

That way you can use Glade or similar to design forms which just run a perl module.

 

Its surprisingly quick (especially when youre used to Access ....) Once the hash is made its lightnig fast ....

 

Alternatively use a web interface like php or apache perl to do the designing.

This is probably a nicer way unless you require things that just can\t be done in a web interface.

 

I used to do this EVERYDAY ... in my old job ... i just have a massive hangover right now and my head is fuzzy so Ill add some more tomorrow ....

[DOlson]

Don't know how closely you can match it to Access/VBA, but I use OpenOffice.org as a frontend to a MySQL server. Works for me.

 

Although I'm learning MySQL for PHP/MySQL web-based applications.

Edited November 8, 2003 by DOlson

[Gowator]

Other options would seem to be to migrate it all into INFORMIX (since you have it and some of the data is there anyway) and concentrate on the front end, macros etc.

 

Must admit Ive never used informix though but I know its accessible through Perl which means you can use a perl cgi for web access or glade etc for local forms.

 

 

Another avenue is perhaps kylix (again I never tried it) but it might provide some RAD tools....

 

If it helps you are not alone....

The ability to knock up a prototype in access is very seductive and Ive done it myself.

The trick I found out the hard way is knowing when to stop!!!!

The JET engine REALLY stinks for REAL DB work....

 

If you set out expectations and timeline I might be able to be more specific....

For example... one option is leave it in Windows but use Gtk generated forms and perl backends to gradually replace the access forms and scripts!!

Like DOlsen says OpenOffice might be a good quick solution for a single user....

 

I think the future is pretty much web based .... so this might play a big part.

 

One way around this might be to stage through a ms.net framework reading the jet database directly from the server but then aim for php/perl replacements.

Both have fairly good conversion utilities from .net VBA to native code ....

The criticality of the applications and downtime etc are your big inponderables....

Do you want to develop it just on a snapshot of the data until its finished OR piece by piece on the live data.

 

If its critical its obviously better to get it all working etc. on a snapshot BUT you will need some debuggers and testers and its sometimes hard to get budget for people putting DEAD data into a test database.

[DOlson]

Okay, I started making an application in PHP and MySQL, and I must say, it's really sweet... Maybe it's not as easy as Access at first, but it does surely rock. PHPMyAdmin is nice too for setting up tables, users, etc.

 

Here's a picture of what it looks like, although you can't tell too much from just one picture: http://aslan.no-ip.com/~dana/images/helpdeskapp.png

[johnnyv]

rekall has been released under a dual lisence

It is somewhat similar to ms access

 

www.rekallrevealed.org

[roland]

rekall has been released under a dual lisence

It is somewhat similar to ms access

 

www.rekallrevealed.org

Looks nice.

Now lets find how to convert all my VBA code to python :unsure:

 

roland

[theYinYeti]

OpenOffice can be used as a Access-like front-end to PosGreSQL or MySQL. Try and google, and you'll surely find the information. It involves installing the ODBC driver for the database you choose, configure it, and then point OO to this driver.

 

Yves.

[roland]

OpenOffice can be used as a Access-like front-end to PosGreSQL or MySQL. Try and google, and you'll surely find the information. It involves installing the ODBC driver for the database you choose, configure it, and then point OO to this driver.

 

Yves.

thanks, that can be an interesting first step. fortunately my database is already splitted in two: one part for the tables, one for the front end: forms, requeries and vba modules.

The one who adviced me to well separate the db itself and the front end was correct. I don't think that was on the M$ manual..

 

roland

[johnnyv]

I have given rekall a try and although it is pretty similar to access in functionality it is way slow.

 

For grabing 2 indexed columns from a table with 4000 rows it takes around 5 seconds.

To contrast it takes a php script served via apache to a web browser around 0.2 seconds on the same hardware(just using the mysqlclient program the same query takes 0.03 seconds).

 

Once the data is grabed then you can work with it on forms fine and fast but it is the initial grab that is a killer. Any ideas on speeding up data transfer?. As it currently stands i will definately stick with apache/mysql/php although rekall may still be good for report generation for me(and speed will probably improve in the future).

 

xul looks promising as well, probably more promising but will not be a RAD.