Exchange Limitations and Complications

Limitations to Exchanging Database Objects


First of all, there are some basics in exchanging data that are true for all circumstances. You should have your current programs updated so that you have the latest patches and tools available.
When importing from other applications you should also be aware that the other database programs that you are importing from may not be consistent in their labeling conventions as to what you are used to in Access. Be aware of these differences when labels are created and data is formatted.

If you are importing files from text or spreadsheet programs, how is the information formatted. Is it coming in a fixed-width format or a delimited format, and if in delimited format, what is the separating character.

Also if your field sizes are not bit enough in the fields you are importing to, your information may be truncated. You may also be importing one type of data into a field formatted as something different and you will get an error. Other errors that you may see are key violation, validation rule failure, null in required field and unparsable record. These could result from rules you have set in the formatting of the fields in the table you are attempting to import into.

While exporting, especially to older versions of programs, you need to be aware that file names may be truncated as older versions of programs often only allowed names eight characters long.

When exporting you also want to be careful that your data formats are common, as unusual formats may cause problems during the export process or the import process in the other program. It is also smart to use the export wizard since this will help guide you through the process and choose delimiting characters for a smooth export/import process.

Some restrictions that apply are:

1. Only tables can be exported to an Access 97 and earlier file.

2. The Link Table Wizard used the first row of data as the column headings. You may need to adjust this in the table you are wanting to import.

3. You cannot update a linked Microsoft Excel spreadsheet in the Access Database tables. This is by Microsoft’s design and you must either open the linked Excel workbook in Excel, make the changes, save, and then close the workbook. Or you can import the Excel workbook into Access, make your changes, then export back to Excel.


Sharing Complications

When databases are being used, there is invariably a competition for the data being used. Unless the settings are such, when one person has files open, others may be restricted for accessing them. The administrator needs to decide, do you want to just share the data tables or the queries and reports and forms as well? What levels of access are granted to the various users and user groups.

How is the data to be maintained and synchronized? Are there multiple users accessing the data at once so it is constantly updated, is each file blocked from another’s use while in use so that the changes are instant before access by the next user, or are changes merged during a nightly update utility run? How accurate would any of these methods be for your data? Is the data to be locked when one is using it so others cannot?

Another point that needs to be considered is when to backup the data, how and to where? How sensitive the data is could dictate these considerations.