MS Access migration¶

Congratulations on the decision to migrate MS Access to the Web!

After all, it is moving to the modern JavaScript with Bootstrap and jQuery technology Front End! Which is powered by Python.

Jam.py is not exclusively developed for Access migration though. Just like Django (Python) or CodeIgniter (PHP), is not. Both mentioned products are not specialised for the databases oriented applications, even though one could migrate Access to any of those.

However, Jam.py is specialised. Just like Access is.

Before considering migrating Access (or any other proprietary software), to the Web with the help of Jam.py, please review some answers below. Which might help with understanding what to expect from migration to Jam.py.

Top Migration Questions¶

The MS Access might be a bread and butter for many developers out there. Particularly in some specific industries like the Law practices or similar. And fair enough. It is the right tool for the job.

However, the risk is that in some years to come, the developers will retire. I know, I’m one of them. Hence, where will the business find people to maintain their expensive application? Maybe they already hired the MSSQL database administrator, since the Access developers told them to do so. The DBA’s might be around in years to come, but the Access developers? Or better, THE Access itself?

Hence, the faster we move away from proprietary database AND Front End application, the better. Simply because the technology will be around way after the MS Access is gone. This is business after all, let’s put aside the emotions. The HTML is here to stay. Who remembers the Netscape any more? Or Mosaic?

1. Complacency¶

Success breeds complacency. Complacency breeds failure. Only the paranoid survive. Andy Grove

About being paranoid, many MS Access Front End applications are not encrypted! This means the IP (Intellectual Property), is not protected. Even worse! The VBA code might contain email passwords or similar. For example: https://wellsr.com/vba/2020/excel/vba-send-email-with-gmail/

Is your application cyber safety compliant?

The application might be compliant for some business, however does it run on the Web? Apple Mac? Tablet? That is exactly what we are also addressing with the migration. The Future with the IP and Cyber Safety.

2. VBA?¶

Does your application rely on heaps of VBA for Business Rules (BR)?

The VBA can’t be migrated to the Web. The question is what is it used for? 5000 lines of VBA code might be a dinosaur code! Is it possible to use Python with 200 lines of code? How about 10 lines?

In addition, what might not be recognised is the speed. The VBA just does not compare with Python. The Developers will argue that this is due to the quality of code. Not quite true. The code is just as important as garbage collection, or utilising memory, etc. And this is where Python shines. It replaces massive technological debt with a few lines of code.

For example, the BR might be a way the application authenticates users (ie. a table with username, password, role). Jam.py can reuse the Access tables used for users, so no issues with that. When the database is migrated, the users authentication table is there and we can use exactly twelve lines of Python code, as seen on Demo, to authenticate users with the roles.

Also worth mentioning VBA to Python – 10 Simple Python vs VBA Examples. The article touches on some differences and interesting points from a seasoned VBA developer. However, for building the Jam.py application, JavaScript is used as a Front End. Hence, the real difference is between JS and VBA. For example, showing the MessageBox or conditional formatting and functions.

On that note, the above article touches on VBA code for MessageBox:
Sub MessageBox()
    'Information box
    MsgBox "Hello", vbInformation, "Information"
    'Yes / No question
    If MsgBox("Do you like this tutorial?", vbYesNo, "Question") = vbYes Then
        Debug.Print "They like it!"
    Else
        Debug.Print "They don't like it!"
    End If
End Sub
In JavaScript, Jam.py does that in a similar way:
var btn = item.add_view_button('Set invoice paid', {type: 'primary', btn_id: 'paid-btn'});
    btn.click(function() {
    item.question('Was the invoice paid?', function () {
            item.edit();
            item.paid.value = true;
            item.post();
            item.apply(true);
        });
    });
The code above actually does more then displaying the MsgBox! It will update all selected records in one go. Imagine writing the VBA code for the same task.

The MessageBox with JS in Jam.py is something like this:
item.alert('Successfully sent the mail');
The button in Jam.py is something like this:
var btn = item.add_view_button('Set invoice paid', {type: 'primary', btn_id: 'paid-btn'});
    btn.click(function() {
            -- do something here --
        });

