Convert access db to...

[s0ck]

I hope someone could shed some light on this or point in the right direction as I'm a complete db noob.

We have an in house MS Access DB that someone wrote years ago. It's almost become a 'hand me down' of software. If I had to describe it, it's kind of a stock checklist; any new stock will go into it and we change the necessary fields to in turn allocate it to a customer. We run reports off it which in turn generate contracts.

The front end, if you can call it that, is a bit quirky in that anyone can change any records and the first record is always a false one because this is where we change the fields to filter on them and hence search for items. I would like to make this easier for everyone to use and perhaps have the functionality that only certain people can search for records but not modify and certain users would have full access. I'm not sure whether I need to completely scrap what we have and start again with something fresh. i.e. a paid up app specifically for this task or whether it's possible to turn it around. I do welcome a challenge though and if it saves some cash at the same time, job done! My jumbled collection of thoughts are as follows:

New frontend for Access (if this is possible) making searches easier and eliminating the current bodge of being able to overwrite data accidentally (they're mostly drop down menu's).

Ditch Access, convert/migrate DB to MSSQL/MySQL/Firebird/etc and look into obtaining a functional front end for above mentioned DB then start 'customising' it to do the same things we do now. Whether this is possible I have no idea. I suspect the tables may migrate fine but I'd have to write queries/reports from scratch? Perhaps the front end could be a PHP web page or something else that can query SQL.

Find some new App that does all the above and save myself the time/aggro of making it work myself.

Thanks for any pointers.

 

[Hades]

Hmmm well if it were me then I'd probably migrate the data onto MySQL and write a front end using Java or PHP. But there is probably a half-way house which will give you improved security and not have to worry about retraining everyone to use it.

Migrate the data onto MySQL or whatever database you prefer. Then use Access to connect to the MySQL tables (create a new 'link' table to MySQL using the ODBC driver).

On MySQL setup one userid for each person. Each id can be restricted to read/update the appropriate tables.

When the user wants to access the data they do so using Access. They need to enter their MySQL userid when prompted and this will govern whether you can read or update, etc.

 

[JIMA]

Couple of other approaches....

If you needed a sophisticated front-end then writing something in VB .Net (or VB6) might be an idea. I found that accessing an AccessDB from VB was pretty straightforward, actually easier than writing forms in Access.

Alternatively it might be worth considering something like Bi-Query that lets you run reports graphically against a database. Not too sure if this also deals with data input but worth a look.

Whether you'd want to move away from Access would depend on the amount of data, concurrent users etc. I'd say if it works and is going to be fast enough to cope with probable future volumes of data and users then leave things as they are. When migrating you always find something that doesn't go quite as planned. However, if Access won't be able to do the job then something like MySQL would be a good move.

 

[s0ck]

Thanks for the suggestions