Understand the Limitations of Linked Data in MS Access

If you are dealing with Linked Data in MS Access, you should be aware of its critical reputation.

While making use of MS Access in your organization, you might also want to link external data with MS Access. As operations in the application cannot always be performed using data already in MS Access. When you make use of external data with MS Access, you will have to link the external data with the application, this will allow you to use the information in its original format. Meaning, you will be able to make use of data from another application, in MS Access. However, when you choose to link data from external sources to MS Access, it will have certain limitations. Although Microsoft has provided Access users with the scope of using data from other applications in Access, it does not really allow them to make changes in the original data using Access. Other such limitations are explained below, along with some work around.

1. Excel Data – When you are making use of an Excel worksheet in MS Access database, you cannot make changes in the already existing worksheet. Also, adding and deleting more rows will not be allowed in-case of linked data. By linking, Excel data can be used in MS Access primarily in a read only fashion.
2. Text Files – While using text files in Access as linked data, your access to them is a little more than read only, but still not too much. Although here you are allowed to add new rows to the data, you still can’t make changes to any of the already existing rows. Neither can you delete any of the existing rows, nor can you update the existing ones. This is done primarily to avoid any kind of breaks in the operations that are already being performed using the given text files.
3. HTML – The access here is read only, much like what you are allowed while making use of Excel data as linked data. HTML data can’t be altered by any means. You can neither update, or delete, nor add any more information to what has already been given or created in an HTML table.
4. dBASE – Since this is a file format that is already available in MS Access, here you can make changes, as much as you can in a native Access database. However, for this you will be required to have access to the primary key of each database table. Without the key your actions will again become restricted.
5. ODBC – This refers to a data access technology that makes use of driver between Access files and files from linked data/ application. This includes applications like SQL Server or Oracle, which are also database applications. Since the source of linked data is also a database table, you can freely perform operations, whether it is of editing, deleting or adding. However, this will be possible only when the linked database has a uniquely defined index. Therefore, if you want to use linked data from other databases applications as freely as you can use native Access databases, you will have to have a unique index for all linked databases. It is always prudent to keep a professional tool on hand for recovering corrupted mdb or accdb files before initiating a drastic change.

Author Introduction:

Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupt SQL Server and excel recovery software products. For more information visit www.datanumen.com