Forum Moderators: phranque
I have to take an old and rather simple Access database and put it on the web. My initial thought was to convert it to MySQL and build a PHP interface for it. Why? Because I know PHP and MySQL and don't know where to begin when it comes to Access and networks. However, I wonder whether or not I'm not just making work for myself.
It seems like there are three obvious solutions. I put them in order of increasing "comfort", but probably in order of increasing effort as well.
1. Use a MS database server of some kind, use Access as the front end. No clue where to start there and looking around on the web has been frustrating since I get so many irrelevant hits. Links to a good article or two would be much appreciated. In the meantime I'll keep looking.
2. Convert the Access db to MySQL and use a MySQL server, ODBC and again use Access for the front end. Again, pretty clueless as to where to begin.
3. Scrap Access altogether and just rebuild it in PHP and MySQL. Conceptually easy - I know all the steps for this - but I suspect that it's more work than absolutely necessary.
Anyway, I would love to get the random thoughts from folks who know the Access side a little and can point me in the right direction.
Thanks,
Tom
I guess I'll install the MSDE and start to play around.
Tom
<Added>
Actually here it is
h*tp://php.weblogs.com/odbc
...and in decreasing cost ;)
But seriously, the first two solutions mention using Access on the front end. What exactly do you mean by this? Are you moving the database to a publicly accessible point that you alone will modify/maintain and that can be accessed either by the general public or some type of authenticated group of users?
Are you moving the database to a publicly accessible point that you alone will modify/maintain and that can be accessed either by the general public or some type of authenticated group of users?
If this is the case - get ready for file locking hell.
bcolflesh, that is exactly why I asked the question first -->
ergophobe, I just read your cross post in the PHP forum...
For what it's worth:
--------------------
First off, I'm not trying to sway your decision one way or the other. I talked to a respected associate that does have extensive experience using M$ Acce$$ and $QL $erver. He informed me that record locking is one of the biggest issues they have when a client with an existing Access database wants to simply "roll it over" to the web. Quite often now they will convert the db into a more powerful solution, which in their case is SQL Server as that is the platform they prefer and support.
I bet there is a way to access an access database with php.
I'm not considering this solution - converting this DB to MySQL would be dead easy. If I'm using PHP, it will be a LAMP setup all the way. That makes the server cheap and easy to find and, above all to test, since I can use existing personal space to test everything.
Access on the front end
I mean using Access as a client and an MS server. That would let users use the interface they are accustomed to.
Are you moving the database to a publicly accessible point that you alone will modify/maintain and that can be accessed either by the general public or some type of authenticated group of users?
More or less. I, or some other admin, would be able to modify the DB structure/design, and a small group of authenticated users would be able to read, add and modify records. It is possible, however, that the DB would be made public in the future. My guess is that traffic would always be very light.
First off, I'm not trying to sway your decision one way or the other.
Please, sway my decision! That's why I posted for advice. Left to my own, I would do the LAMP solution, but I want to make sure that there isn't some super easy and *reliable* way to just roll it over. You're convincing me to stick to plan A.
[edited by: txbakers at 11:34 pm (utc) on Dec. 5, 2003]
[edit reason] fixed missing endquote [/edit]
I don't know about record locking in Access, but I did a quick test yesterday. In case anyone is curious, here's what I found.
I converted the Access DB to MySQL and put it on a server. Then I opened both a mysql client and Access, using ODBC to connect to the DB as a client.
As near as I can tell, I can't get Access to refresh. In other words, I change a record in Access - Tom becomes Tom123. The change is, or course, reflected in the data the next time I send the query via the mysql client. Then I change the data using the mysql client. In Access, however, I could only get the data to refresh by quiting the database entirely and restarting it. Merely moving to another record and coming back won't do it. That's bad.
I'm sure there's a way to deal with this in Visual Basic, but since I don't know Visual Basic, I think it will be just as fast for me to start from scratch.
To be fair, using Access as a client has some advantages - namely you can set the focus on a field, hit CTRL-F and search for a string in your record set. I'm doing this locally, though, so I'm not sure how well it would work sending all this info over the internet. Some of these records have the equivalent of 10 text pages and I can't see how Access could do the search without having the whole recordset available - i.e. huge amounts of data going back and forth for that one feature.
Tom
That's why I opted to "phone-a-friend". My experience with Access via the web is NULL. I won't offer direction based on comfort alone. I do, however, know this fella's experience and trust his opinion whole-heartedly. That's why I found it funny that bcolflesh posted right before me and echoed exactly where I was leading the questioning. I had already made the call prior to my first post, but wanted to know more detail about where you wanted to take this project -- both now and into the future.
I'm glad you are electing the LAMP option and think you will be happier. As far as changing the interface everyone is accustomed to -- hey, what could be easier than a browser?
P.S. As a side note, did you set up the ODBC connection as a System DSN?
But I would like that to be based on knowledge, not just comfort.
Access is a good desktop database, or for a small network.
It doesn't have the guts to support lots and lots of IO threads. It can't handle it and eventually will clog your entire website.
I speak from experience. I started my web app with Access and slowly it just died with only 10 concurrent users. I did an all-nighter over a weekend and converted everything to mySql and NEVER had a problem since.
And thanks txbakers for the personal anecdote. In your case it's an all-Access solution though, right? I presume I wouldn't have that trouble if I used a MySQL server and an Access client (though I've pretty much given up that idea).
ODBC connection as a System DSN
No, just a User DSN to connect to the MySQL server on the same workstation. Since my ODBC knowledge is less even than my Access knowledge, I have to ask: would that make a difference in my testing? I know that in theory a System DSN should provide somewhat faster access, but that wasn't really an issue.
The plus side to using Access as a client is that only the data is exchanged, rather than all the code for layout. One thing I had not considered, however, is that this is pretty low-budget and will almost certainly go on a shared server. I think most hosting services will not allow an Access client to link directly in that situation, but will only allow communication via CGI/PHP etc.
I know that mysql-front will generally be denied access to a mysql database. So that would probably mean colocation or something expensive in any case.
Tom
ODBC connection as a System DSN
Never mind, I didn't read close enough here. I forgot you said you had already pushed the Access database into your local MySQL database and were accessing the MySQL server with your Access interface. The point I was considering was an environment in which you were accessing the Access db remotely, perhaps on a shared server. This is where my friend was stressing the word *System* as opposed to *User* DSN but I didn't ask why, now I wish I would have. Sorry. It must have some sort of significance though. Here nor there at this point, I thought you were testing the Access db remotely.
As far as ...only the data is exchanged, rather than all the code for layout..., keep in mind that CSS will really cut that load down. Plus you can change the look anytime you want by simply changing your stylesheet. I'm sure you're aware of this, but it doesn't hurt to throw some shiny spots on the areas you feel may be less than desirable. I kind of like this idea better. Plus, what's 1K or 2K in markup really going to cost your transmission load? ;)
One thing I had not considered, however, is that this is pretty low-budget and will almost certainly go on a shared server. I think most hosting services will not allow an Access client to link directly in that situation, but will only allow communication via CGI/PHP etc.
I know that mysql-front will generally be denied access to a mysql database. So that would probably mean colocation or something expensive in any case.
mysql-front? You lost me on this one...?
mysql-front
This is/was a gui interface for mySql which is no longer being produced. Guard your installer carefully and make lots of backups. It's a great program.
as txbakers says, it's a mysql client (Windows only). If you ever work with MySQL on a Windows machine, it is well worth the cost (free download!). To clarify my comment, mysql-front tries to make a direct connection to the db (I don't know what protocol it uses). Most virtual hosts (every one I've used so far), will only allow connections that are coming internally (via Apache/PHP/CGI etc), so you still need to be able to use the standard mysql client. But for use on a server you run where you can set the privileges, it rocks! So much easier than the standard client, faster and more powerful by far than phpMyAdmin.
- Rename a column? Right click then rename. Done
- change the column order? Click on the col name, hit Edit, select position from a drop-down list, click Go. Mysql-front will create a temp column in the right place, copy the data to it, delete the original and rename the temp col to the original name.
- want to just use the plain old mysql client? Go to the SQL window and type in your sql - with syntax highlighting! Query doesn't work? Great, it's still there and can be edited as is without pasting or retyping
I thought mysql-front was so good, that I paid the voluntary donation twice for my one copy - the developer seemed pretty surprised. I guess not many people actually donate.
Txbakers,
Good news, the project has been revived.
Anyway, someone has taken over for Ansgar. Ansgar's last version is still available for download. The new version is in alpha/beta or some such thing, but is completely rewritten. I think it's still buggy, so I would stick with version 2.5 for now.
Tom
One quick example - If I want to change one character in a big long memo field, in mysql-front I just go and change the character. Done. Perosnally I think that's great - efficient and less chance for ending up with some copy/edit/paste error.
To each his own.
Tom
