Better integration with SQL Server
Install SQL Express alongside Access and make SQL Server the default database format instead of ACE. With a SQL Server database Access could and should support all the features it currently supports with ACE. Integrating SQL Server in that way would enable a much greater range of features and use cases to be supported out-of-the box.
Thank you for your feedback.
In terms of setting SQL Server as the default database format, that is currently not in scope for the Access product. We have provided and will continue to provide efficient methods to integrate your Access data with SQL server, and the team will continue to develop more features that streamline the data migration experience (see https://support.office.com/en-us/article/migrate-an-access-database-to-sql-server-7bac0438-498a-4f53-b17b-cc22fc42c979)
However, the ACE database engine has and will continue to be Access’s central data storage unit.
There are new features coming soon that will improve the Access: SQL experience, and we are excited to share this news. Please stay tuned! :)
Please do not bundle the installation of SQL Server whatever edition to Access. This only makes (limited) sense in a desktop scenario. But it's a complete overkill in a multiuser scenario where a lot of people uses an Access frontend to access a central (SQL/whatever) Server.
But a better integration would be nice.
KVD, maybe you could elaborate on what you mean by not being able to afford this option. SQL Server is free to download and use. If Microsoft integrated it properly with Access then using Access with SQL Server wouldn't cost any more than using Access with Jet/ACE. This would be a major upgrade in Access capabilities because people could seamlessly take advantage of SQL Server features at no extra cost.
Perhaps I am misreading this thread ... I vote AGAINST SQL Server as the DEFAULT backend. Most of my clients cannot afford this switch. I personally do not see a need for it. As is we can choose SQL Server as a backend if we want to, right? The default back end is Access itself now and it should continue to be so. I would really hate to see SQL Server usurping this place first by default and then and over time, Access back ends will get "deprecated" and will be gone.
Frank Rotolo commented
No one really loves Jet/ACE, but I think MSFT continues to include it as the native backend because it's easier for inexperienced users to work with? Many Access applications still use MDB/ACCDB's and a good upsizing wizard would be required, along with a good SSIS-like GUI for maintaining the BE's.
Ananda Sim commented
Yes, I support making the ACE to SQL Server bridge more comprehensive, overcoming glitches and differences between the current ACE - ODBC driver - Jet.
I'm encountering NVARCHAR(MAX) not working properly against MEMO, so I'm using NTEXT for the time being. I've left the mapping of double to SQL Server float/real instead of the more precise data type.
SQL identity column mechanism doesn't generate a number at the same event as Autonumber.
The bit field has a null state that does not match Access boolean field
Those kind of things.
Improve ACE - ODBC driver if possible. Don't weaken Access by stripping out ACE and making the old Access Data Project
ADP was the wrong solution to the wrong problem. I don't want to bring back ADP, I want to see Access upgraded so that it can take full advantage of SQL Server to the point where SQL Server becomes a suitable replacement for ACE.
Alphonse, SQL Express is installed in many multi-user environments and other Microsoft products install it by default. Installing Jet/ACE makes much less sense in a multi-user environment which is why I'm suggesting SQL Express instead. If the user already has a SQL Server installed then the install process could give them the option to use that instead of SQL Express.
Alphonse Giambrone commented
Installing SQL Express alongside Access makes no sense in a multi-user environment.
Daniel chen commented
You mean "bring back adp?"
All they had to do is listen to what we told them two years ago in seatlle