Welcome to our ultimate video guide on how to migrate your Access database to the cloud without coding.

Part 2 will show you how to import your Access data, database tables and table relationships into Caspio.

Summarized Video Transcript

This Access application has a total of five tables.

Those three that we’re importing into Caspio are the Users table, Bugs table and Comments table. The reason why all five tables won’t be imported is because the Filters table and the Settings table are unique to the offline application. We won’t be needing those two inside our web app.

The tables we’ll import are also populated with fictitious data to show you how we can bring them into Caspio. And finally, we’ll show you how to import the relationships between the tables as well.

Since you can’t import your entire Access database, forms and reports will need to be recreated in Caspio.

Splitting Your Access Database

Access allows you to split your database into two files:

  1. One that contains your forms and reports
  2. Another that contains your tables, data and relationships

To do that in MS Access, follow these simple steps:

  1. Click the Database Tools tab on top.
  2. Click the button that says Access Database.
  3. In the resulting popup window, click the button that says Split Database.
  4. In another popup, save your tables to your Desktop.
  5. Name your file, BugTrackingDB.
  6. Click the button that says Split.
  7. When you’re prompted to close some tables, click Yes.

Uploading and Configuring the Access File

Once you’re logged inside Caspio, click the New App link on top. You’ll then be given two ways to begin:

  1. By importing your existing data
  2. With a blank app

Since you already have your Access database, follow the steps below:

  1. Click Start by importing data.
  2. Input Bug Tracking DB when prompted to name your app.
  3. Click Choose File.
  4. Locate BugTrackingDB.accdb in your desktop, then click Open.
  5. Click Next when you’re done.
  6. Click Next one more time when file upload is complete.

You’ll now see a list of all your tables for import on the following screen. As mentioned before, there is no need for the Filters table and Settings table. Go ahead and disable both of those tables by unchecking their boxes in the Source Object column.

Using the Action column, you can specify how you want to bring the tables and data into Caspio. Since you are bringing the Bugs, Comments and Users tables for the first time, select the action called Create New. Other options include Replace, Append, Update and Update Design.

In the Destination column, you can rename the table to something else. Keep the names as is for now and continue to the next screen by clicking Next.

Note that you can do the following on this screen:

  • Toggle back and forth between your tables to see the sample data
  • Include or exclude a specific field that you’re importing
  • Change the data type of each one of your fields

Go through each one of your tables now and make sure your data types are set up correctly:

  • Table 1: Bugs (New)

    • Change the ID data type from Integer to Autonumber. This will automatically flag it as a unique ID.
    • The fields AssignedTo and OpenedBy serve as foreign keys and need to remain as integers. This is because the unique ID being stamped is an Autonumber (from the Users table). Since the Autonumber data type will only generate whole numbers, the integer data type must be used on the related table.
  • Table 2: Comments (New)

    • Change the ID data type from Integer to Autonumber.  A new ID will now be added for each new comment, similar to your bugs table.
  • Table 3: Users (New)

    • Change the ID data type from Integer to Autonumber as well.

Note that these changes can be made even after your import.

Click Import to finalize the upload of your database file.

Once the import is complete, click the Close button.

Viewing Your Table Relationships

Click the Tables object on the left-hand panel of the overview screen.

Here you’ll find all three tables you’ve imported. Clicking Open on any one of them will allow you to view the data stored inside. You can also rename, modify, delete and add fields by clicking Design.

To view the relationships between your tables, make sure you’re in the Tables menu and click the Relationships link on top. Next, include all three of your tables inside the canvas screen by checking all three boxes under In App.

And just like in Access, you can rearrange these tables however you want to display them. You can also see how Caspio was able to import the data, the tables and the relationships successfully.

Creating Additional Tables

All you need to do here is create three lookup tables that you can use as dropdowns for your app.

A quick note before you begin — every single table that you build should have a primary key to identify all the records in the database. You should always get into the habit of creating a unique ID first.

NEW TABLE 1: priorities_lookup

Go back to the main Tables menu and click the New Table link on top, then add the following fields under Name with their corresponding DataType:

  1. Priority_ID – Select Autonumber as the DataType.
  2. Priorities – Select Text (255) as the DataType.

Once you’re done inputting both fields: hit Save, name this table priorities_lookup, then click Finish.

You’ll need to input some values on this lookup table next, so Open this table’s container and quickly add the items below in the Priorities column:

  1. Urgent
  2. Critical
  3. Minor

NEW TABLE 2: status_lookup

Go back to the main Tables menu and click the New Table link on top, then add the following fields under Name with their corresponding DataType:

  1. Status_ID – Select Autonumber as the DataType.
  2. Status – Select Text (255) as the DataType.

Once you’re done inputting both fields: hit Save, name this table status_lookup, then click Finish.

Next, Open this lookup table and add the items below in the Status column:

  1. New
  2. In Progress
  3. Closed

NEW TABLE 3: categories_lookup

Go back to the main Tables menu and click the New Table link on top, then add the following fields under Name with their corresponding DataType:

  1. Category_ID – Select Autonumber as the DataType.
  2. Category – Select Text (255) as the DataType.

Once you’re done inputting both fields: hit Save, name this table categories_lookup, then click Finish.

Next, Open this lookup table and add the items below in the Category column:

  1. Reporting
  2. Application
  3. Performance
  4. Forms
  5. Workflow

Go back out to the Tables menu when you’re done.

You could also create these tables in Access and import them straight into Caspio, but we wanted to show you how to build tables directly inside the platform. Importing tables from an Excel file is also an option.

Configuring Your Users Table

In order to create a login interface, there is one minor modification you’ll need to make. Click the Design link on your Users table to get started.

You can either allow your users to log into the app with a unique username, or do the same using the email field. Whatever preference you have, you can give them login access with any set of credentials.

We will be using the Username field for this example, and add a Password field as well to the table. Simply follow the steps below to make these changes:

  1. Find the Username field and check the box under Unique.
  2. Type in Password under Name at the bottom of your list of fields.
  3. Select Password as the DataType to make the field encrypted.
  4. Make sure the Password field is selected and click the Move up link on top until it’s sitting under Email_Address.
  5. Click Save for your changes to take effect.
  6. Click the Datasheet tab on top to view and edit data inside the table.
  7. Find the the username, johndoe, then click the edit symbol of his Password
  8. In the popup window that will appear, in the text box for New Password, input test.
  9. In the text box for Confirm New Password, input test once more.
  10. Click Change Password.
  11. Close the confirmation message.

Remember that John’s password is now test. We will be using his credentials as we build the app.

See You in the Next Video!

You now know how to import your Access database into Caspio. You also learned how to build new tables and manipulate some of the data elements to maintain application integrity.

In the next video, we’ll teach you how to build a login interface.

Next Steps

COMPARE LOW-CODE VENDORS
NEW FORRESTER WAVE REPORT
See why Forrester ranks Caspio a 'Leader' in low-code platforms in this 2019 report.
GET HELP FROM A CONSULTANT
TALK TO AN EXPERT
Have a vision for an application? Talk to a Caspio product expert and we’ll help you achieve it.
SEE CASPIO IN ACTION
SEE CASPIO IN ACTION
Want to see if Caspio is a good fit for your needs? Choose a date and time for a personalized demo.