3. Excel?¶

Does your application depends on Excel or some other Office products?

This might be a show stopper, simply because the organisation will not let go of Excel. This might be particularly true within some Financial institution. The good news is Python can read/write Excel document formats. For sure Access has better interoperability with those products. It is the Desktop application after all. However, some operations within Access using Excel, might take a long time, sucking up the resources. Not so with Python and Excel. It is super easy to integrate Excel with Jam.py application using DataTables.

Good resource for using Excel with Python.

4. Queries¶

Most of the “applications” consist of a single hard-coded query or a single linked table.

The above is a quote from Front-End for MS Access migration? It is an old thread with all points still valid. With Jam.py, one does not write queries. It is absolutely possible to do so, if needed.

It is a no-code, low-code or more-code RAD framework after all. And that can open the whole new world for a group of developers! Because each of them can work simultaneously on some other part of application, being the Forms or the Server procedures, everything is instantly accessible to everyone with the right privileges.

Hence, everything is simplified. The Jam.py framework will build complex queries just like Access does, with a few clicks of the mouse. Contrary to the Access, this query, in fact the complete application, will work on any supported database. Develop the application and deploy it to preferred database. Simple. No need to rewrite the queries.

5. Primary Keys¶

Be assured, there are a number of applications out there with no Primary Keys in Access.

The Primary Key (PK), is a must with all relational databases. With no PK, Jam.py cannot reference the Foreign Keys (FK). Not only relevant to Jam.py, Django does not do that either. Since both are sharing similar ORM. So no matter how we migrate Access to the Web, with which technology, the PK simply must exist. This is even true with Access web applications and web databases.

6. Deployment¶

If possible, have the front-ends copied locally on each workstation, for performance reasons.

Again, the same source Front-End for MS Access migration? See how nothing really changed from year 2008?

Some Access Developers would argue that RDP or Citrix or even VPN is the way to deploy the Front End application to the Web. All this technology was around in 2008. Nothing new.

What is new is the Cloud.

Back to topic. Regarding the Access packaging, some people are using one distributable file, sometimes protected with the encryption. Jam.py can do exactly the same. Even more, one can use SQLCipher to protect the IP. Combining both within a portable application, which can run from, for example Windows x64 with no installation at all, is a powerful deployment and secure method. With such approach, the application database might be off-site or even embedded and encrypted with SQLCipher. This method can provide safe and secure access even from a portable drive in an off-line environment, for example with no Internet access at all. Only the local area network is needed for more then one user.

Regarding Web servers deployment, Jam.py application can be deployed in a few minutes on almost any popular Web server. Which can serve thousands of users simultaneously. Jam.py flagship application is supporting 2000 remote locations, each with 10-20 users. There is no need for installation of anything and costs almost nothing.

7. Compile Error¶

The code in this project must be updated for use on 64-bit systems…

Self explanatory error.

8. Cannot open the database¶

Cannot open a database created with a previous version of your application.

Not so self explanatory error.

Conclusion¶

Hope the above sparked some interest. Even though there are probably more reasons why one should or should not try to migrate Access application to the Web. With or without using the migration tool as below is providing.

Where from here?¶

To successfully migrate MS Access to Jam.py, the assumption is that at least some knowledge exist with using the Jam.py Application Builder interface. Or, if feeling confident, one can start creating everything from scratch, and then load the data into the tables manually. We think that the below procedure is way more faster method. And, it does not cost anything.

If agreed, please visit:

https://jampyapplicationbuilder.com/conv/

After the Access file is uploaded and converted with no errors:

link to the Application Builder will be emailed, which is using the sqlite3 data converted from Access instantly available on the Web! It is super easy and takes no time! If not interested at all in building the FrontEnd Application, please click on Export to download the DB as zip file which contains the sqlite3 file. The converted sqlite3 database has a long name exactly the same as in provided the link. This is to increase safety and security for all downloads.

Note

If interested in instantly showing data on the Web, meaning using Jam.py, please continue with the below:

