Turning an Access database into a web-based platform
Question: This might be a newbie question, but I have been thinking about it for a while
and here it is:

Our company is interested in creating a web-based program to facilitate the
reports of its field activities. We have been using Microsoft Access as our
reporting tool, but it has been struggling to keep up with our increasing
operations.

We currently have a standard form that our chief field engineers fill out and
email our technical office, where the data are entered into the Access
database. The form basically has information regarding our daily operations.
After the data are fed in, we get reports on how much work we have done and
how much material is needed, etc.

We are investigating ways to make this system more efficient. We want to:

  1. Use SQL Server 2012 as our relational database as we have invested in SQL for our ERP program.

  2. We want our field engineers to input data directly into the system. We are wasting a lot of time right now because the data is entered twice, once by the field engineers into the forms and once again by the people in our technical office into the database. By saving the time that is lost by entering the forms twice, the technical people can have more time to develop the web-based interface.

  3. We want to be able to use road maps and maps in general because we want to see where our operations are geographically. We are a road marking company and being able to play with road maps and maps would make it convenient. I have no experience in this field, but if I can integrate something like Google Maps into the app, it would be great.

  4. We want our web-based program to run on multiple platforms. All our field engineers have PCs and they use Windows. Moreover, it would be a great plus if the application can work on iPhones and Android phones as the field engineers would not need to take out their laptops in that case.

  5. The system might not be inclusive in the beginning, but if successful, we want something that we can build upon.

  6. Because we are not a software company, we will only have 1-2 people developing the interface. So we would want a stable, reliable, easy-to-develop system, where a field engineer can log in, enter data, see what he entered and make changes if necessary, and log out. We don't have high graphics etc requirements.

I have been given the task to start such an endeavor and I am planning to
start learning and writing the code myself. Our plan is to adopt a web-based
system in a year's time. I will then either educate the technical staff to
develop the system as they are doing with the Access interface right now or we
will hire a new software engineer to guide our people.

My experience with coding is:

  1. Introductory Java
  2. Introductory C
  3. Introductory Python
  4. Intermediate SQL and algorithm knowledge due to messing around with the ERP software
  5. Intermediate VBA knowledge due to reports I have created using ERP data

Based on our demands and my skill set, which programming language do you think
would be the best fit? I might not have the right skills for the task, but I
am an avid learner and will put a lot of effort into this.

Thanks a lot for your guidance!


Answer: There are many routes to go in deploying web applications and you must choose
one to ideally fit your team's situation (training, skillset, resources).
While I cannot enumerate the choices here, I can describe your migration needs
and few options.

As you may know, MS Access is a file-server database like SQLlite, compared to
its client-server database counterparts such as MySQL, SQL Server, PostgreSQL,
and Oracle which scale at enterprise levels. Hence, Access is designed to
serve small business solutions. But you can use its data storage (tables);
data entry (forms); date processing (queries); and data output (reports) as a
working model for your web app. The essence to understand is that Access is
both a **FrontEnd** (forms, reports) and **BackEnd** (tables, queries)
management system. Therefore, in transitioning to a web solution, you must
substitute these components accordingly: 1) developing a frontend GUI system
and 2) developing a backend database system.

And the beauty of Access is that it can connect to all the aforementioned
systems in helping the migration process (import/export, linked tables,
ODBC/OLEDB, etc.).

Popular tandems to consider include:

  1. **[PHP/MySQL](http://www.w3schools.com/php/php_mysql_connect.asp)** \- a very cost-effective, open-source route and popular among webhosting sites such as BlueHost, HostGator, GoDaddy, etc. You can replicate your forms with html/css and run your update/append queries, form processing, report output with php/mysql. Google Map APIs provide PHP among other languages (javascript, python, etc.).
  2. **[ASP.Net](http://www.asp.net/)/SQL Server** \- another pair for web applications. ASP.Net is a web application framework built on the C#/VB.Net languages. But using available consoles and IDEs, your team can recreate the user interface and run backend programming. As you know SQL Server and Access works great together, facilitating the integration. 
  3. **WordPress/Joomla/Drupal ([Content Management Systems](http://code.tutsplus.com/articles/top-10-most-usable-content-management-systems--net-6493))** \- an effective streamlined deployment route to manage your website via a control panel dashboard, templates, plugins, and modules leveraged by a team of developers and pre-built platforms. You can save design time with templates and processing with popular plug-ins especially for forms to database.

Still other less extensive routes you can consider:

  1. **MS Access/Office365** \- the newer [Microsoft Office 2010 and 2013](http://blogs.office.com/2012/07/30/get-started-with-access-2013-web-apps/) have been pushing the aim of publishing Access desktop databases as web databases. Providing a URL via an Office 365 subscription, your engineers and technical team can interact with the database through web browsers. So any user on Mac, Linux, or smartphones can work on the database. Some intricate VBA/macros must become web-compatible. Consider this if a full-fledged website is not quite necessary but a simple web tool.
  2. **PDF forms/MS Access** \- Adobe PDF Reader allows users to enter data on fillable forms. The Reader comes free as a downloadable computer program and mobile tablet/phone app. You can design your forms in any format and even built trigger event/validation the [Adobe PDF SDK Javascript API](http://www.adobe.com/content/dam/Adobe/en/devnet/acrobat/pdfs/VBJavaScript.pdf). Your engineers can work in the field and submit via email their forms on any device. Once received you can use the API to upload form data into database with Access VBA. Do note: designers need [Adobe Acrobat](https://helpx.adobe.com/acrobat/kb/create-fillable-pdf-forms-acrobat.html) to build fillable forms.

Finally, do understand moving to a web solution, you must account for
security, user access, responsive design, new technologies/best practices, and
regular maintenance up an above a standalone Access database which currently
resides on your private secured company network.

