It is possible to link an Excel range into an Access database as a table. In this way you can maintain data in Excel while storing it in the linked table in MS Access.
Although it is very useful for simple data storage this solution is not suitable when your data requirements increase. You may need to enforce relationships between tables. Or you have to define primary keys or indexes for tables.
This is where Connexa XS comes shows its true power. It allows you to enforce the restrictions you defined in a MS Access datamodel while still deploying Excel as a frontend.
Other advantages over table linking:
- You can still edit tables in MS Access. This is also true for field definitions. Unlike linked source tables that can only be altered in Excel.
- Datatype-validations and custom field validations are automatically generated in Excel based on the Access table definitions. Using table linking these validations will have to be defined per column in Excel.
- The Connexa addin allows you to alter data with multiple users. When you want to accomplish this via table linking you’d have to share every source workbook first.
- You can have the same data open in Excel and Access. Table linking is prone to various types of errors in that situation.
- Existing Access databases can be easily selected and explored via Excel. Via table linking this is a time consuming and repetitive process.