Web-based Database Applications

[spacetanker]

Greetings. I am hoping that one of the many smart people who frequent this forum will be able to offer advice on my quandry. Here goes...

We are a small sales agency running Windows Server 2003. For the past several weeks I have been wrestling with the deployment of a VPN to enable off-site access to our Microsoft Access database. The fact that we have several off-site users running Windows XP Home has complicated this process and forced me to consider a Web-based deployment to enable a broader audience access to this information.

We have a website being hosted by JaguarPC. At present it is very basic and does not have any database capabilities. One person in our office is quite adept with Website design, but his knowledge does not extend into this field much. I have a limited knowledge of computer programming with C++, PHP and such, but have an excellent knowledge of BASIC, Visual Basic and Access database programming.

For those of you who have trod this path before, where do we begin? I have a few references on Apache, PHP and SQL and have dabbled a little with them, but it seems to be a very convoluted subject.

The end result of this project should be a web-based portal requiring user authentication followed by a menu of pages presenting forms for display and updating of information as well as reports based on the data.

Thank you in advance for any assistance you might be able to provide.

[Gwaihir]

I admire your energy to take on such a conversion over that and I'd take the PHP MySQL approach over your MS server any day, but only if I be working from scratch. It sounds to me like you've got quite the system set up already, with an extensive VB scripted front end to your database.

Are you sure there is no VPN software on the market that will run on XP Home? If so, could tunneling through SSH perhaps help you out? I tunnel a lot of things from my Win XP (pro) box over an SSH connection established by Putty (excellent free SSH client). I tunnel to a Linux box though. I never looking into SSH servers for Windows, but I'd be surprised if there were none. Or, how many XP home users are we talking? How about just giving them an upgrade?

One more note: Access? It isn't really made for any serious databases, are your sure you can trust it with so much vital data? You can switch to MS SQL server on the back and continue to use your current Access based front end.

[jason]

I too admire your desire, but like Wim, I have to offer you a word of caution. I am a freelance web developer. I've been developing database-driven websites for several years, using ASP (Windows) and PHP (Linux and Windows) mostly. Given my experience, I can build a basic application fairly quickly, but more complex ones still take a fair amount of time. One of the things that I've given thought to is converting my billing system (currently commercial software) to a web-based system (custom programmed). While this would make things much easier for me, the amount of time and effort it would take to build exactly what I want just can't be justified right now. I'm better off taking on client's projects that will make money and settling for a billing system that is a little less convenient to use.

There is VPN software out there that will work on XP home. One of my laptops is running XP home and I use my employer's VPN software on it all the time. We use a Cisco VPN, but it is an enterprise solution that I'm sure is quite costly. I'm sure there are solutions that exist for small businesses, though.

As Wim suggeted, SSH tunnelling can be an excellent way to create a "mini-VPN" and this has thus far been my solution to accessing my billing software from remote locations. My setup is basically this: I have Road Runner cable service to my house. That runs into a Netgear router and I have a Windows box connected to that router that runs my software. I've installed OpenSSH for Windows on that box and told my router to pass any requests for port 22 on to it. I've also set the router to register my current IP with DynDNS.org so that I can have an easy to remember name to connect to that is imune to changing IP addresses. On the remote side, I use the shareware Internet Secure Tunnelling (but PuTTY can do the same thing) to connect to my Windows box with Remote Desktop, where I can run anything as if I am sitting in front of the box itself.

I'm not recommending that you run your app through remote desktop. I'd suggest (as Wim did) converting from Acess to an SQL server back end and setting up tunnels to access that server on your user's home machines. Then give them the Access front end set to connect through that tunnel and they'll be set.

Just some thoughts...

--Jason

[spacetanker]

Thank you for such a timely reply; I do appreciate it.

At present I have deployed only one small module of what is intended to be a very robust and comprehensive application. By design I intended to roll it out incrementally, adding new modules as time permitted me to develop, test and deploy. Thus, I am really not terribly deep into the process of developing the app. The VPN issue has, for the past few weeks, commanded the majority of my time since having the database ready to go really loses much of its value without being able to access it remotely.

Insofar as VPN client software goes, I have been looking into that. Much of what I have found is either expensive, requires monthly fees or hardware or some other such catch. These really are far from ideal solutions though, and requiring outside sales personnel (who are not truly employees but are paid on commission) to upgrade to Pro is likely to be very problematic. I looked at OpenVPN but that seemed to be very complicated and if I have to learn to program something I would rather it be something that will help me down the road.

My database programming experience runs all the way back to dBase, so Access was the next logical progression. Along the way I have dabbled with SQL and have succeeded in popping the Access DB over to SQL in the past. I fully expect that in order to do web based apps that I will have to get intimate with MySQL, so that is no problem.

That said, I am even more convinced that the way to go is to bite the bullet and move this app over to the web immediately. Where do we begin? I am looking for some of the shortcuts you guys have found that perhaps took you a thousand years to learn but now only take a week to do (exaggerated).

Thanks again!

[spacetanker]

Jason, thank you for your comments. I was responding to Wim as your message posted.