The Application Builder from the provided link will set the Database to “DB manual mode”. This means your new database is safe from changing the structure, like incidentally deleting the table or similar. If you would like to continue migrating Access to Web with the help of Jam.py framework, import all tables into the “Test Import” Group (click on Test Import/Import button), taking care of all needed DB columns correction or missing info. All fields must have a datatype if not already recognized. The “DB field name” should not be touched, the “Caption” is normally MS Access field caption, and the “Name” must be valid JavaScript naming standard, in short, no spaces, special characters, etc. To help out with the process, here are some videos:

Video overview - Jam.py VS MS Access

Video tutorial - How to publish MS Access Application to Web using Jam.py and SQLite3

Video tutorial - Simple CRM built automatically with Selenium IDE in 2.45minutes, demonstrates the Tutorial. Part 1. First project

New style Demo - Jam.py Demo Application

Personal Account Ledger - Migrated from MS Access template

Northwind Traders - Northwind Traders Migrated from MS Access template

After Importing some tables, just replace builder.html with index.html from the link to access your Application. Sometimes the index.html page will not show anything due to Load Balancer issues. Please try after some time. Or start over. One can even set the App password or create users within the Builder. The default App/Builder username/password is admin/111, change it on Project/Users and set Project/Parameters/Safe Mode to restrict access, etc. One can set all lookups to different tables as usual. It should be a fully functional App with all the bells and whistles!

It is important to note that once the Access file is converted and all tables Imported through the builder.html, it is not needed to use this service at all. Immediately use the Export [CTRL+E] option and download the Export file. Then, install the Python Jam.py framework locally or on the server, create the New application, Import the same downloaded file, and point the Application to sqlite database as per creating the New application. That is all.

Notes/Issues¶

This tool is for the database tables only, it does not convert MS Access file with no tables in it. Hence, no need to upload a Front End application, which probably connects to SQL Server or some other database(s). Output will be empty, or it will contain only a few tables. The ODBC linked tables are supported.

The process is expecting that the Primary Key exists on all tables. The simple INTEGER is not a Primary Key. The Log File will indicate this. Please address and fix this issue before attempting the conversion. Use only one Numeric Field as the Primary Key, ie. AutoNumber field.

MS Access might have a special character, space, slash/backslash, dash (-),quote(‘), etc for the table field, as well as table name. The conversion will try to replace those, however, we would advise to fix the issues in MS Access, before attempting the conversion.

The conversion will fail if there are non-ODBC linked tables in MS Access. Please remove any linked tables before attempting the conversion. The ODBC linked tables are supported.

The conversion will fail if Access is using utf-16-li, no need to try again. No log or link will be produced and we suggest fixing Access.

There might be an #Error in the Access field, please fix it if conversion fails, and attempt again. The best is to inspect the Log file, sometimes clearly showing the error. If the log file is empty, something went wrong. No need to try again if Access wasn’t fixed.

Please do not upload confidential, private or password protected/encrypted database. If conversion to SQLite3 is needed for such data, the tool can be provided on demand. Password protected database conversion might fail, no need to try again.

Terms and Conditions¶

“This site and its components are offered for informational purposes only; this site shall not be responsible or liable for the accuracy, usefulness or availability of any information transmitted or made available via the site, and shall not be responsible or liable for any error or omissions in that information.”

Now what?¶

First of all, thanks for trying the migration!

Now that the database is converted to SQLite3, let’s see what we can do with it. The migration utility just speeds up the process of getting the data on the net. It does not build the Front End Application, even though we could easily automate this step.

But then, the Front End look and feel is subjective. If replicating the exact MS Access form as close as possible, it really takes no time to do it manually.

Before doing that, the best is to have a look at the MS Access Relational Diagram. It should clearly show the relations between the tables, as seen on the Chinook database.

For Access application with no Relation Diagram within it, it is still possible to look at the queries design. Each query would probably mean some sort of relation between two or more tables. Hence, in Jam.py, it is possible to create the Lookup fields to other tables just like MS Access does.

Tables¶

In order to use the automatic displaying of data (automatically with CRUD), the table name is used as table Caption! That is, if the Caption was not changed during the table Import.

