iNotes4You's Blog

Microsoft Access is the worldwide most powerful system for developing database applications with forms, reports, queries and VBA modules. An acceptable performance is ensured for databases up to 2 GB and a number of records up to 100,000 per table when using a state-of-the-art PC.

Microsoft Access 2010 Specifications
I myself develop databases in a Frontend-Backend design mainly for auditors since about 25 years with more than 2,500 licensed users. My experiences are that no other system allows you to develop applications, add features, and fix bugs as efficiently as with Microsoft Access.

So let’s have a look at a client which allows to use Microsoft’s proprietary database file format (.accdb or .mdb) on Apple’s iOS devices.

Impathic LLc, USA offers the Microsoft Access Mobile Database Client at Apple’s App Store.

Microsoft Access databases are Stand-Alone Files, and so the database file must be transferred to your iOS device in order to open it in the Access Mobile Database Client. This is in contrast to many other databases, which connect over a network in ‘Client-Server’ mode. While having your database file on your device prevents the ability to work on the data concurrently with other users, it also allows you to work with your database in a disconnected environment, such as on an airplane.

Transfer to an iOS device …

How to transfer a Microsoft Access database to an iOS device …

Transferring an Access Database via iTunes File Sharing

• (1) On your desktop, locate your “.accdb” or “.mdb” database file.
• (2) Plug your iOS device into your desktop or laptop using the USB cable and open iTunes.
• (3) In the left panel of iTunes, locate and click on your iOS device name.
• (4) At the top of the screen for the device, select the “Apps” tab.
• (5) Scroll down to the “File Sharing” section at the bottom of the screen.
• (6) Select “Access” from the list of apps.
• (7) Drag your Access database from your computer into the file list.
• (8) When you next launch Access Mobile Database Client, you will see your database in the list of available databases.

You may also transfer a database from a cloud provider offering a client app for iOS devices which supports the ‘Open in’ command or you can send the database as an E-Mail attachment.

Transferring an Access Database from the cloud

• (1) Sign up for a free account at http://www.dropbox.com if you don’t already have one.
• (2)Upload your “.mdb” or “.accdb” file to your dropbox account using their desktop integration program.
• (3) Download the free “Dropbox” app from the Apple iTunes App Store, launch it, and log in.
• (4) Browse to your database file and tap the forward button , then select “Access” as the application to open the database with.

  Notes
  When you first select your database in Dropbox, the app may say “Unable to view file”, but you should still be able to tap the forward button to open it in Access Mobile Database Client.

  Modifications made to your database will not be saved back to your Dropbox account, but instead will be saved locally on your iOS device. To get your modified database back to your desktop, you need to transfer it back using the iTunes File Sharing method, or by tapping “Edit” from the database list, selecting your database, and then mailing your database back to yourself, or opening it another application (such as the official ‘Dropbox’ app).

Transferring an Access Database via E-Mail attachment

• (1) Tap on the E-Mail attachment (an accdb or mdb file).
• (2) From the ‘Open in’ menu choose ‘Access’.

The full feature description …

The mind map created with the app iThoughts for iOS contains all features of the app Microsoft Access Mobile Database Client. It can be downloaded here:

Microsoft Access Mobile Database Client

Map as a PDF

Map in ITMZ file format (iThoughts)

Map in MMAP file format (MindManager)

Map in XMIND file format (XMind)

Impressions of the application …

See this annotated slideshow to get an impression of the user interface.
Tap on an image to see the navigation bar.

This slideshow requires JavaScript.

The pros …

• Support for Microsoft Access 97, 2000, 2002, 2003, 2007, 2010
• Support for relationships between tables
• Export of tables or queries as CSV file to other apps like Apple’s iWork app ‘Numbers’
• Ability to adjust the font size for listings
• Simple UI

The cons …

• Creation of databases not supported
• Changing of tables (add fields, etc.) not supported
• Adjusting the column width not supported
• Backup and Restore not supported
• Printing of tables not supported
• Saving changed databases back to a cloud storage not supported
  iTunes File Sharing is the only way to transfer edited databases back to other devices or cloud storages
• No integrated browser for opening Hyperlinks
• Display of images not supported
  Images located in the system table MSysResources or other tables are not displayed.
• Errors when executing queries with string operations
  Error executing query: Syntax Error: Stopped parse at …
• No feature updates or bug fixes since 18 months
  This usually is an unmistakable hint for a frozen app.

It’s obvious, and no disadvantage of the app, that

• linked tables in a Frontend-Backend configuration are not supported
• sharing the Frontend is a senseless act as forms, reports, and VBA modules are not supported on iOS devices

The test results …

I imported the following databases via iTunes File Sharing

• My simple WordPress database WordPress.accdb
• The Microsoft sample database Northwind.accdb
• A database containing a table with 100,000 and one with 3,666,731 records

Does the client app or the iPad behave disgracefully when using large amount of data?

Here are some stats:

• Table with 12 fields:
  Number of records = 100,000
  File size on a PC = 320 MB
• Loading of records into the list view:
  500 records within 11 sec
• Query with the WHERE condition ‘Begins With’ for a text field and descending sort order:
  215 records selected within 38 sec
• Full text search over 12 fields:
  448 records selected within 370 sec

Repeating the tests with 3,666,731 records came to a nearly linear increasing processing time.

