Structurally
- Excel is referred to as a flat file — Access data is referred to as relational
- Both can be used to link to various external data sources BUT you cannot edit the data to change the source data through the Excel user interface
- Both can connect to Windows SharePoint Services lists BUT Excel provides just a read-only connection to SharePoint lists; whereas Access lets you read from and write data to SharePoint lists
- Access applies a structure to the data to help ensure its integrity; Excel does not provide this feature but does allow you to manually set up Data Validation rules
Data Analysis
- Access queries provide maximum flexibility when querying data, Excel lacks this feature
- Excel provides what-if analysis tools that allow different scenarios to be run on the data and to compare the results of several scenarios into one report
Multi-User Collaboration
- Access allows for multiple users to work in the database at the same time; Excel requires sharing of a workbook with only one user accessing the data at a time
- Access provides multiple connections to a Windows Sharepoint Service; Excel only provides one way
- When stored in a shared network folder, an Access database still allows for muliple simultaneous users, Excel still only one user at a time
Security
- Access automatically saves data as it is entered. It is a good idea to create backup copies of the database as scheduled intervals in case recovery of past data is necessary
- Excel contains an AutoRecover feature which allows you to schedule the intervals at which data is saved
- Excel allows for locking cells to prevent modification, hiding of columns and rows of data, and protecting worksheets
- Both programs allow for encryption of files and allowing only authorized users access
- Excel allows for setting files to read-only and specifying user-based access to the data
- Access supports the security of any database to which it is linked as well as allowing encrypted password protection on individual databases
For more detailed information review these documents:
