Access 15 and SQL Server

1. In my new company, Access is still considered a toy-database with many flaws and no performance and even a security risk to SQL-Server. I've almost given up to show people the benefits and ease of sharing Data between SQL-Server, Access and Excel.

Personally i think Access is more than enough for small companys, sharing a single Access-File on a server (with file-sharing) and accessing it with ado.net. I had done this in my old company and it did work well. 2. I didn't knew that there was another possibility than ADP! I don't know the difference between both. 3. I just tried it, but can't use it because of (1). 4. Like (3). 5. What new features would you like to see in Access that would make development for SQL Server faster, more efficient, or more manageable? And any other data you would like to contribute to this subject is most welcome.

I would like to make Access the only (!) developing GUI for SQL-Server, fully replacing the SQL Management Studio. Access would be used much more seriously and the "SQL-Server-Fans" could get used to local Access-Databases where it makes sense. This is because it's easy to work with Access, but annoying to work with SQL-Management Studio (2005). And Access alredy supports creation and management of many things in SQL-Server, so why not be consequent?

1. In my new company, Access is still considered a toy-database with many flaws and no performance and even a security risk to SQL-Server. I've almost given up to show people the benefits and ease of sharing Data between SQL-Server, Access and Excel.

Personally i think Access is more than enough for small companys, sharing a single Access-File on a server (with file-sharing) and accessing it with ado.net. I had done this in my old company and it did work well. 2. I didn't knew that there was another possibility than ADP! I don't know the difference between both. 3. I just tried it, but can't use it because of (1). 4. Like (3). 5. What new features would you like to see in Access that would make development for SQL Server faster, more efficient, or more manageable? And any other data you would like to contribute to this subject is most welcome.

I would like to make Access the only (!) developing GUI for SQL-Server, fully replacing the SQL Management Studio. Access would be used much more seriously and the "SQL-Server-Fans" could get used to local Access-Databases where it makes sense. This is because it's easy to work with Access, but annoying to work with SQL-Management Studio (2005). And Access alredy supports creation and management of many things in SQL-Server, so why not be consequent?

RE: julg said:

"- easier development: get rid of the mdb-container. every form/report/module/table/index/schema should be placed in its own file -" Oh Please DON'T

The success of Access is BECAUSE of the single file!

If you break it up well... go to .Net Really

I can tell that MS Access team doesn't talk with developers much. They still think mom and pop's are building recipe databases at home when MS Access doesn't even come with anything but Office professional and up. Obviously you don't know enough about building databases to be see that Access is so old and not even close to compatible or powerful to other BI tools. Each tool has its needs, but we're having to push Access to do what SQL tools can do.. and need that functionality. My biggest grips are syntax compatibility, cannot write native SQL queries that work with Access, linking to SQL server to pull data to data analysis would be easier if the SQL tables and views would be like using SSMS on SQL server, but inside Access, since we use Access more for Data gathering and mining and Excel for Charting, since it don't work with Access. Using Excel to SQL doesn't work, but Access is used in the middle. When I'm ready to upsize, it doesn't work, I want to port native SQL query code in Access against a SQL server, to share with an Informatica BI ETL developer and to build a data warehouse. Data manipulation in Access is not as friendly and I want to create report queries like SSRS and SSRS QBE which is better than Access QBE tool.. sideways rather than vertical. Basically everything about SQL file storage, security, SQL database files, view, tables, native syntax, even an the SSMS Visual Studio query syntax highlighting and Intellisense, and I love the viewing the data window below the text query works wonders. Yet, I don't want to always have a SQL Server or I work at places where I don't have SQL installed, or know how.. I want all the power of SQL Express, SSMS, Visual Studio inside Access to build an Access database that has great record locking, querying, and storage, and native SQL formats and SQL style security model. Basically take Access and Make it Powerful.. you'll find that what you're really looking at is the ultimate convergence on Access and SQL Server tools/formats. We may not have SQL, a DBA, Sharepoint, SSSRS but we really need Access, We need Access to be that in between BI power. All the current query editing, table linking, data formats are outdated in Access, and relationship tools.. we need the power of newer the SQL tools suite in Access.. and BTW make a better charting tool while your at it..

We need the power of the SQL development tools in Access as Access developers, start there! I can give you several examples and databases that would have taken much less time to develop if only everything SQL was a part of Access!