And … Neither the app nor the iPad crashed!

Summary …

I cannot recommend Microsoft Access Mobile Database Client because of the limited sharing options and missing sync functionalities when using more than one device.
It might be useful for looking up records on an iOS device for databases containing more static content like product lists with stable prices or other tables similar in kind.

The competitors, Tap Forms HD by Tap Zap Software and FileMaker’s Bento, are light years ahead.

Related links …

DBMS on iOS devices (1)

DBMS on iOS devices (2)

DBMS on iOS devices (3)

Importing Data

Databases and Mind Maps

From Access to Tap Forms

I appreciate your Comments and Likes.
I you have any questions please don’t hesitate to contact me via comment or the contact form on my About page.
Thanks for visiting http://iNotes4You.com.

Using a database is the only appropriate way to store large amount of data.
As tablet and smartphone purchases are booming it stands to reason to have a look at some apps for iOS devices.

Allow me to point out some previous articles about databases here on iNotes4You

DBMS on iOS devices (1)

DBMS on iOS devices (2)

Importing Data

Databases and Mind Maps

From Access to Tap Forms

First of all it’s useful to have a look on what customers use databases for:

• Personal information management
  This field of activity means the “classical” applications for PDA or organizers. Mostly, there is no cooperative work with other users on the same piece of data. The amount of data on the mobile device is usually small, and is at most edited on the mobile device itself. The main challenges for developers of mobile databases for personal information management are
  • Flexibility
    regarding changes of tables and fields
  • Independency
    of stationary devices
  • Easy to use
    even for non-professionals
  • Reliability
    of the developer constantly upgrading and adapting the app to changing operating systems and devices
• Replication of large databases
  The replication of large databases or parts of them – large in the context of mobile, lightweight appliances, e.g. not more than 2 GB – is characterized by the cooperative use of the same data by numerous users. Data are mainly managed and manipulated by the DBMS on a stationary server, only a few or no changes are performed on the mobile device. Business applications, geographic information systems or multimedia are typical applications. The most important challenges for mobile databases in this field are syncing, performance, storage capacity, and the way to display data on a small screen.

The differences between stationary and mobile devices can be described by the following issues:

• Amount of data
  Due to the restrictions of CPU performance and storage capacities applications for mobile devices are suitable for about, let me say, 10,000 records. Larger amounts of data cause performance problems when it comes to import, export, and sync data with other devices.
• Hardware resources
  Stationary devices are customizable for the needs of their applications with a broad range of exchangeable hardware components. In contrast Apple’s mobile devices are not upgradable. So the later usage of the device has already to be considered when it’s purchased.
• Form factor
  Screen sizes like that of the iPad are not suitable for databases containing tables with a large amount of fields. Even in landscape mode it’s hard to view tables with many fields and different field types. Usually views of tables are not customizable by defining suitable forms, subforms for related tables, data sheets, and reports like it is possible e.g. in Microsoft Access.

As we are talking about applications for the actual iOS devices the key feature list assumes an iPad and an iPhone as mobile devices optionally supported by a Mac as the big brother to do some tasks mobile devices are not designed for.

The key features …

Here are some key features for apps managing databases on mobile devices.
When expanding the branches of this map you will see many details reflecting my comprehensive experiences with database tools.

The mind map created with the app iThoughts for iOS contains many detailed informations when expanding the branches. It can be downloaded here:

Key Features of Mobile Databases

Map as a PDF

Map in ITMZ file format (iThoughts)

Map in MMAP file format (MindManager)

Map in XMIND file format (XMind)

Classification of apps …

The apps available at Apple’s App Store I would like to describe are distinguished from each other by

• Creating and customizing a database on the mobile device
  examples are Tap Forms (Tap Zap Software Inc., Canada) and Bento (FileMaker Inc., USA)
• Creating the database on a stationary device and editing data on the mobile device
  an example is Microsoft Access Database Client, My SQL Mobile Database Client, (Impathic LLc. USA) and the mobile database clients for Microsoft SQL Server, MySQL, Oracle, Sybase, PostGreSQL, and DB2 also developed by Impathic.
• Access to databases located on webservers
  an example is MySQL Database (Software Solutions + Technologies AG, Switzerland)
  These solutions are not discussed here because of the dependency of an external data source managed on a webserver.

Some features in details …

I want to talk about an appropriate usage of databases on Apple’s iOS devices. So the focus is on private and small business applications, the usage of more than one device without the necessity of a stationary device, and the independency of external data sources.

So the key features are

• Creating the database on the mobile device
  e.g. On an iPad or by importing a CSV file which automatically generates a table with the correct field types
• Creating the fields by selecting from various filed types which are particularly useful for mobile devices
  e.g. location data
• Syncing across multiple devices
  e.g. syncing via Apple’s iCloud
• Importing data from previous systems
  e.g. via a CSV file from Microsoft Access
• Exporting data to other apps for further usage
  e.g. to Apple’s spreadsheet app Numbers to create graphs or editing data for print outs or presentations

Please visit iNotes4You to read the upcoming article DBMS on iOS-Devices (4) with a description of the Mobile Microsoft Access Database Client by Impathic LLc, USA.
This article will be published on May 22.

Thanks a lot for visiting iNotes4You.
Comments and Likes are appreciated.