Hence, if the database table name is django_ledger_accountmodel, then the JavaScript variable Name is also django_ledger_accountmodel! And more importantly, the visible table Caption on the Application is django_ledger_accountmodel as well.

So maybe better to set the table Caption to Account, Bank, etc. See how nicely everything looks like on the screenshot below?

This is also true for any table column during the table Import.

Finally, the application might look like this:

The MS Access linked tables (by ODBC at.al), are now possible to import. There are some constraints though, not all datatypes can be recognised due to the ODBC connection. Of course, the MS Access linked tables are just the issue when using the conversion tool provided above. One can always connect to MSSQL server directly from Jam.py Application Builder and Import the tables.

Using UTF8¶

The above is all well and true for the English language. Or the Latin alphabet. However, using UTF8 in MS Access is supported, but not supported in JavaScript naming standard. Unfortunately, this is directly impacting Jam.py tables Import usability.

Automatic translation to comply with JavaScript naming standard

Because of that reason, when using our site for the migration, all non-Latin alphabet is translated to JavaScript naming standard automatically. The below notes are valid for official Jam.py installation downloaded from the Internet, which does not support the automatic translation yet.

Consider this scenario:

The Table name is 00_COSTES ALFONSO. And the list of the tables continues with 01_, 02_, 03_, etc. Unfortunately, it is also using this as a table name: 00_PRECIOS ICM > GENOCOL_IMAT.

Which just proves that MS Access will accept anything we throw on it. Is this a good practice? It is for the Access though. However, any modern framework will reject such a practise. Including Jam.py.

Importing the above table will produce the below:

Such a table Import might fail. It cannot be imported, and the problems need to be fixed first.

Note

The first issue is the table name starting with 00_, 01_, 02_…, due to a JavaScript naming standard. The solution is to rename the tables to remove 00_, 01_, 02_… in Access.

The migration utility will replace the starting numbers if they exist.

Or, we might use Python for this task:

import sqlite3

connection  = sqlite3.connect("database.sqlite3")
cursor      = connection.cursor()
tableQuery = "select * from sqlite_master where type = 'table' and name != 'Order' and name != 'sqlite_sequence'"
cursor.execute(tableQuery)
tableList = cursor.fetchall()
for table in tableList:
    t_orig = table[1]
    t_fix = t_orig[3:]
    renameTable = 'ALTER TABLE "'+t_orig+"\" RENAME TO "+t_fix+""
    cursor.execute(renameTable)
connection.close()

Secondly, the table field used with UTF8: definición_de_la_patología. Note how the original MS Access field used is Definición de la patología with spaces? The conversion replaced spaces with the underscore automatically. It does that for the table name as well. However, read on.

The Import as such is going to fail, due to JavaScript naming standard for the column Name.

The solution is to rename definición_de_la_patología to definicion_de_la_patologia, or similar valid Name. The DB Field name can stay as is: Definición_de_la_patología.

Finally, the Italian language field from MS Access might look like this:

As seen, Jam.py will happily use the UTF8 for the Caption. As well as the SQLite3 for database table Name and the DB field name. JavaScript naming standard is now fixed for the Name *.

In short, for any non-English language, or non-Latin alphabet, like the below Hebrew language, the Name * must be changed to Latin alphabet, and the rest can stay the same:

Please note the Primary key field as well. Because the Name is in Hebrew language, the Primary Key field is also in Hebrew and Import will fail.

For that reason, we developed the way to translate all necessary items automatically, with no manual intervention needed. The above example with Hebrew language would automatically appear as per below. Due to translation inconsistencies, it is always advisable to double check the translated string. In this example, the “doubt” word probably is not correct, hence leaving to the readers to decide.

If happy with the way the table looks like and DECIMAL(15,4) is changed to CURRENCY data type, we can proceed with the Import. It is possible to immediately check the result on the Internet, by removing builder.html from the browser link.

For this particular example with Hebrew language, the Application needs the support for right-to-left language.

This can be achieved by finding below code in index.html file (on Project/Task):

