What best practice techniques should I observe when building a Microsoft Access database application that will facilitate future scalability?

I have a hard requirement to construct a database and application using Microsoft Access, and business requirements are still very fluid. I anticipate a move from Access to a browser based solution. There are a number of solutions available to move the database to a new platform, so I am more concerned about front end development. What can I do now in Access to design my business logic and UI code to make the future move with a minimum of pain and rework?

Solution

Well, if you write code in c++, but then want to use web code such as JavaScript, then you talking about a VERY different kind of language and programming.

Also if you write code in JavaScript, but plan to use say FoxPro, or say c# in the future, then once again you using a very different programming language.

So at the end of the day, the programming language in Access is VBA, and it certainly not going to run in asp.net.

However, you can dump c# and use vb.net.

If you adopt vb.net, then significant parts of your VBA code WILL transfer over to say asp.net + MVC.

Unless you willing to flat out state that you going to choose vb.net for the asp.net/mvc application, then you question makes zero sense, and has VERY little relevance to asp.net and the code you write.

You can’t use the code you write in Access for the future project, so it really a lame question on your part.

However, if you stating that you willing to use vb.net in place of c#, then YES you can adopt a number of approaches that will allow much of your code to be used with very little changes with asp.net

So the main suggesting here is that since your code will be written in VBA, if you REALLY are asking an honest question here, and REALLY want to have code transfer over to asp.net, then the best suggestion would be to use vb.net with Visual studio.

While forms code in Access don’t transfer over to vb.net, the standard code modules, and ALSO the class objects you write in Access WILL transfer over with VERY few changes. (Assuming you adopt vb.net).

What this means is that you likely want to ensure that “general” code routines are NOT place in the forms, but placed in standard code modules.

However, if you not willing to adopt vb.net as your programming language for the asp.net application, then your question really does not make sense since VBA and c# are so vastly different.

So the BEST choice would be to use vb.net, since then code modules written in VBA/Access will transfer to vb.net with VERY little change. The same goes for class object code you write + create in Access, and once again such class object code written in Access/VBA can be moved to vb.net with near zero changes. So the class objects you write in VBA do transfer VERY well to vb.net

Now if this question was about data structures and tables, then there are other tips, but your question was limited to writing code, and not the database or data system you plan to use. (that would be a DIFFERENT and NEW question as opposed to how to write code in Access that transfers over to asp.net (and the answer is simple - use vb.net + asp.net).

To prevent further downvotes, I will demonstrate this with REAL code.

Suppose you need a routine that after you select a file, you want to check if the file exists in a folder, and if it does, then you append a number.

So if

MyData.zip exists, then the routine is to return

MyData02.zip

This is a common bit of code. The following code from Access works fine, and was THEN USED IN A PRODUCTION asp.net application. Because I chose Vb.net, then I was able to paste in the VBA code DIRECTLY from Access, and use this code in a REAL LIVE production asp.net application. Note how the code is 100% the same in VBA, and that of asp.net

Public Function GetServerFile(strFile As String) As String

    Dim strDir As String
    Dim strF As String
    Dim i As Integer

    strDir = UpLoadFolderPath()

    ' check if file already exist - start increment -N to file name
    Do
        If i = 0 Then
            strF = strDir & strFile
        Else
            strF = strDir & FileOnly(strFile) & "-" & i & ExtOnly(strFile)
        End If
        If FileExists(strF) = False Then
            Exit Do
        End If
        i = i + 1
    Loop

    GetServerFile = strF        ' return

End Function

So the above is RESOUNDING proof of my post here.

The posters question is 100% legitimate.

If you choose vb.net for your asp web application, then many of the class objects you create and write in VBA will move over to vb.net

And in this example, it was not even a class object, but code cut + pasted from a Access application VBA, and simply placed into a code module in asp.net (vb.net allows both class modules, or to simply add a code module like you do in access).

The code runs fine in both Access and in asp.net

The code is used after a file upload in asp.net to prevent file collisions and increment the file name with 02, 03 etc. Thus code written many moons ago in Access is running in asp.net/vb.net, and it runs without any modifications.