Would you recommend me using MySQL+MS Access on my situation?

[rtarrial]

Hi everyone,
been visiting this forum for a long time, but first posting

I would like some advice.
I work in a company with 600+ employees. We have a Windows 7 network, every user has a login with limited privileges (unable to install new softwares, for example).

My work here is to develop a system for data entry about the employees, to this end we are considering using Access 2007 as Frontend and SQL as backend, MySQL for instance.

I´m new to MySQL and ODBC, intermediate for Access, and advanced for VBA.

My question is: when the time comes for me to deploy my frontend to the 600+ employees, is it possible for it to work out-of-the-box, without interference from the final users? Is the MySQL ODBC Connector essential, or are there other alternatives from it?

Please consider:
1. Users don´t have ANY knowledge about computers AT ALL. I expect them to feed my forms, but I want to avoid them to all and every maintenance steps;
2.Final users CANNOT install some softwares, namely those that WIndows UAC present complaints. Probably including the MySQL connector. Those would have to be installed one by one, with someone with administrator privileges. Of course, if this is the case, we will have to abandon the Access+MySQL approach.

I´m aware it is somewhat possible to automatically (using VBA) relink Access tables to the SQL, as is explained in

http://www.mvps.org/access/tables/tbl0010.htm.

But, if the MySQL ODBC driver is not yet installed, is it still possible to automate the whole process?

If the MySQL ODBC connector is not fit for the task, would you recommend something else, i.e. the MS SQL Server? I do hope that MSSQL ODBC driver is natively installed on Windows..

Anyone can help?

 

[vacunita]

Personally and with such restrictions, I'd ditch Access and use a web based interface. An Intranet if you will.

That way no installation is required, you can deploy a shortcut to the intranet address to the users via the Domain, and you only need your interface to exist in the server.

Also no installation required, and no maintenance either from the users. As long as they can open a webpage from inside your own network, they can access the Interface and get to work.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[rtarrial]

Thanks for your reply, Vacunita!!
However, I´d say Web development is not an option in this case. I lack knowledge on this topic.

How about Access ADP (Project)? Is this an option?
Sorry this beginning to get off-topic.. Maybe move the topic for Access forum?

 

[ramam1]

Do you (IT department) have the ability to push updates remotely? For example how are windows patches deployed? You could potentially distribute components such as the ODBC connector (and the configuration for the connector) using the same techniques patches are applied with.

 

[vacunita]

The time spent trying to successfully deploy to 600 liimited users will be far greater than the time it takes to learn some basic HTML and put the site together. Or hiring a temp to do it.

In any case, I'd say you'll have to look into what you can push from the server. How much control and understanding you have of the Domain Policy so you can deploy the application via the Domain.

You could ostensibly configure Excel to work of off SQL DB. but Again it will depend on if you could successfully push the configuration through the domain.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[sleipnir214]

Abraham Maslow once said, "When the only tool you have is a hammer, every problem begins to resemble a nail."

I work for a public school system, and my CIO decided the best way to distribute some ad-hoc queries into our student management system's databases was Access. I will second vacunita's advice -- you really don't want to get into the business of distributing Access applications.

Access is nowhere near as robust as you're probably thinking it is, nor does the automated distribution of Access databases work anywhere near as well as it should, even when the users are administrators. Security is tricky. You never know when a Microsoft update is going to hose something.

After three years of use, maintenance is still a problem. Do youself a favor: don't maintain 600 installations of an app when you only have to maintain 1.



Want to ask the best questions? Read Eric S. Raymond's essay "How To Ask Questions The Smart Way". TANSTAAFL!

 

[ramam1]

Wow I just reread your original post and noticed this is green-field work.

For sure go with a web based application - developing a new Access based app is questionable at best IMO.

Either do it yourself - learn basic web development (PHP, and especially an MVC technology like Cake or Zend etc), or if that doesn't make sense, hire someone to do it for you. I think you'll save time and money and end up with happier customers and users if you do.
cheers

 

[rtarrial]

Thanks everyone, your comments are enlightening. Really appreciated.
It seems everybody recommends me to shun the Access approach, right? Well, if this is the case, which web development language should I use?
Ramam1 recommended PHP with Zend or Cake, for instance.

To help you on your suggestions: I learned to program on PERL, and also have a large experience in Excel VBA and some experience in Access. A beginner on SQL, but catching up fast.
Some experience in HTML and CGI (HTML+PERL).
I´m aware that PHP syntax is somewhat similar to PERL, so that´s a good starting point.