<html lang="en">

and replacing it with:

<html lang="he" dir="rtl">

Field Captions¶

To expand on UTF8 and field Captions, there is a utility for MS Access to dump all table fields Captions (MS Access optional Descriptions), to the CVS file. Why would we do that? Well, almost all third-party database conversion utilities do not write the Captions to the new database. Hence, the Access information might be lost. This is particularly true for a non English Access Front End Application, where the developers might utilise Captions from the Language table. Hence, the table name would always be the same, only the Captions would dynamically change.

In the above Italian language scenario, the Access Caption might be Definición de la patología. The MS Access Developer decided not to use Captions, as they are optional. Hence, the table Import will use the table name for the Caption, which is fine for this example as there are no many non-Latin names.

However, please consider the Access application with the field captions! This is what we are trying to resolve, so we reuse all the hard work already done in Access.

For example, if the MS Access table DEMO_TRACKS has a field NAME1 and the Caption for the field Track title 1, the TEST.CSV file might look like below and the Python code to update it:

DEMO_TRACKS,	NAME1,	Track title 1
DEMO_TRACKS,	NAME2,	Track title 2
DEMO_TRACKS,	NAME3,	Track title 3

import sqlite3
import numpy as np
captions = np.loadtxt('test.csv', dtype=str, delimiter=',')
print(captions)
# this will output:
#captions = [
#        ['DEMO_TRACKS', 'NAME1', 'Track title 1']
#        ['DEMO_TRACKS', 'NAME2', 'Track title 2']
# .
# .
#    ]
con = sqlite3.connect('admin.sqlite')
cursor = con.cursor()
for table_name, field_name, caption in captions:
    cursor.execute("SELECT ID FROM SYS_ITEMS WHERE DELETED=0 AND F_TABLE_NAME='%s'" % table_name)
    res = cursor.fetchone()
    if res:
        item_id = res[0]
        cursor.execute("SELECT ID FROM SYS_FIELDS WHERE DELETED=0 AND OWNER_REC_ID=%s AND F_DB_FIELD_NAME='%s'" % (item_id, field_name))
        res = cursor.fetchone()
        if res:
            field_id = res[0]
            cursor.execute("UPDATE SYS_FIELDS SET F_NAME='%s' WHERE ID=%s" % (caption, field_id))
con.commit()
con.close()

Note

To prepare the CSV file, use Excel as “Save As” - “CSV UTF” - very important!

Also very important to run dos2unix command on the file in Linux, since it will otherwise do this at the output:

