LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 01-06-2009, 10:55 PM   #1
hewittrj
Member
 
Registered: Mar 2007
Posts: 83

Rep: Reputation: 15
Is Mysql the answer


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.
 
Old 01-07-2009, 12:11 AM   #2
r3sistance
Senior Member
 
Registered: Mar 2004
Location: UK
Distribution: CentOS 6/7
Posts: 1,375

Rep: Reputation: 217Reputation: 217Reputation: 217
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.
 
Old 01-07-2009, 05:22 PM   #3
jlinkels
LQ Guru
 
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195

Rep: Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043
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

Last edited by jlinkels; 01-07-2009 at 05:24 PM.
 
Old 01-07-2009, 06:16 PM   #4
hewittrj
Member
 
Registered: Mar 2007
Posts: 83

Original Poster
Rep: Reputation: 15
what is PHP/?????

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
 
Old 01-07-2009, 06:36 PM   #5
jlinkels
LQ Guru
 
Registered: Oct 2003
Location: Bonaire, Leeuwarden
Distribution: Debian /Jessie/Stretch/Sid, Linux Mint DE
Posts: 5,195

Rep: Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043Reputation: 1043
Quote:
Originally Posted by hewittrj View Post
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?
Yes. But maybe you can get away with it and find an open source application in PHP which covers your needs

Quote:
Originally Posted by hewittrj;
and will it work in a intranet enviroment with internet access and still be secure.
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.

Quote:
Originally Posted by hewittrj;
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.
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.

Quote:
Originally Posted by hewittrj;
My Uncle is a miser with his money that is why he ask me to do this and not a pro.
I am not a pro either, but I try to do the same things as professionals do

jlinkels
 
Old 01-07-2009, 07:57 PM   #6
hewittrj
Member
 
Registered: Mar 2007
Posts: 83

Original Poster
Rep: Reputation: 15
l Lookinto PHP

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
 
Old 01-07-2009, 08:04 PM   #7
chrism01
LQ Guru
 
Registered: Aug 2004
Location: Sydney
Distribution: Rocky 9.2
Posts: 18,377

Rep: Reputation: 2755Reputation: 2755Reputation: 2755Reputation: 2755Reputation: 2755Reputation: 2755Reputation: 2755Reputation: 2755Reputation: 2755Reputation: 2755Reputation: 2755
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.

Last edited by chrism01; 01-07-2009 at 08:06 PM.
 
Old 01-07-2009, 08:09 PM   #8
hewittrj
Member
 
Registered: Mar 2007
Posts: 83

Original Poster
Rep: Reputation: 15
just as an after thought how about asp.net applications
 
Old 01-07-2009, 08:40 PM   #9
r3sistance
Senior Member
 
Registered: Mar 2004
Location: UK
Distribution: CentOS 6/7
Posts: 1,375

Rep: Reputation: 217Reputation: 217Reputation: 217
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. NoviceW Linux - Networking 17 09-17-2014 02:13 PM
ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. SpellChainz Linux - Software 3 06-28-2007 11:46 AM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. SpellChainz Linux - Newbie 1 06-23-2007 03:35 PM
mysql error Can't connect to local MySQL server through socket '/var/lib/mysql/mysql. Dannux Linux - Software 3 03-24-2006 08:44 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 10:01 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration