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.
Derek McLachlan commented
Good idea as long as we still have local tables in the front end. They are endlessly useful.
If this could be done without losing the single file based database approach fine if not this would be a dangerous and very destructive move in my opinion. Having a single file that is directory agnostic and completely transportable is a real competitive advantage that provides something that web databases/professional database engines don't and can't provide - Its what make MS Access MS Access. It would be a really dumb thing to get rid of that and move to all the hassle of a scratch pad riddled with dependencies. By the same logic you could argue that you should get rid of notepad in favour of word. Get rid of BMX's in favour of mountain bikes and I should probably have either a steel strung guitar or a Spanish guitar but not both.
Single file based approach would require Ace being incorporated into SQL Server mdf format! Hell will freeze over before Microsoft do this :)
Steve Goldring commented
The best answer to this suggestion is MSOLEDBSQL against SQL Server in the cloud. Very big news that this data technology is back in focus. Testing so far indicates significantly better performance compared with ODBC Driver 13 for SQL Server
It makes no sense to bog down PCs with SQL Server uselessly by default when the vast majority have no need for it and in the cases where it makes sense, the ability already exists to install the Express edition (for free) and leverage it. To me, this suggestion is a no go and if I could vote against it, I would. I don't think MS wants to go down the avenue of shoving SQL Server down people's throats.
SS has it's reason for being, but making it the default with Access, I'm sorry, NO!
If you want SQL Server Express, install it, no? It is freely available. Simply download it (https://www.microsoft.com/en-us/cloud-platform/sql-server-editions-express) and install it. You can interface Access with it right out of the box.
Since it is a separate program on its own, it make no sense that it would be installed as part of Access. Furthermore, the vast majority of users use Access as is on its own. It would make no sense to go and install SQL Server Express and thus bog down their systems for no reason with useless services and the likes!
If you want it, it is but a free download away, but it makes no sense to include it as part of Access.
some Performance issues while connecting to SQL Server could be improved. Actually the heavy Network load due to permanent locking actualisation leads to slow querys.
SQL Queries in Access are more advanced than SQL Server as as you can declare an expression and use in other other expressions. I do like the idea of Views in SQl Server but Access has Form ware whilst SQL Server is a back-end database.
Detlef Traum commented
I don't agree with the opinion "ADP was the wrong solution for the wrong problem." In my Opinion ist the change to ODBC a step back. I did not find any convincingly argument for the removal of adps. What do i not understand? Don' know, after 3 Years of investigation ...
Christopher Cookson commented
I don't think SQL Express is necessarily needed, as with increasing cloud data usage, there's a strong case for SQL Azure if you need multi-user capability, but want to retain the rich client capabilities that Access desktop has over Access web apps.
It would be nice to have a 'Publish to SQL Azure' option.
The ability to connect to a SQL Server back end, (either full blown or the Express version), is there now and has been there for some time; this should continue. Closer alignment between MS Access and SQL Server in terms of rules, data types and so on is a very good idea. Having an upsizing wizard that works almost always is a third good idea. However ...
Still think SQL Server as the DEFAULT BACKEND is needlessly rocking the boat. It is a layer of complexity that is simply NOT needed.
For my clients, I do NOT supply run time versions or MDEs. They get the source code (with passwords as needed). This is one of my selling points ... nothing is hidden. If my rates do not satisfy them or if the client thinks I am not sufficiently responsive, they are free to engage someone else. In 25 years I have not lost a single client for this reason and have not had a mission failure in a non-run time, non-MDE environment. I think using SQL Server as a DEFAULT backend is a bad idea.
As George has said, SQL Server is completely free to install and use and bundling it with Access should not imply any increase in cost for anyone. A valid point however is that installing it by default wouldn't be appropriate for users who already had SQL Server. There ought to be an option to use an existing copy as well as to install a new one.
George Hepworth commented
@KVD. There is a free version of SQL Server. It is called SQL Server Express. You can actually deploy a small production database with Access and SQL Express--with licensing only for the developers' copy of Access. The backend is free of licensing costs. You can deploy the Runtime version of Access for all users.
That said, I'm not sure I'd want to make SSE the default backend for Access, but it ought to be relatively straightforward to make it an alternative for those who want it.
I'm not going to vote for this one as other suggestions seem to warrant more attention (IMO). But cost just isn't an issue here.
To respond to nto, I just looked on Google under "SQL Server prices". The product ain't free. Sure you can get a SQL Server desktop for $64 but it may not be handed out to your clients. The enterprise copy is $7,000 plus. There are less expensive variants (hard to understand limitations) minimum of $2500, but they all come with a per core charge. Access 201x by itself is approx. $120, as a part of Office 201x Professional it is included.
This begs the question, why should SQL Server be the DEFAULT backend? If you and your clients want that knock yourselves out. That capability has been around for a long time. I vote AGAINST MS SQL Server as the default backend, not only for price reasons, but also because of my fear that MS will make it very hard to use another Access database file as a backend - no matter what they say up front.
Kent Gorrell commented
My two cents - ACCDBs should be more closely aligned with SS.
For example, When upsizing data from Access to SS you often find all sorts of rubbish data that Access allows but SS won't.
Applying the same rules and more closely aligning data types would make the migration easier. And this includes preventing bad field naming in Access as well as bad data.
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.