[[’ufeffDEMO_TRACKS, ..]

Note

The good news is, our conversion utility now supports Access optional Caption. They are written directly into the SQLite3 database as comments. All popular SQLite3 utilities, like “DB Browser for SQLite” or “SQLiteman”, can read the database schema with the comments.

The below is an example for Arabic language where MS Access utilises the optional Description, which Jam.py might use for Captions. Each Caption starts with - - , which is a comment in SQLite3:

CREATE TABLE `Location` -- منطقه (`ID` INTEGER PRIMARY KEY AUTOINCREMENT -- ردیف
,
`location` VARCHAR(255) DEFAULT '' -- منطقه
,
`Edare` VARCHAR(255) DEFAULT '' -- اداره
,
`Address` VARCHAR(255) DEFAULT '' -- آدرس
)

We are working towards enabling Jam.py to read the table and field comments and interpret them as Captions while importing the table. As seen, the JavaScript naming standard does not apply for the above table, since the table is using Latin language for everything except Captions.

Reserved Words¶

In JavaScript and Python, there are a number of reserved words that can not be used. However, MS Access might happily use those. Simply add some character to the Name *, and leave the database Table Name as is. For example:

Caption *	Privileges	Active	Order
Name *	privileges_f	active_f	order_f
Table Name	privileges	active	order

Deleted Flag¶

In order to use a full Jam.py functionality with so called Deleted Flag, which basically prevents data to be permanently deleted, we need to add DELETED field to the tables. However, this is not required for Jam.py or the Application to function. It is an nice feature to have, specially if the Foreign Key enforcing is enabled. Meaning, some data cannot be deleted if some other data exist elsewhere as a reference. Deleted Flag totally eliminates the hustle of enforcing Foreign Keys deletion.

We could easily automate adding the DELETED field during the conversion, as it is not required for all tables probably. This is what we can do:

we can add a field to any table with a Python code, as per below. The db.sqlite3 is the database converted from Access.

Note

“Order” word is a reserved word for Python, so we exclude it. There are other reserved words, please consult the Python or Jam.py documentation.

import sqlite3
connection  = sqlite3.connect("db.sqlite3")
cursor      = connection.cursor()
tableQuery = "select * from sqlite_master where type = 'table' and name != 'Order' "
cursor.execute(tableQuery)
tableList = cursor.fetchall()
for table in tableList:
    renameTable = "ALTER TABLE %s"%(table[1]) + " ADD COLUMN DELETED integer"
    print(renameTable)
    cursor.execute(renameTable)
connection.close()

Now, we should be able to select the Deleted Flag as deleted field during the table Import.

Primary Keys¶

As noted in the Top Migration Questions, the Primary Key is a must. However, a lot of MS Access tables use the Indexed key only, and not an AutoNumber. If the key is an Integer, we might use it as an unique identifier in Jam.py.

Note

This only means that we need to manually update the identifier for a new record. More about that later.

For the tables with no indexed Integer, for example the plain indexed text values, the best is to add the Integer identifier.

Consider the scenario where table column grupo is referenced in table material, with MS Access functionality:

CREATE TABLE `agru`
(`grupo` VARCHAR(255) DEFAULT '')

In MS Access, the field grupo is indexed (no duplicates), however SQLite3 interprets this as above after the migration.

The solution is to add ID as identifier and populate the record with rowid, which is built-in SQLite3 function:

alter table agru ADD COLUMN ID integer;
update agru set id = rowid+1;

Now we can create agru_id in the table material and populate records with the ID value from table agru:

update material set agru_id = ( select id from agru where material.agru = agru.grupo)

As mentioned, because it is impossible to add a Primary Key with AutoIncrement to an existing table, the database has no functionality to increase the ID on-the-fly. Hence, we do that manually.

To do that, we just add below code to table grupo on Client Module:

function on_before_post(item) {
    if (item.is_new()) {
        let copy = item.copy();
            copy.open();
            if (copy.rec_count ===0) {
                item.id.value = 1;
            }   else {
                item.id.value = copy.rec_count + 1;
            }
    }
}

The preferred method is to always use a Primary Key with AutoIncrement within the database. However, this would mean redesigning the application or starting from scratch.

Foreign Keys¶

Similar scenario, after the data is migrated, we can create a Foreign Keys within the Builder. Again, the Jam.py utilises lookups for looking up for data.

However, if the Access tables did not use a Foreign Keys, this will be an issue. Consider this scenario:

All columns in this Access table do not utilise a Foreign Key. This is clearly visible when we click on the field. If the Foreign Key is used, the Access Lookup would open. In addition, if we utilise MS Access Database Tools/Analyse Table option, the Wizard would suggest to split the table in a number of new ones. Hence, the migration to Web of such table will not produce the expected result, since the values are hard coded. The solution is to split the table with MS Access Wizard first, then use the migration tool.

Indexes¶

As stated, the migration does not import indexes. This is due to a number of constraints. The good news is once the data is converted, the indexes can be created within the Builder. The good practise is to index all lookups on some table.

Why is this important? Because doing it within the Builder, all indexes are kept and can be recreated on any supported database engine. If the indexes exist within the database only, the Builder would not know about it and they would be lost when moving Application to the preferred database.

Users table¶

If already using some MS Access database table for users authentication, the only thing missing is the Role lookup. It is quite possible that the role is hard-coded in Access, and this is what we are trying to avoid. Use lookups as much as possible.

The process is described in here: How to authenticate from custom users table.

Obviously, it is the best practise to use password hash for storing the passwords. This simply means adding a column with a Password hash text to the Users or similar table.

Note

To Be Continued…