You mentioned DynDNS.org. What purpose would that serve? Our web site has a static IP address at present. Help me connect the dots, please? Is this because your home Internet connect has dynamic addresses?

In the past I have converted Access database back-ends to SQL as I was toying with developing Web-based applications. So if I were to convert my current database to SQL and place it on the server then I would only need OpenSSH and Internet Secure Tunneling to access that data?

I will do a little looking into these two programns today and will follow up. On first blush it does not seem to be intuitively obvious as to their use. How difficult is it to set up such a system?

Matt Angove
Integrated Sourcing

[jason]

I do have a dynamic IP at home, thus the need for DynDNS. I figured it wouldn't be necessary in your setup, but I was just explaining how my setup works. You could skip that step.

As far as the MySQL/SQL server debate, if PHP and MySQL kind of go hand-in-glove, but MS SQL Server will also work with PHP. Both have their strengths and weeknesses. Personally, I'm a MySQL fan, but I don't have a whole lot of MSSQL experience, so I can't make a very good compairison.

If you choose to continue with teh Access app, then yes, allyou'd need is a server running OpenSSH and your clients would need to run the tunneling program. Setting up tunnels can be a little difficult at first, but I'm willing to help you get started with that if you need the help.

If you go the web route, I'd suggest getting familiar with PHP's PEAR repository. There are a whole bunch of free classes that you can use to build powerful web apps in there. Some taht I rely on quite extensively are DB (database abstraction) and HTML_QuickForm (forms). Using DB to access your databse is much easier than using the PHP native functions, plus you get the added benefit of platform independence--if you chose to switch from MSSQL to MySQL or vice-versa you'll theoretically won't have to change you code. HTML_QuickForm helps you automate the creation and processing of forms.

Without QuickForm, when you wnat to do something edit a record in your database, you have to pull that record out and stick all of the values into your form. This is OK when you just have text fields because all you have to do is insert things into the forms value:
echo(" <input type=\"text\" name=\"LastName\" id=\"LastName\" value=\"{$record['LastName']}\" />");

but it becmes difficult when you start dealing with things like <select>s and you need to set the proper "selected" attributs. QuickForm will do all of the dirty work for you--just tell it the fields you want and the default values (which would be the values you pulled from the database) and you're done.

I'd also highly recommend working with templates. Templates let you separate the design and functionality of your app, so if your web guy redesigns your site he doesn't need to touch (and potentially break) your code (you can also use templates to make a design change once and have it reflect across your entire site). There are a few template classes available in Pear, but I haven't used them. My favorites are patTemplate and Smarty. Smarty is very powerful, but it can be difficult to learn. patTemplate is much easier to learn but not quite as powerful.

Hopefully this will set you in the right direction. I'm sure I'll come up with more pointers. When I do, I'll let you know. Of course, if you have any questions, feel free to ask.

--Jason

[Gwaihir]

In the past I have converted Access database back-ends to SQL as I was toying with developing Web-based applications. So if I were to convert my current database to SQL and place it on the server then I would only need OpenSSH and Internet Secure Tunneling to access that data?

And Access on the client to run your front end, just like in the VPN solution.

If indeed this is to be a relatively large pool of outside contractors, that Access requirement is probably just as big a hurdle as the VPN one. Together they will lead to a small but undeniable stream of support requests for the client side setup as well. Bringing it to the web will remove all of that.

To do so you've basically got two options. You can either stick with your windows server, on which you'll be able to do a lot of things with the VB you're used too and could fiddle with Access' options to show stuff on webpages, running Access on the server side (can't say I'm overly fond of that Access to web bit for a solid solution). Or you take the plunge and build your front end in PHP or Perl, which once you converted completely could host here at JagPC as well. The database is not the bit to worry about: MS SQL, MySQL.. it's both a solid SQL server and not a world of difference.

One security note about hosting here though: personally I wouldn't feel good having very sensitive information such as credit card numbers stored in a shared environment. If you're dealing with that type of stuff I would advise to get VPS or dedicated. Personally I'd also send Jag some questions regarding who exactly has what access to these systems as I have no clue how screened the junior techs working from abroad are or not.

[Jaded]

I would recommend a mixed static/dynamic setup. Then you can maintain a more secure LAN and all you have to worry about is creating the interface for the DB or just have your sub-contractors upgrade (When two major insurance companies told us we needed to support a certain type of format...we did. lol)

You can then disable, backup, repair your DB on site.

[spacetanker]

An update...

I have contacted a local computer repair service that specializes in networks and asked them about the chances of getting thw VPN "unstuck" for XP Home. They agree that it shouldn't have been such a problem; I figure it is one of the twelve zillion checkboxes in Server 2003 that is the problem. I expect them by this afternoon, when hopefully I can get that issue resolved.

While that doesn't eliminate the need for this dialog - since eventually I want to migrate to a web-based system - it will at least take the immediate heat off the matter. I'll be back in touch to resume this conversation once I have a chance to chew more on what has been said already. I put on my PHP/Apache/SQL hat this weekend.