File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes MS-Access & potential pitfalls Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "MS-Access & potential pitfalls" Watch "MS-Access & potential pitfalls" New topic

MS-Access & potential pitfalls

Ashish Hareet
Ranch Hand

Joined: Jul 14, 2001
Posts: 375
I'm making a prototype scheduler for plant maintainance & breakdown times for the machines( & components, etc. ). I intend to use MS-Access as a back-end for now & later move on to Oracle or preferably MySQL.
I just read somewhere that MS-Access does not support quite a few SQL operations. Now the question is -
"What are the potential problems I'm gonna be facing here"
Trying to benefit from your experience
And one more thing - I intend to write a small editor for adding tables,columns,insert,update etc for my DB - is it a good idea or should I write data to the DB thru Access & let my app only retrieve the values. The reason I want to write the editor is cause I might have control over the data types in the tables - Any suggestions

I opened the JDBC section of Core Java barely 24 hrs ago so plz bear with me if my questions sound naive
Ashish H.
Michael Zalewski
Ranch Hand

Joined: Apr 23, 2002
Posts: 168
MS Access does not have stored procedures or triggers.
Neither does MySQL.
MS Access is a poor choice for more than a few simultaneous users. Technically, it will support up to 32 simultaneous users, but you will probably get into trouble long before you get there.
MS Access will not support 24 x 7 availability. In order to do maintenance operations such as backups, you will have to disconnect the application. There is no 'redo' log so if you have to recover, you loose everything from the point of last backup.
But MS Access is perfectly fine for small applications. And if you access it through the web, the limit of 32 users means 32 'concurrent' users. This might mean that as many as 300 or more users are looking at the system, with only up to 32 at a time actually processing a request.
I agree. Here's the link:
subject: MS-Access & potential pitfalls
jQuery in Action, 3rd edition