No experience on Java, or OO at all; not even sure how it works.

Another thing to keep in mind: my IT Department is composed of.. well.. only myself

 

[feherke]

Hi

I´m new to MySQL and ODBC, intermediate for Access, and advanced for VBA.

If that VBA means Visual Basic for Applications, then you could go with Visual Basic. See How to Write CGI Applications in Visual Basic on MSDN.


Feherke.

http://free.rootshell.be/~feherke/

 

[rtarrial]

One more important thing I forgot to mention.
It is essential that the page be "dynamic". For instance, when the user checks a checkbox, the contents of a combobox can be modified. Much like VBA works.
For this matter, would anyone recommend ASP.NET?

(Now this is REALLY off-topic!)

 

[ingresman]

I think you are wanting to implement a rich user interface be it in a browser or some kind of fat client. For a fat client such as Access you have a lot of power at your fingertips, under a browser you are really needing to go down the Ajax route perhaps using jQuery. All of which will be new you I think.
Should you be pushed down the web applicaton route?, possibly not. Your original post didn't mention it, seemed to more of an issue instaling the ODBC driver. looking at everyone elses points and issues I would say.
1. If you go with access, hold it on a shared drive that your users point to. Assume as you are only using the code parts it would be quite small.
2. Possibly use VB.NET rather than Access
3. Installing ODBC, you should be able to copy over the DLL's and update the registry as appropriate
4. Scrap ODBC and use web services (will need a web server)
5. Consider SQLServer as an alternative

 

[rtarrial]

@ingresman

you are right, my thread started with the ODBC issue but there were so many negative views about Access+SQL that I start to consider other strategies.
But of course I would rather remain with the Access approach, as I´ve already finished design for all tables and also some forms.

So what you guys think about this: First, I could work on MS Access as frontend to MSSQL (avoiding the ODBC drivers installation). In that case, would it be better to use Access Projects (ADP) or link tables via ODBC?

Meanwhile, we could start development of the web interface.
I´ve heard of Ajax and JQuery, but it is way too complex for my level. Perhaps hiring someone to do the job.

Once the web interface is done, we could say farewell to the Access frontends. It´s double the work, of course, first developing it in Access and then in another web platform, but it would be faster IMO, because I already have some knowledge on Access.

Do you think any of this makes sense?

 

[DonQuichote]

If you have the users "locked up", you surely must have ways to push updates and installs. So you must be able to push MySQL connector, for example.

If your users already have Access installed, you could use an Access front-end and a MySQL backend.

Beware that Access is a kind of program that enables total ignorant people to build little forms and reports. And that it got a very bad name from that. I have worked with Access as a front-end in former jobs, and it is not that bad. But you must program it right, off course. A lot of people use Access because they cannot program, but like any system you have to know what you are doing. There simply does not exist a simple idiot-proof system for working with huge sets of data.

In one of my jobs, we were flatly disallowed to use bound forms for stability reasons. That looks like a lot of work, but will save tons of work fighting Access itself trying to outsmart you. I am glad they had this rule.

Be careful with the fact that Access processes queries locally even if they refer to a server, unless you mark the queries as "pass through" queries. If you fail to do this, Access will happily download the entire tables used in the query and perform the joins locally. Needless to say that this is very bad for performance.

Good luck!


+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[Hap007]

MS Access frontend and a MySQL backend work very nicely together.

To make it work, you will need to instal the MySQL odbc driver(very small footprint) and then configure the MySQL ODBC driver to connect to your MySQL backend.

Perhaps you could use one of the install programs to build a small installation program that could handle the setup of MySQL ODBC driver and the ODBC setup

I would guess that Admin access would be needed to intall the software.

Good Luck,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[smays]

At some point someone mentioned Visual Basic. As your original post mentioned, your greatest strength lies in VBA. I do not know much about Visual Basic (I am a huge fan of CodeGear development tools), but I am confident your VBA skills will transfer nicely to Visual Basic. That said, frankly, I would seriously consider VB, especially if VB is capable of making stand-alone executables.

Using CodeGear development tools I create stand-alone executables (with very few dependencies, including NO ODBC DRIVERS AT ALL) that communicate nicely with a centralized MySQL database all day long. Copies of my stand-alone executables are resident on our local file server and every PC that uses my software has a simple shortcut on the desktop to access the program on the file server.

Steve.