This is actually a 2 part question.

First he background on why
I have a family member who needed a Database to maintain his customer work orders and another to maintain his Warranty Repair customers. So I built him a Access Database for both. The relational Databases were broke down into 3 parts.
1. Customer information
2. Workorder Information
3. Equipment Information
and the Warranty database had warranty Company Name.
The problem is that more then one person needed to access the information at any given time, Access won't let them do this. But if memory server SQL databases do.

The first question is am I on the right path to solving this problem.

The second question is how hard is it to configure MYSQL to act as the Back end of a Access database and if it is also feasible to run SABA on the same PC as a SQL server. probable no more then 4 or 5 people will ever access the database and even then 2 of them will only be viewing the information and not inputting any data.


RObert

p.s. I am not an ACCESS programmer or a fulltime database writer my skills are minimal and I just do this for family. any help will do but I do ask a lot of questions.

As far as MySQL goes, it would seem to be the best answer for this. I believe an easy solution you could look into would be OBDC (Open DataBase Connectivity), too connect Access to the MySQL database, this is a simple way and it almost looks as if it's Access's own table within access but is infact going off to the specified location.

Technically it is possible to open up a Jet (Database Engine behind Access) Database for multiple users, but you may need to look into setting it into a Record locking mode rather then a Database locking mode, this is just an alteritive, I'd still suggest the MySQL route.

I think there is something wrong with your Access setup or with the implementation itself. Access is perfectly able to act in a multi-user environment. You only have to share the MDB file on a mapped drive. You have also the choice to split the application from the database if you like.

However Access is crappy for multiple reasons:
- It is not scalable. Some day you'll run into exponential growing access times. It doesn't count for that example database with 470 CD titles. But it does when you have 20.000 records.

- The application implementation is not straighforward, as you can use VBA, macros and SQL in an unstructured and concurrent way

- In my time (1995-2001) Access VBA tended to change from one version to the other, and render your application unusable in every new version.

- Security is not implemented in a secure way

- Worst of all: when ONE of your clients upgrades to the next Access version, you have to upgrade ALL clients instantly. Good for sales, bad for the system admin.

Access is a toy, just good enough for said CD collection or the addresses for your Christmas cards.

Interfacing Access with MySQL should be perfectly possible, Access speaks SQL, and if I am not mistaken you are able to write your SQL statements yourself, so you can write MySQL instead of MSSQL.

Decoupling the application from the database is smart and a huge advantage over an integrated thing. You'll overcome the forced upgrades and the speed problem.

That having said, if you could go for a web based application (PHP/MySQL) you'd also get rid of having to install your application on every machine which needs that. I found that always a major PITA.

jlinkels

If I could run this from a web page and with PHP and use the explorer as the interface would I have to rewrite the entire database to make it work?
and will it work in a intranet enviroment with internet access and still be secure. I used a setup when I was in the Army and we would get our database updates from a SQL server at the DOIM. with access as the front end. As for problems with Access and a multi user enviroment only if the database is split into two parts front end on the client side and a back end on the server side. We always had to do updates at the end of the day to make sure what we put in made it to the DOIM server. My Uncle is a miser with his money that is why he ask me to do this and not a pro.



RObert

Yes. But maybe you can get away with it and find an open source application in PHP which covers your needs

Yes. This doesn't break your security in any way if you have a proper firewall installed. ALL security depends on this firewall, regardless of the application.

I don't know what the DOIM is, but only is part of your database is off-line you'd have to sync at the end of the day. I have been supporting an Access hotel reservation system for years, and that was really multi-user.

I am not a pro either, but I try to do the same things as professionals do

jlinkels

I will look into PHP and maybe get lucky and find a PHP alternative to this little problem. If you see anything that might work let me know at
kc5jmr@gmail.com

DOIM is Acronym of

D department
0 Of
I Information
M Management

The military like to give everything a acronym I think

1. You're better off not posting your email in case a spambot picks it up. Plus we prefer to post stuff here so anyone can benefit
Just keep an eye on this thr.
2. You can read about PHP here: http://www.php.net/manual/en/. Basically you use it to write interactive (CGI) webpages. For a relatively small need as yours sounds, put MySQL (DB) and Apache (webserver) on one box. KISS eh?
A normal Linux install will do that for you, if you choose those options.

As said, then the users only need a browser on the client, IE or Firefox for MSWIN.

BTW, this means the updates will be in real-time, no end-of-day syncing reqd, although I highly recommend backups eg daily.

Good luck and come back if you get stuck.

just as an after thought how about asp.net applications

I don't know much about asp.net. If your asking will asp.net work with MySQL, Then yes this is possible. However if you are asking about access and asp.net, while it would work, it's ill-advised. Personally I do not know too much about ASP, I have seen some ASP but behind the scenes I am not sure. PHP is a dangerous area tho, while it's preferable to many many people, it's worth noting that PHP requires more work and more consideration of code... PHP is IMO too open on too many fronts and it leads to alot of security holes that it expects the programmer to know about.

As to an earlier question, you don't have to rewrite the entire database, but it's easier and more secure. You should be able to export/import data between the two database systems quite easily for the conversion between Access and MySQL, xml, comma delimited files or what not should work on that front.