Migrate MS Access Data to the Web in 9 Minutes

Full Transcript of Video

In this video, I’ll show you how to migrate your MS Access data and make it accessible via a web application

To start things off, let’s take a quick look at the data we’ll be working with.

Here we have a simple access database. I have a list of customers and a list of orders. You’ll see that I have a form here where I can add orders to my database and a form to add new customers. And...I have a little Visual Basic Script here that sends me an email when a new order has been added.

Okay, so that’s my starting point. Now, I want to convert all of this into a web application. The first step in migrating your access data to the web is...getting it out of Access, because MS Access is a file-server type of database. Instead, you need to move the data out of Access into a client-server database such as MySQL or MSSQL. I won’t go into all of the reasons why as that would take too much time, but just know that if you want to migrate your Access data to the web, you must first move it into a client-server database. Once you do that, you can build web applications over that data...which I’ll show you in this video.

Now, there are many ways to get your data out of Access, as you can see here. If you have a database setup, you can export directly to it using the ODBC connector, or I can export to XML, or a number of other methods. For sake of simplicity in this video, I’ll export the data using Excel. I could use any of these other methods, but Excel is the most straightforward. I’ll export each of these tables into different Excel sheets.

Now that I have my data out of Access, I can build web apps over it. I’m planning to build a web application that mimics the capabilities found in Access, and add a couple of useful features. I’ll create an app that lists my customers, and then lets me drill into customers and see their orders. Of course, there will be forms to add new orders or customers, and I’ll even add a workflow element that will send an email every time a new order is added, and will add a secure login to make sure only certain users can access the data. Sounds good? Let’s get started.

I’ll be using the m-Power development platform, since it makes the process so easy.

The first thing I want to do is build an application over my list of orders from my Access database. In m-Power, I’ll need to name my application and choose a template. The template is basically a customizable, prebuilt web application. It includes the layout and features, but also lets me customize it however I wish.

I just need to tell m-Power where my data is and how to sort it, which we’ll do now. My data is in that Excel file that I exported from access, so I’ll import that here. Then m-Power uploads this data into a MySQL database table. Now, m-Power works with any relational database, so I could jus tas easily use MsSQL, Postgres, iSeries, you name it, but I’m working with MySQL in this video. Once I’ve uploaded that data into a database table, I can update the field names. Now I just need to tell m-Power which fields I want to include in my application.

Okay, so now I can move on to the last step where I need to tell m-Power how to sort this application. I’ll sort this one by order number and move on.

Now, I can add calculated fields, external business logic, or filters to this application but I don’t need any of that this time so I’ll move on to the build step. I’ll just click build and wait while m-Power puts everything together for me. You’ll notice that we’ve gone from an Access database to a web application built over that data in just a few minutes...all without any coding whatsoever. Now that it’s built, let’s check it out.

As you can see, it’s the same order data that you saw before. The big difference here is that I can view these orders from anywhere whereas before, I was confined to access. It’s the same data, it’s just more accessible now--I can view all of my customer orders here, and even add new orders up here.

Now, I don’t love this form here so let’s go fix it up a bit real fast. I’ll open up m-Painter, which is m-Power’s visual editor. Here I can update the look and and feel, add charts, maps, and so much more. Right now though, I want to touch up the form. Once I open up m-Painter’s form editor, I can rearrange this form how I want it. I’ll organize the fields so the form is more compact and easier to manage. In the form editor, I can split rows, add or remove fields, and rearrange fields however I want using simple drag and drop. Once it’s done, I’ll save the form. Now, let’s check it out. It looks much better now doesn’t it.

Let’s add a new order just to demonstrate that it works. I’ll just add a new order here using dummy customer data and click submit. Now, you see that new order appears in the list.

Now, let’s say that my salespeople are in charge of adding new orders, but I want to have an email sent to the order processing department when a new order is added.

m-Power has a workflow element that makes this process easy. I’ll open up m-Power’s workflow designer here, which let’s me set up workflows based on certain conditions. In this case, when a new order is added, I want to perform an action. I can set it up to do all sorts of actions but this time let’s send an email. I then need to set up the email that I want to send. I have to input a recipient, give a subject, and create an email body. The nice thing is, I can add database fields within this email body. So, I can send all the details of the new order along with this email. Once that’s all set, let’s go back to the form and check it out.

I’ll go in here and add another order real fast. Once I hit submit, it should automatically send an email to the email address that I specified. Let’s check that out. Great! As you can see, the email is here. Once I open it up, I can see the order details from the order I just submitted. So this is working great so far.

So, now I have my order data on the web, complete with an automated email notification when a new order is submitted. Now, let’s add my customer data and link it all together. I’ll go back through the same build process that I showed you before, but this time I’ll upload customer data that I exported from Access. I’m rushing through this build process because it’s the same thing as I just showed you. The only difference here is that I’d like to connect this to the previous application, and I can do that through smartlinks. A smartlink connects two applications together and passes key data automatically. In this case, when I click on a customer it will let me drill down into only their orders. Now that I’ve added a smartlink, let’s build this app. You’ll notice that this was the same process as before, and that’s again thanks to the templates which really standardize the build process.

Okay, the application is ready, but I want to make a quick change in m-Painter before running it. If you remember, I added a smartlink that will drill into a customer’s orders when clicked. By default, that smartlink is added in a separate column, but I want to add that link to the customer’s name. Once I do that, I can remove the extra column. Let’s save this and run the application.

I now have a list of my customers, just like we had in access. As you can ssee, it’s the same data. The nice part, is now I can click each customer’s name to drill down into their orders. The application is smart enough to know which orders to display.

So, there we have it. In just a few minutes I migrated data out of access and turned it into a couple of connected web apps. Let’s do one more thing before I wrap this up. Now that this data is on the web, it’s currently available to anyone with this link. But, what happens if you only want specific people accessing this data? I can turn on application security within m-Power, which will force users to login in order to use the applications. Of course, once security is enabled, I need to add an authorized user. I’ll do that really fast here and save the user. Now, when I try to use that application, I’m forced to login. Of course, I can add even more security features in here, but that’s not required for these apps. I just wanted to show you how easily you can bring your access data to the web, without sacrificing security.

So, there we have it. In under 10 minutes we’ve taken data out of of access, turned it into two connected web apps, created a workflow notification piece, and even added security. All this without coding.

If you’d like to learn more about m-Power or set up a demo, visit us on the web at mrc-productivity.com. Thanks for watching.