I’m working on creating a website for a small association. We’d like to have the possibility for people to apply for membership on that website, so we can review their application and accept them as members. I’m just wondering what is the best way to do the administration of the members. We first came up with the idea to do it in MS Excel but then I thought MS Access might be better, as you can make forms and reports with it. And I thought it might be possible to connect it somehow to the website, so that when people register on the website, their info automatically gets added to the access database.
I just don’t know if that is possible – I’m thinking now that when someone registers at the website, his info gets added to the mysql database on the server where the website is hosted. I wonder if it’s possible this information automatically gets added to the MS Access database? The thing is people will also be able to sign up on paper sheets which we manually will add, so in that case a easy to understand GUI like in ms access would be handy, as not all of us is able to insert the info in the database on the server in phpmyadmin. And perhaps someone just walks onto the desk and wants to sign up there, so in that case it would also be handy to power up Access and add the member directly there.
Any ideas as to what would be best solution for this? Connecting the 2 databases might not be the best idea I think (I can imagine data redundancy or errors during connecting etc.), but downloading the server db each time and importing it in access isn’t really efficient either.
Thanks in advance! :)
You could use Access as the frontend and MySQL for the backend. That way you only have one database in use.
This isn’t something I have a lot of experience with and, crucially, I don’t know how it would fare if you were allowing PHP scripts to update the database at the same time as the Access app. However, it does seem like a useful starting point. Maybe more experienced people than me could comment more.
MySQL should take care of locking tables on its own. But I think you’re right – if you use Access (someone correct me if I’m wrong), my understanding is that you will always be working with an exported copy of the DB (not the DB itself) so keeping everything in sync could get difficult. Unless you use Access solely for creating reports, etc. (and not for managing data), you might create “slow” race conditions and risk data loss.
You might consider something like phpMyAdmin in place of Access – It’s more capable when it comes to handling MySQL, plus it works with the DB directly.
Well, I was thinking of using phpmyadmin as it indeed works with the DB directly, but my colleagues unfortunately won’t have much knowledge of using that. So in that way, an easy gui like ms access has would be better. Also I think it would be more convenient to just open the database in Access in case they want to sign up using a paper sheet or just by visiting our desk instead of having to login to the server and start up phphmyadmin and add them that way. This ODBC thing looks interesting though, so I might have a look at that.
Thanks for the responses so far – if anyone else has any ideas how to do this in the best way, please let me know :)
not really – Using Access to interact with the database indirectly would probably work just fine, most of the time. But I’d still be worried about how terribly it could mess things up in the “edge cases.”
Also, while I agree that the phpMyAdmin UI is different, I don’t consider it to be “difficult” to use at all (certainly no more difficult than Access – actually, the biggest problem I’ve run into when desktop users start using web-based UIs is that they need to be re-trained to use [Tab] to cycle through fields, instead of [Enter]).
The other alternative would be to use the web database solely for application submissions (and not for data management at all – switching roles, so to speak; making the “real” data exist in Access and importing new data from MySQL).
Just my two cents: I would advise against using PHPMyAdmin or MSAccess to give employees access to the database. Especially if you are worried they will have trouble with the PHPMyAdmin interface, they certainly shouldn’t be interacting directly with a production database. I would suggest writing a simple web page that uses PHP to validate input, strip out dangerous text, and submit the changes to the database. Always treat internal employees just as you do the public users. If you wouldn’t give them direct access to the database, don’t give employees (save for SQL DB admins, of course) direct access, either.
You must be logged in to reply to this topic.