It would be awesome if I write code on Access in SQL and I could copy that code to SSMS and have it run on a SQL Server Express tables/views,etc.. Portability would be the keyword! This is why upsizing doesn't work, I cannot even do it manually, let along have an outdated wizard do it, I may even build the SQL Server version with all the views and queries better than Access, but i want the queries to recognize the table names (and ownership) the query names on a copy and paste. its that simple. Access should be very similar to SQL tools. Just one is powerfully smaller/one file/for regular users/ and one is for the entire business needs.. Just like a car.. regular model and luxury model package but instead Comparing Access compatibility or functionality is like comparing Win7 to Win 3.1. Same family, different power.. but same underneath.

tables are not even close to compatible with SQL tables and neither is Access Query code or QBE editor, or SQL view in Access.. and native SQL query thing, just is not compatible with SQL server syntax. There is no intellisense, or SQL syntax color highlighting in Access.

I don't create ADP, but tried it and failed, I do like Sharepoint Access Services.. I like to b eable to create SSRS reports.

Be able to write native SQL on Access tables/views.

Be able to create actual stored views on Access. Rather than creating complex base queries that people can break (used for reports) I never build a FE vs Backend system, too hard manage, not conventient to update, and I just learn to drag and drop objects between Access files. Allow relationships queries to be created and stored in Access, if you do that, we could then Drop the need for table deletions which would kill relationships, which have to recreated, I want them to like SQL I want a GUI to create them, but once created, it makes the SQL code, but deleting object would not kill my stored relationship.. currently you have delete relationships, deletion/replace objects and recreate relationships (if you can remember what they were!) Security by Entire database or AD user groups would be better than security wizard which fails to do what I want it to. I want it to be like giving users access to folders on the network, simple setup and maintentance.

We could use the SQL database file format. All other Bi tool SQL code to be imported into access. Object dependency is such a pain, writing SQl query code, doesn't care about object dependency breaking, is much more powerful, because in access, unless you have the option to remember autocorrect, you have to remember how to bolt things back together. I do these things alot because I build lots of similar databases for multiple uses.

I LOVE ADPs, probably one of the most underrated software development products in the world! However, it should be used for more serious, data stuff. Excellent for delivering CRUD forms, and for delivering reports. Found it best to develop most of the business logic in SQL stored procedures (using SQL Management Studio). Can scale to hundreds of users. Excellent for agile development approach.

Make the error reporting between and SQL Server and MS Access more robust. The dreaded 3146-ODBC Call failed is very limited and not useful. Also, much better integration with SQL Server for Store Procedure, Functions with regards to accdb (NOT ADP's) application. Also publish a recommendation/white paper/Sample/How to, for various real world scenario so as developer can utilize it without doing Trial and Errors. This you dont have to wait for Access 15 these should be done for Access 14 also. (Especially when there is SQL 2005/8). I hope this is not asking too muck. best regards Khuzema

Included in Access Store procedures and something seems to SSIS. Thank you

I think Access needs something like T-SQL programming language. This will make it much more powerful and easy to program. Also, it goes without saying that the file size of 2GB needs to be drastically increased.

Bruce: "The success of Access is BECAUSE of the single file!"

Have you ever experienced project database corruption? It's a GOOD BYE to your project (!)... well, if you don't back up every 5 minutes.

I love the idea of separated files in design mode; see Visual Basic. They can be put together when you create MDE/ACCDE (or maybe EXE sometimes in future). P.S. We need more controls!!! And, at least one container control.

P.P.S. Please, fix old bugs before you add anything new enhancements. One of the most annoying bug is handling decimal separator incorrectly in various national settings, eg. Val(17,15) returns 17 with Czech-decimal-comma-setting. According to this, any function returning string is a BIG problem, eg. Switch. For more bugs & wishes see www.alis.cz/relax/download/access/Access2007_bugs.rar

or

www.alis.cz/relax/download/access/A2007bugs.pdf

Vladimir Cvajniga said::

"Have you ever experienced project database corruption? It's a GOOD BYE to your project (!)... well, if you don't back up every 5 minutes."

In 17 years of programming Access, yes once in a while but I do do back up every hour so I have never really lost too much. If Access breaks up source objects "behind the scenes", that is fine but it should be completely invisible to the user , As for me and my my clients the power of access is ONE FILE ant is transportable and easy to back up and modify. If we get into separate files and haven't to compile them together than... well that is .NET programming or any other of the 100 odd programming languages out there. I am one of the first to complain about MS forgetting about Access Programmers and too much of their effort recently has been spent on the mythical mom an pop building a recipe database... BUT on the other side of the coin, When I read some of these request I wonder if it is an Access forum of a C# forum. Access is Access not C#

To me ADPs have many compelling features but the fact that they are totally self contained is worrying. I would like the performance features of ADP to an existing ACCDB, not convert everything to ADP. In other words have 5 ADP forms in an ACCDB that has 100 normal forms. Garry