Designing web-based front end for database

[JGalletta]

I currently have a fully functioning, shiny, brand new database that my users would like to be web-accessible. I understand some (all?) use .NET to accomplish this. Could the answer-er of this question please outline the following for me:

1. Steps necessary to accomplish this task.
2. Languages to be used/learned (.NET, HTML(5?), etc.?)
3. Simplest methods of reformatting/converting current front end into web-based front end if a simple conversion is possible.
4. Perhaps a step by step instruction on what to do or at least a shove in the right direction?

Using Access 2007-2010 back-end (currently not split from front end - necessary?) & would prefer functionality on Chrome.

My Background:

I designed the current database from the ground up, with full functionality and slightly complex modules (e.g. loops within loops through recordsets.. nothing all that extreme). I have never programmed for web-use. I would need to know where to look and what knowledge to acquire in order to make this project a living and breathing entity of the current database. I would also like to minimize any speed problems associated with running queries via the internet.. if that even is an issue. Anyhow, I would hope that you can assess my skill level from the way I've described things. I have no problem learning new things, and am ready to create another monstrosity if I must. Feel free to go through my older posts to see some examples of the work I've done so far.

Your help is greatly appreciated.

-John

 

[spikepl]

For ASP.NET all you need is at www.asp.net - tutorials, examples ...and you'd need to learn VB or C# to make asp pages.
The philosophy of a web-based application is different from a normal application. If you have never done a web application before, then add some time to your undertaking. Further, the languages are an order of magnitude richer than VBA so there is quite some learning curve there too.

If you are not scared then you are probably looking at 1 -2 month full time to get a releasable tested application running. But some samples from www.asp.net will help. And then you need Visual Studio Express (free from MS). Some HTML basics are necessary too, but that is not a major stumbling block. Anything but a basic application would also call for some CSS and Javascript, but you can learn that along the way from the samples. A slight understanding of what AJAX is about would also be nice, to make a very very nice user interface, but that can wai until you have conquered the rest.

Buy some ASP.NET book from WROX and work your way through it. They are pretty good and come with samples. Google.

I think there are some tool on the web that can do some conversion - not sure whether to ASP.NET or VB.NET. I have never used them so cannot say. I would be surprised if they managed to do a 100% conversion, and then you still need to understand how the whole lot ticks anyway.

 

[JGalletta]

Would you reccomend this one?

Any should do, no?

 

[spikepl]

Cannot say since I haven't done this one. I have worked my way through 3 WROX books and they were all excellent.

One other thing: my current web host cannot talk to an A2007 db. Things change all the time, but to be on the safe side I would stick to a 2003 db or at least check with the ppl where you want to host it, unless you go for SQL server or MySQL. I would recommend to take it easy. ASP.NET is a bite big enough, without also having to deal with the quirks of new db's

Update: The big choice facing you is VB or C#. VB will be much more familiar to you. C# was, a few years back, when I was messing with this, more popular (meaning more examples/problems could be found on the web). I don't know what current status is - presumably unchanged or even more tilted towards C#. C# has the added advantage that it is fairly similar to Javascript. Both VB and C# get compiled into the same stuff, so performance-wise there is no difference. During getting the gist of how the ASP.NET framework works, I reckon VB would be less of a stumbling block. What to stick with in the future could easily become a religious debate - but at that time you can make your own choice.

 

[Pat Hartman]

The users probably should have mentioned the web-accessible stuff up front so you didn't waste your time building with Access because nothing you've done interface-wize is reusable. You can use ACE as the RDBMS for a web app but SQL Server is a better choice. In either case, the tables are reusable but nothing else. Even if you have the most expensive version of Sharepoint available to you, which does support "Access" applications, you'll be starting from gound zero because there is no conversion from a client/server app (which you have) to a web-enabled app (which you want).

Why do the user's want a web app rather than a client/server app? Many times it is just because. The only real reason you would need web functionality is if you needed to support access from outside your network by people who couldn't install the Access app.

For starters, you need to split the app into a FE and BE. The BE stays on the network share but each user gets his own personal copy of the FE which will be stored on his C:\ drive and run from there. Having multiple users opening the same physical copy of the database, especially when it isn't split, is the cause of frequent corruption unless your network is absolutely top notch and never even blips.

 

[JGalletta]

