MS Access has limitations and shortcomings that should be considered if you plan on selecting MS Access over SQL Server. Here are the Access shortcomings that will be particularly troublesome:
The query design grid will only support queries of up to 1,024 characters. Although you can enter the query in SQL view you cannot use the query designer to create the query.
Many organizations select Access specifically for its query and report interface and wizards - but they do not always permit construction of the desired result – even when the result is available programmatically.
There are times when a query is too complex for MS Access to understand and you will only receive the error message "Query too complex". In these circumstances the only approach is to divide the query into smaller parts and determine where the problem lies. A workaround will often be available that requires that you base the query on the results of another query – but see the section immediately below on performance problems.
Performance Problems and Hangs
The JET database engine used by MS Access can be very slow especially with queries that involve sub queries or queries that use other queries to gather information. Queries that run in seconds in SQL Server can take tens of minutes to run in MS Access and they may actually hang your machine. Queries that attempt to assign counts or sequential numbers to result sets are particularly susceptible to severe performance problems.
Perhaps the most troublesome issue is when MS Access returns incorrect results for queries with multiple joins and or sub queries. There are times when Access will notify you that a join is ambiguous and cannot be constructed and recommends that the query be reconstructed to use intermediate queries. There are other times however that Access actually performs joins in the wrong order and therefore provides incorrect results. Although this is a very rare occurrence the fact that it can happen at all may not be acceptable to your organization. When it occurs that only option is to divide the query into smaller parts and create intermediate queries as needed.
Feel free to contact us for a no-cost consultation to learn how we can help.
609-799-7715 ext. 310