• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Access/Visual Basic to Web Application General Question????

I have a pretty extensive Access/Visual Basic Front-End application that's approx. 11 years old with ongoing development over its life even still today.  It's a tracking/reporting application that's used by the customer service department to track product deliveries and tech services performed at customer's properties in various states.  The transactions are posted to the back end database (MySQL) by a satellite tracking device mounted in the driver/tech vehicles or manually by dispatch using the front-end GUI.  The front-end GUI connects to a MySQL database for the main tables in the application and SQL Server to pick up the Accounting systems tables (that's where the customer account information is stored/maintained).  I personally created the application plus all Visual Basic code.  It is about 60% SQL and 40% Visual Basic Recordset processing, etc.

This all works great but now the client needs to give the area managers the ability to access some functionality of this app (mainly some of the reports for their respective areas) through a custom web interface.  In other words, they're not interested in say a remote connection (VPN, Citrix Server, etc.).  Basically they want to develop a web interface to simulate some of the functions in the Access front-end since it would be impossible to run the Access front-end in a web browser.

It would be practical to try and duplicate 11 years of development all at one time so I recommended that we take a few functions that Dispatch currently uses to produce information for the area managers and start there and get that portion working.   Then expand the web interface as needed/requested duplicating what's already running in the Access front-end.   Therefore, the objective right now would be ....

1.) A web interface where each area manager can log in and display certain statistical information relating to his/her area.
2.) Develop some of the essential reports (2 or 3) at the moment that's currently being run by Dispatch and emailed to each area manager.

The question is .... What web development environment/language would be best suited for this purpose???  

The client already have a web server and my thinking is they will need an experienced web developer and or web development team to pull this off successfully  with extensive collaboration from me because I created the existing front-end.  If they are looking for a web developer ... what should they specifically be looking for relating to skills???  This is kind of a web application and not just a static / dynamic web page to display information.   The reports are pretty involved and is why Access was selected in the onset because of it's graphical reporting object.

Any ideas, comments, suggestions, etc. will be appreciated.

ET


0
Eric Sherman
Asked:
Eric Sherman
  • 5
  • 5
1 Solution
 
ee_reachCommented:
What operating system is their current webserver?
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
That I'm not sure of but it's going to be pretty standard.  Probably something in the Windows 2003 product line.

ET
0
 
ee_reachCommented:
If it's windows then you have some options since you can run php on windows, but you can't very easily run asp.net on linux/unix.

Assuming it is a windows environment, you/they will need to choose which language and environment to go with.

Keep in mind, if they already have a website, they probably already have their own language and environment.  They will just need to be able to access your databases, and they will need to know what functionality to develop.  Most likely there is no reason to require them to change their tools and environment just to access your databases.  (Think of the re-training costs alone to require them to change languages and environments.)

Here are some quick thoughts re your questions:
The two main web language options in a windows environment are php and asp.net.  But they could also be a java house...  Would need to know more about their existing environment to make a firm recommendation.

The two main web servers for Windows are IIS (available only on Windows) or apache (free, open source, can run on either windows or linux/unix.)  But they could also be running WebLogic or something else.   Again, need to know their existing environment to spec their requirements.

For graphics reporting in a windows environment, VisiFire is nice and can probably do most everything you do in Access.  Of course there are other products too.... depending on their existing environment.

So the first step is to find out what their current O/S and environment is.

Then we need to make sure they can get data from your Access database using their current language, etc.

Also if you are going to add more users accessing the Access database, you will have some considerations about whether Access will continue to be the best database for that solution or whether you will need something more scalable.  Access wasn't really built for that purpose initially and you will need to make sure it is still the best solution or if you will have to look for a more general purpose database engine.  (See this article for some considerations http://databases.aspfaq.com/database/why-can-t-i-access-a-database-or-text-file-on-another-server.html.)

Then they will need a description of the functionality and the structure of the database.  This is where you will be playing a key role.  The functional spec will specify the behavior and tell them which fields they need in which database tables.  

Also, someone will need to specify the appearance of the site.  (E.g., everything from the color of background to the font, the links, and the graphics - unless they just re-use whatever they already have for their existing website...)

How many people they will need on their team will depend greatly on how much functionality will be required.  Extracting data from a database and displaying in on a screen is core functionality for web developers.  Also getting user input and making changes/updates/reports based on that input is part of their core functionality.  But understanding the underlying algorithms that perform various calculations is domain knowledge that they will need from you.

Probably once you are able to describe the functionality to their lead developer, they will be able to go off and estimate the amount of work and how many people they need on their team to deliver the project in the timeframe required.

Well, this is long, but I hope it helps.

ee_reach
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks for the comments ee_reach ... very helpful and is kind of what I was thinking.

My original post was long and you may have missed my comments where I described the Front-End / Back-End setup of the existing application. It's a Access F/E that connects to a MySQL and SQL Server B/E database.  I converted the Access database to MySQL a few years ago because the client had discussed future web enabling some of the functionality.  The back end databases are already sitting on their web server.

My concern is the current app is well designed including the data entry forms for the users and especially the custom reports (some are DOT compliance forms) and will have to be duplicated exactly.  This is a chemical company with tankers hauling some hazardous materials around.  I know the client very well and they will want the web app to function for the most part exactly like the desktop front-end which may present some challenges.

You are correct in that an Access database is not designed to run on a web server but that's not where Access strengths are.    I agree with the comments posted here by another user a few months ago ...

"MS-Access is about the integration between the front end and back end all wrapped up with a really nice development environment.  Oracle, Sybase, SQL Server and MySQL are all fabulous databases but the front end tools are simply clumsy as can be and fall miles short of the MS-Access system most likely due to the fact that the others are by nature not state aware.

The power of MS-Access comes from the multitude of controls available AND the ability to use extensive visual basic to fill in where automatically generated function falls completely short."


I have yet to find a tool that can surpass MS-Access as a FRONT-END development tool.   That's really no joke either!!!

As in this case ... PHP is probably an excellent web language but it's has no report writer.  Now you will have to find a tool to create reports and data entry forms, etc.

It's not a complicated application but I don't want to get into a situation where it's a constant battle between the web developers and the current application as you can see ... trying to break down 11 years of development where a web developer who does not know the client's business logic can understand will be very time consuming.  Sometimes, it's probably faster and more efficient to just do it yourself.  I guess that's what I was also thinking as a second alternative.  Just learn a web dev. tool and build the core reports, forms, etc. but right now, it just seems to be individual tools like PHP and not just one development environment like  Access/Visual Basic.

Thanks for your input.

ET  
0
 
ee_reachCommented:
Hi ET -

Thanks for your clarification and follow up.  I definitely understand your point of view.

Also, I used to work for a company where we did research and system design for DOT, so I can relate to your concerns there as well.

In the web world, they probably would not view a database as a front end tool.  There is a clearer distinction make there as the web browser does a lot of what you would consider front-end work.

You may indeed find it faster, especially in the beginning to write some of the code yourself.  However, there will come a time when you will be needed to extend your VB/Access apps while someone else works in parallel on the web version.  At least this is me putting my project mgr and archihtect hat (20+yrs experience) on for a moment and looking at it from other angles, playing devil's advocate, etc.

Also, you yourself would have some ramp up time in order to learn to build strong, robust websites.  Sure, you can grab a tool, but even then there is still usually at least three tiers to a web app.  1.)Database, 2.)  business logic, 3.) Presentation layer (a.k.a web page.).  It takes time and experience to understand and incorporate best practices for the web, as it is a different sort of critter than the more unified desktop apps.

Re your comment about PHP, a point is that your client has several technologies to chose from.  They are not limited to php - it could be ASP.NET  or it could be Java.  The latter both have strong reporting capabilities.  But also not to sell php short - there are many third party reporting pkgs that are probably suitable to your needs.  E.g., PHP Report Maker or mysqlreports.com's Smart Report Maker, etc,etc.  And many php tools are likely to be freeware/open source.