Ok, I think I'll learn a few more things before I decide which format to use, although I'm afraid the current db uses features only available in the newer format. My workplace (I'm doing this for my boss/father) already has SQL server, but this database is not utilizing it. Perhaps more study would point me in the right direction. Regardless, ASP.NET seems like the language for me to learn at this time. I'll be doing most of my focused work on this db during the Winter of 2012, so I'm solely interested in learning the actual languages I'm going to need at this point. Later on, I'll definitely be getting into more detail.

 

[JGalletta]

My boss is trying to avoid purchasing another Office License just to view this database from a remote location (field crew supervisor's truck, distant manager's computers). This is also the first database I've ever designed, so my boss had no clue how worthy of anything it would have been or of the multiple applications the program may be used for, but it turned out to be a home run, so our supervisors will be utilizing its functionality in the field via mobile hot spots once I integrate .NET capability. Also, our business has two divisions, and the information is pertinent to managers at both locations. A web based FE would eliminate the need for VPN or other types of VNC viewing via thin clients, etc.

 

[spikepl]

Enlighten your boss: Access Runtime (which allows to run an Access application without installing a licensed Access or Office) is free (but still requires WAN/LAN connection to home - an SQL server, preferably).

And don't scoff at VNC, LogmeIn and the like. Running a Terminal Server (or Citrix) would allow ppl to logon via WAN, would obviate the need for developing a web app, and could be made available in a matter of days, and not next year, which your web app apparently is slated for.

As Pat mentioned: ask very specifically who needs what and why. All ppl on the LAN can do just finr with Full Access or the free Runtime. It's only the ones off the LAN that might need something extra - they could still make do with Runtime, if your backend is on an SQL server, or they could get into Terminal Server. All this is less effort and faster than a web app developed by a webapp-newbie

 

[JGalletta]

Could you explain how that would work? Because we looked into that and it didn't exactly seem feasible. There's probably something we're missing.

 

[spikepl]

Errr how what would work?

 

[JGalletta]

And don't scoff at VNC, LogmeIn and the like. Running a Terminal Server (or Citrix) would allow ppl to logon via WAN, would obviate the need for developing a web app, and could be made available in a matter of days, and not next year, which your web app apparently is slated for.

Assume all new users are not on the LAN. That's why we we steered towards web-app development. Are you just saying to have a thin client viewed from another computer via a VNC ? What about multiple users? I just don't really understand how you meant for a system like that to work.

 

[spikepl]

VNC requires some computer that they can log into - this is not workable for a mass-issue application but for single individuals. But a Terminal Server can handle many more - it's like logging into your own machine over the WAN (and just get the screen refresh sent via the WAN). There are some posts in the forum about Terminal Server troubles. I worked with access over a terminal server ages ago . We were a small outpost stuck on a slow broadband line, and eventually we got access via a terminal server (which function-wise is akin to Remote Desktop). From all you said so far I would recommend evaluating a Terminal Server - it's just a purchase of some software and perhaps hardware, configuration (hire a body for that) , and then you are more or less done in a week.

For any more details, you need to ask someone else - I am a user and not a systems person. But ask - a phonecall or two, and perhaps you are a hero next week

 

[Pat Hartman]

You may need to convert the BE to SQL Server depending on what your provider supports. Not all support Jet databases (Access 2003 and earlier) and fewer support ACE (Access 2007 and newer). Once you find a provider (start with GoDaddy and ServerIntellect), you can determine if you can stick with ACE or if you need to convert to SQL server. We've written a lot about converting to SQL Server and it isn't really difficult.

You would start by installing SQL Server Express which is free. Then use the upsizing wizard built in to Access to convert the tables. You may need to do this a couple of times until you get rid of all the errors. If you get errors, just delete the SQL server database, fix the Access database and do it again.

The provider will give you a web address for the database, you would use that to link to rather than the local server on your PC. Any user who does not need to do actual development work (which is probably every one but you and a backup) can use the free Access runtime engine to ru the app.

I haven't bought just Access in a while but it is between $100 and $200 per seat. You will spend hundreds of hours converting the app to Asp. Even though he isn't actually paying you, that's huge. If he paid you even minimum wage, it would cost a minimum of $800-$1600 and probably two or three times that given you have to learn a difficult language in the process.

 

[JGalletta]

I am getting paid to do this. Regardless, this project is just a wish at the moment. Our busy season starts in about a week (we're a blueberry farm) and now is not the time for a system upgrade. How about if we look at this from a performance standpoint? Then what is the best solution. Also, if the db is in .NET form it can then be used on a smartphone's browser, right? I'd really like to make things all into mobile apps considering I'll eventually be the owner of the business (family farm). Then all data collection and planning/correspondence can be done remotely. What steps should I take now to make this easier in the future?

P.S. It's quite beautiful to make your job easier before it's even your job. There's so much managerial information floating around our operation that hasn't been utilized and it bothers me to no end. It's my goal to collect all relevant data possible in order to make my future easier and more profitable.

 

[Pat Hartman]

There is nothing to prevent you from having a hybrid application. Some features available from a smart phone others only from the client/server. Do you really want to read reports on your smart phone?

As long as you're getting paid, there's no problem with the 3-1 (or worse) differential between using Access and one of the .net languages. Access is a RAD (rapid application development) environment and does a large part of the work for you. Once you get into another environment, you'll appreciate Access more.