MS Access and Excel Migration Tool
Table of Contents
MS Office is a very powerful and popular tool to satisfy your business and personal needs. MS Access helps you to build databases and MS Excel is an excellent way to maintain spreadsheet data. One of the drawbacks in using any desktop application is, when you use it over a period of time and have large data, data portability becomes difficult. It gets stuck to the PC in which the file/application is in. Sharing and collaboration may not be easy too. More often MS Access database require a interface to enter the data and use PHP or any server side scripting to update/insert of manipulate the information stored in it. Writing and maintaining Macros for excel spreadsheets could also become difficult once the data becomes huge and complex.
Zoho Creator, on the other hand is a web based application, which is equally powerful and simple to build online database applications that can be served via the browser.
Zoho Migration tool for MS Access & Excel is a simple tool to upload your existing MS Access applications or XL Spreadsheets to Zoho Creator. You just need to have your MS Access MDB file, and import it to this tool. All the tables are imported with the relationships intact, you could just check them and upload them to Zoho Creator as an application. In case of the excel spreadsheets, you can import the XL file and each sheet becomes a form and corresponding views are created in the application.
- You need to have Microsoft .NET Framework Version 2.0 installed in your machine for the tool to work properly. By default, the windows machine have this framework installed. In case the framework is not installed in your machine, please download it.
- There is no limit on the number of columns while importing data to update records. However, the number of columns should not exceed 70 while creating an app by importing data using Migration tool.
The following screen shots would help you to understand better to migrate from MS Access Databases and MS Excel Spreadsheets.
Open the MDB file using File > Import MS Access Database menu. If you want to import a XLS file then click on Import MS Excel Spreadsheet menu.
Select the File and click on Open.
Once the import is successful, the tool lists the tables in the database, with its Field and their properties. If required, you can modify the Field Types and the Field Properties. Refer Zoho Creator Field Types for more information on each Field type. Refer Field Configurations for more information on each Field property.
Upload the imported file to Zoho Creator using the menu File > Upload to Zoho Creator. You can export all the tables as a new application by giving your zoho credentials.
Enter you Zoho User Name and Password and click on Upload to Zoho Creator. Login to your Zoho Creator account and check out the application listed in the home page. Now that you have successfully uploaded your mdb file to Zoho Creator, you can share it with your users, add and update records as per your requirements. Refer the Quick Start Guide to help you get started with creating applications in Zoho Creator.
You can upload data to any of the Forms in your application. You need to select the table or sheet from which you want to upload the data and select the corresponding Form or you can create a new Form on the fly and upload your data. Then you could sign into your creator account and verify that the data has been uploaded to the respective reports..
Step 1: To upload data to the existing application, import the xl file that has the data and Click on File > Upload to Zoho Creator > Add Data to Application from the Menu. Refer the attached screen:
Step 2: Give the details and click on Upload to Zoho Creator button.
In addition to manually migrating data from your Access and Excel files, you can also do the same through command line (console) mode. The command line mode can be used to automate uploading your offline data with existing Zoho Creator application.The arguments for various mode of uploading is mentioned below.
Create New Application
zohocreator.exe <filepath> <username> <password> -n <applinkname>
Eg : zohocreator.exe c:\myfolder\ordermanagement.csv john mypassword -n order-management
Create New Form
zohocreator.exe <filepath> <username> <password> -e <applinkname> -n <formlinkname>
Eg : zohocreator.exe c:\myfolder\ordermanagement.csv john mypassword -e order-management -n Order_form
zohocreator.exe <filepath> <username> <password> -e <applinkname> -a <formlinkname>
Eg : zohocreator.exe c:\myfolder\ordermanagement.csv john mypassword -e order-management -a Order_form
Add Record (By Shared Users)
zohocreator.exe <filepath> <username> <password> -e <applinkname> -n <formlinkname> -o <ownername>
Eg : zohocreator.exe c:\myfolder\ordermanagement.csv john mypassword -e order-management -a order_form -o Smith
zohocreator.exe <filepath> <username> <password> -e <applinkname> -u <formlinkname> <fieldlinkname> <columnname>
Eg : zohocreator.exe c:\myfolder\ordermanagement.csv john mypassword -e order-management -u order_form Items Products
Add Or Update
zohocreator.exe <filepath> <username> <password> -e <applinkname> -au <formlinkname> <fieldlinkname> <columnname>
Eg : zohocreator.exe c:\myfolder\ordermanagement.csv john mypassword -e order-management -au order_form Items Products
-n refers to new application or new form
-e refers to existing application
-o refers to the application owner's name
-u refers to data update
-a refers to'' adding data
-au refers to add/update
- You can also use authtoken instead of username and the value of authtoken instead of password to migrate your data. The argument is
zohocreator.exe <filepath> -authtoken <authtoken value> -e <applinkname> -au <formlinkname> <fieldlinkname> <columnname>