Access database to SQL with web based front end

Question

Existing Access Database Software program needs to be migrated to SQL database and need the front end to be web based for Local and Remote Users.

I would like to discuss what are the possible options or recommendations over here.

Answer 1

If you’re used to Access, then it going to be rather hard going back 20 years in our industry and hand coding and laying out forms with some web markup language by HAND. (the word crazy comes to mind here!).

I can’t imagine there are folks suggesting here for hand coding the layout of your web forms! 25 years ago people coded their forms for computers by HAND in languages like c++ etc. Then products like Access came along in which you lay out the forms with a GRAPHICAL DESIGNER! (and NOT have to write code to build the user interface layout)

I would recommend asp.net and VB.net. There reason for this is “many” but ONE REALLY nice feature is that you can in most cases take a VBA module of code and it will 99% work just fine in vb.net. The vb.net language is NEAR identical in syntax to VBA (we going from VBA to vb.net – so any “tricks” etc. you learned in VBA will in most cases work in .net – this is certainly NOT the case when going from vb.net to VBA – but IS the case from VBA to vb.net).

Let’s say in Access you want to build a form with a button, and a text box that fills the text box with the numbers 1 to 10.

The VBA code would look like this:

Dim str As String
Dim i As Integer

For i = 1 To 10
   str = str & i & vbCrLf
Next i
Me.Text1 = str

When you run this code, your form will look like this:

Note how we did not HAND CODE the layout and placement of the buttons on the forms.

Now, fire up visual studio and create a web site. Now add a web form. We AGAIN get a Access like GUI designer without having to write code to layout the web page by hand! (by hand???? – are folks kidding me???).

Simple Drag + drop a button onto the form. Now drag + droop a text box and you are DONE!!!

Now for the 1 to 10 code? Simple cut + paste the above VBA code right into the vb.net code editor. You get this:

    Dim str As String
    Dim i As Integer

    For i = 1 To 10
        str = str & i & vbCrLf
    Next i
    Me.Text1.Text = str

In above we ONLY had to change the last line of code and add .Text to the code!

And this is the resulting web form.

Really taking into account that you are an Access developer?

You likely want and need a visual forms designer that you do not CODE BY HAND! – this will kill any budding Access developer.

And as above shows, you get to use a “nice” language without all that scripting stuff – the code editor and syntax is near identical to Access.

I am hard pressed to suggest any other approach then a VERY similar approach to how you develop desktop software. Asp.net + vb.net will give you an environment that is closer than any other development tool since your coming from Access.

Answer 2

I'd go the open source way. For example MySQL with PHP/Python/Java, or Node.js in conjunction with something like LoopBack(http://loopback.io/) — a very cool and powerful rest API framework. LoopBack works with most relational databases, and even non relational databases like mongoDB. Of course there are other possibilities as well; depending on your needs and the size of your organisation. You should research on the platforms and frameworks that suits you needs. You don't want to spend unnecessary time and money on heavy systems that could be overkill for what you need. I hope this was useful for you :)

Good luck!

Answer 3

Depending on your Access version, you should have an upsizing option. Click on tab-- Database Tools. Click on SQL Server in the Move Data area of the ribbon. The wizard will walk you through the process.

You could also link your SQL Tables to the access DB and run update queries against the attached tables.