If a unified environment is desired, obviously .NET is an technology to be considered.  And .NET has two architectural approachs.  The first, MVC (Model-View-Controller), and the second, which is forms based.  My guess is that if you have 11 yrs developing this app in VB/MSAccess, then using Visual Studio and Forms for VB.NET would feel very familiar to you.  And in the past few years, a lot of open source/free tools and technologies have been built to meet the needs of Forms based .NET developers.  So  that is one option to pursue.

But I am sure your client's existing web team will already have an environment and techology they are already using for their web apps. However, they will be dependent on you to explain what is required.  It is a real opportunity for you to take a leadership role, if you have an interest in that sort of thing.

Well, I'm going to close now as I have some errands to run and it is quite late here.  But I wish you well in your new project.  I can see you have given it a lot of thought and you have a heart-felt investment in the main application, so I am sure you will make the whole project a success no matter how it all shakes out in the end.

Kind regards and best wishes!

ee_reach
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks ee_reach, your input is most welcomed and "Spot On" as the saying goes.  Your comments actually jogged my memory and pointed me in the right direction.

<<<<<Also, you yourself would have some ramp up time in order to learn to build strong, robust websites.  Sure, you can grab a tool, but even then there is still usually at least three tiers to a web app.  1.)Database, 2.)  business logic, 3.) Presentation layer (a.k.a web page.).  It takes time and experience to understand and incorporate best practices for the web, as it is a different sort of critter than the more unified desktop apps.>>>>>

EXACTLY -- I agree 100% which is why I advised in the onset ... you will need a web development team to pull this off successfully.


<<<<<If a unified environment is desired, obviously .NET is an technology to be considered.  And .NET has two architectural approachs.  The first, MVC (Model-View-Controller), and the second, which is forms based.  My guess is that if you have 11 yrs developing this app in VB/MSAccess, then using Visual Studio and Forms for VB.NET would feel very familiar to you.  And in the past few years, a lot of open source/free tools and technologies have been built to meet the needs of Forms based .NET developers.  So  that is one option to pursue.>>>>>

Yes, this is the part of your comments that jogged my memory.  When we integrated the satellite tracking system into this application a few years ago... the IT manager at that time was also a web developer and he used the .NET platform.   Basically a little web application that polls the satellite system and post the transactions into the MySQL tables that were submitted by the techs/drivers from the device in their vehicles.  So I know they are using the .NET platform.

Myself along with the IT manager (who is no longer employed there) estimated at least a year of development time to complete such a project.  Someone probably thinks the Access/VB application can just be copied and duplicated on a web server!!!!

Thanks

ET

0
 
ee_reachCommented:
<<<Someone probably thinks the Access/VB application can just be copied and duplicated on a web server!!!!>>>

LOL!!!!!!!!
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Yep ... and 1 year was really a conservative estimate.  Actually, my original recommendation which would probably be a lot less expensive and less development time was to allow the field/area managers to access the current application as it is (there are several options) rather than trying to re-develop it into a web application.

IMO, from a financial standpoint, to contract a web development team then contract my services for the next year or so to break everything down is going to add up to some big numbers pretty quickly.

Some clients are like that ... once they see the latest technology, concepts or methods all of a sudden they have to have it (running in a web browser, integrated with Google apps, etc.) not considering the development time/cost.

All I can do is make recommendations in this cast but I think the .NET platform is definitely the way to go.

Thanks,

ET

0
 
ee_reachCommented:
I definitely see what you mean.  Let me know if there is anything I can do to help.  I've been developing software for a long time and have seen a lot of platforms come and go.  You can reach me through the "Contact Me" button in my profile.  (I think it is actually Profile->Hire Me->Contact Me.)  Kind regards --ee_reach
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Ok, thanks ee_reach.

I will contact you if the client insist on web enabling certain functions within the current application and cannot find a reasonable rate to do it.  Maybe we can work something out on building some small sample apps, etc.

ET
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now