Microsoft Access 2013 is out and both Ben and have been experimenting with it for sometime now. Here are 10 things we love about the new Access web services:
1. It’s SQL Server-based. (Ben)
It used to be that Access had its own database engine, known originally as JET and now called ACE. We could always upsize an Access database to SQL Server and keep on using Access as front-end to the new SQL Server database. However, the upsizing was never hassle-free and there were enough differences between the JET/ACE engine with SQL Server that there were some incompatibilities. Upsizing is no longer needed and your database will scale nicely with a SQL Server backend.
The Access team is meeting with the SQL Server folks on a regular basis, that never happened before, expect bigger and better things from the collaboration of both groups.
2. We can still use “regular” Access for our reporting and other enhancements. (Ben)
Web technology is a wonderful addition to any traditional legacy database. We typically design complex applications and it is very unlikely we will convert them to web-only applications. However, the fact that we can connect to the same SQL Server means we can continue to use our legacy applications, enjoy many wonderful features that regular Access has to offer. It’s a win-win.
3. …and it’s not just for Access! (Ben)
We can also use SQL Server Reporting Services, Excel or any other tools that supports connecting to SQL Azure/SQL Server over ODBC to also generate reporting. Many of our projects usually have reports output into Excel spreadsheets for various reasons and with ODC functionality, it is possible to create a spreadsheet using data from Access web services!
4. The new programming model frees us from needing to design the forms and focus on normalization first (Ben)
Some programmers will be horrified over this loss of control. No longer could they place a button perfect to the pixel. But, hey. We’re not hired to make pretty apps. We’re there to make business functional, and by making the form design simple, we gain two big things – 1) consistency and 2) intuitive interface.
5. Searching and filtering is now built-in. (Ben)
Access provides a list view which always shows the search box with a list of records. There is no code needed to get it to be searchable and users can just click on a record to see the details.
6. Navigation is also built-in, allowing us to focus on the business process, rather than the mechanisms of getting from there to here (Ben)
When we add a table, a new tab appears on the main page and we can optionally hide it. Furthermore, when a record has a lookup field on it, it can be displayed as a hyperlink which can be used to pop open a form showing the details of this related record. There is also related item controls which makes it every easy to drill through. All with no code!
7. Greatly improved lookup interface makes it easy to build foreign keys (Ben)
In previous versions, lookup fields required 5 steps in the wizard to set up the lookup. This is now a single step and the description are far more easier to follow so that a person who never has heard of terms like ‘foreign key constraint’ and ‘normal form’ can easily create lookups. It’s also going to avoid keying in erroneous data.
8. We finally get a customizable navigation bar (now called Action Bar) (Ben)
Lots of times, we found that the legacy navigation bar would not be always appropriate — sometimes we end up having to provide our own navigation bar just in order to be able to write code to check conditions when a record navigation is performed and cancel it if needed. Well, action bars are now fully programmable, so we have much more control over checking the conditions and allowing the actions without having to re-invent the wheel. Again, a big time-saver.
9. I love the new pop up properties window! (Juan)
I love how the properties for controls now just pop when I need them, no need to hunt for properties in a list anymore!
10. One click publishing is improved and Office365 2013 makes it very easy! (Juan)
When you get into the dirty details, setting up your own web server is not a trivial task. Nobody wants to make investment only to find out that it was overkill or was all wrong. Setting up a subscription account is comparatively far less risky compared to large outlay required in getting a web application running. We think that’s a big improvement.
Have you had a chance to work with the preview? If so, let us know your thoughts in the comments below!
Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.
Thanks for sharing the positive side of the new Access 2013! This article is a breath of fresh air. Access 2013 is a truly amazing accomplishment by Microsoft. After looking at the nuts and bolts of the new framework Microsoft has created for us I am incredibly impressed with the boldness, foresight, and hard work Microsoft has done in bringing Access to the web.
Hi Juan and Ben, I work for a multinational company in Italy and we’re still using Access 2007 with most of our backends ported over to SQL Server. At point 3 you say, “it is possible to create a spreadsheet using data from Access web services!” but does Access Web Services mean the data fed to Excel is stored in SQL Server/Azure, in Sharepoint or in Access Web Services proper which may be something else? Also can you give me an example of how Access web databases can be used at a corporate level where we generally handle data within our intranet?
Giorgio –
In Access 2013 web database, the data are stored directly in SQL Azure or SQL Server, rather than via SharePoint. For details on this, have a read of this article:
http://blogs.office.com/b/microsoft-access/archive/2012/08/08/access-2013-and-sql-server.aspx
As for using Excel and ODC, this is a feature available since Excel 2007 which allows you to connect to external data source in very similar fashion to how you would use a linked table in Access. With this, you can get your Excel spreadsheet to be ‘live’ using the table or pivottable or chart. For discussion on setting up a ODC:
http://msdn.microsoft.com/en-us/library/office/bb545041(v=office.11).aspx
As you can see from the Access blog post, they give you the connection string that you can then feed into the ODC (so you’re actually connecting directly to SQL Azure/SQL Server).
As for an example — I think the traveling salesman is a good example of where a web database can be set up to solve the problem of providing a data entry portal that can be accessible no matter where they are and because they have to log in to the portal, you have control over who can get to the portal or not. Furthermore, because it’s now in SQL Azure (if you’re using Office365) or SQL Server (if you’re using a on-premise SharePoint installation), you have more options in connecting and accessing the data directly. You could set up a Linked Server in your local SQL Server box and thus enable cross database querying or run a SSIS package to keep the data synchronized.
If there’s no traveling salesman or need for access outside the corporate office, the only benefit is the ease of deployment and that may be minuscule compared to rich Access client, especially if you have automation involved. However, I think that many of people running Access applications will find new opportunities to use web platform, even if only as a simple add-on to their LOB application so there’s a easy way to enter data remotely.
I hope that helps answer your question.
Thanks Ben, you say “As you can see from the Access blog post, they give you the connection string that you can then feed into the ODC”; where in the blog post do they give you the connection string and at which stage in the Let’s Get Connected in Excel 2007
post do they show feeding it(which I suppose it’s a text string) into ODC?
I can tell you I have had many clients who are more concerned with how it looks than how it functions. They assume I will make it work and they want it to look good too. I designed an Access web based application which was very functional, the client paid me, then took a different approach because the other system looked better. This is a real issue.
Linda –
I personally think that the Access web 2013 definitely has a modern look. There are some animation effects such as whooshing in a view when changing the view. The point was that all of this eye-candy comes for free, without any programming effort on our part so we can focus more on the functional application. Otherwise, we’d have to charge more just to make it pretty (and we all understand that pretty also can mean usability). The whole point of developing on Access is to be able to do it quickly and for cheaply. Now that pretty eye-candy is included for free, it’s even a better deal.
Since version 2 to 2003 ACCESS was the number one RAD desktop database and front end db tools.For the reason that it’s very cost effective to implement. When A07 was out most of the ACCESS developers jump to Alpha Five, .NET, and other free open source tools because of the web.Then ACCESS 2010 was the answer of MSTF, very small chunk of the developer buy it because it’s buggy,db is in the list and not relational and very expensive to implement(Access services in Sharepoint 2010 Enterprise). Here comes ACCESS 2013 (the answer to the experimental A2010 web db),ADP was Murdered, no significant improvement in desktop dev’t. side. Still the web app is not affordable to small and medium businesses specially in a 3rd world countries. I’m hoping for a sharepoint 2013 express because sql server express 2008R2 rocks.I’m currently evaluating Morfik-Build Ajax-based Rich Internet Applications – http://www.morfik.com
Im an Access developer since version 2, Access web database in not practical to implement for small to medium size company’s intranet specially that we are in a 3rd world country. sharepoint enterprise is very expensive. Internet connections in 3rd world countries is very unstable, so office 365 is not an option. pls Juan and Ben do not delete my post. tnx
Hi Rene
Which country do you live in?
Juan
Juan Good day to you, I live in the city of Davao, Philippines.
Dear Ben and Juan,
Thank you for the wonderful post. However one thing that is not covered extensively by most Access posts is about the programability in Access 2013. We can use VBA in Desktop Access databases, however for Web Access Apps, it is limited to Data and UI Macros designed in Macro Designer. Been a hardcore VBA developer, I couldn’t find myself been very fond of it. Is there any alternatives or options in Access 2013 ? Something like been able to create Stored Procs in backend SQL Server, and ability to use them in Access Web App ? Eagerly waiting to know. Thank you.
Andy -
Excellent question. There are two aspects to consider:
1) In this version, it is possible to link directly to the same SQL database via ODBC, so you could still use a traditional Access front-end linking to those SQL tables and thus enjoy the rich VBA and other automation options. This is definite improvement over the 2010 web database where we found that the SharePoint lists wouldn’t work so well with larger amount of data and had to change too many things just to get it to work in the 2010 web database.
2) Out of the box, there is no ability to edit T-SQL or create/alter stored procedures. If you had an on-premise SharePoint, then it would be possible to edit those. In an O365 accounts, your access is read data or read/write data only, no ability to perform DDL operations. We’ve already expressed the need to be able to write T-SQL and encourage others to chime in. One way to send them feedback is to try out the O365 preview (which is free) and using the feedback buttons that’s shown in all preview products.
To try the preview:
http://www.microsoft.com/office/preview/en/try-office-preview
and choose either ‘Small Business Plan’ or ‘Enterprise Plan’, NOT ‘Home Plan’.
Thanks!
Thanks Ben,
Yes, ability to add T-SQL will increase the use of Access Web App hosted in O365 by many-folds. If this happens, there couldn’t be a better way to build quick business apps.
I am going to try O365 preview and request this feature in feedback right away.
Thanks a lot.
Andy
Thanks, Andy!
The more feedback we give to Microsoft, the better. Hopefully that’ll be done sooner than later.
I’ll bet they haven’t updated the charting engine to be the same as the other office products…..I am so sick of access charts.
Well, nowadays whenever we need charts and/or pivottable, I prefer to export out to Excel and if necessary automate Excel. I find this provides for much better reporting using Excel’s resources.
Hello Juan,
Just to be sure. ADP is removed from Access 2013. But is an ODBC-link the only alternative vopor desktop applications? If yes, can execute stored procedures with parameters?
There are two ways you can call stored procedures from Access: using pass through queries and using ADODB recordsets. To learn more about the later please visit:
AccessExperts.net/blog/Easy
Access 2013 just confirms the Microsoft idea of kill Office. 2013 just a new skin of Access 2010 with some incomplete and useless features and without some INDISPENSABLE features like ADP (Microsoft Project), without a REAL integration with MSSQL (AGGREGATED NUMERIC and DECIMAL FIELDS). I hope Microsoft wake up to people really wants.
Rui, do you realize that Access 2013 web database is powered by SQL Server and can use decimal fields?
http://blogs.office.com/b/microsoft-access/archive/2012/08/08/access-2013-and-sql-server.aspx
To use the new webapp functionallity of acceess 2013 i need sharepoint and i need sql-server. Where can I find more info on what license plans there are.
Hi Daniel
You can try out AccessHosting.com free for 30 days. Disclaimer: we are partners with them.
Sincerely,
Juan Soto
Great article! I’ve recently worked with O365 and Access Services. Linking to SharePoint lists from within Access Services is a great feature. However, I want to do the “opposite” – I want to link to the tables (now stored in the Azure SQL tables) from another site in our O365 environment. I would think BCS would be the way to do this, but since we don’t have access to the Central Admin to manage Secure Store Services, etc., I can’t do that. So do you know if future versions of O365 is going to allow that type of connectivity? By doing this, we can use other reporting capabilities from within the SP environment rather than having to use things like Excel or Access connecting to the data via ODBC. Thanks again for a great post!
Mike
In Access 2013, you still can link to any SharePoint Lists inside a web app. It’s my understanding that BCS is available on certain plans, most likely the enterprise plans. The next release of Office365 isn’t out yet so we don’t know any idea exactly which plan will have BCS for the new version of Office365 but if you’re on such plan, then you should be able to set it up. Until they release the new plans, we won’t get to know for sure, but for what it’s worth, you may find this a good read and perhaps leave a comment with Access team to ensure that BCS will be a part of the O365 plans:
http://blogs.office.com/b/microsoft-access/archive/2013/01/22/visualize-access-data-in-excel.aspx
I hope that helps!
Hi Juan
Thanks for posting a nice article. I would like to ask a question. I have a database in access. Now since this when I want to add feature like remote connection so that multiple user can work against the application(which in turn database). Access had the limited feature in terms of database(does not allow multiple connections if access db is already in open state). How can office 2013 access version help me in adding concurrent database access to multiple application users ? I have a vb.net application as frontend to access the database which is in access.
Warm Regards
Amit
Amit,
Access databases always has supported concurrent connections since version 1.0; the standard best practice is to split the data into its own back-end file. The problem usually is because people are using both data and Access objects (e.g. forms and reports) in same file and they’re sharing same files. If you search on “splitting Access database”, you will see numerous articles discussing on how it works and why it should be done.
The fact that you have VB.NET application connecting to Access database leads me to think the problem may not be necessarily with Access but without knowing more about how you are connecting to the Access database, where it is located in relation to the VB.NET application, how it is used by users, it’s hard to tell why you may be experiencing concurrency problems. If after searching on the splitting database articles you don’t have any idea why it’s still a problem, feel free to contact us for a quote.
I hope that helps get you started.
Looking forward to the Web features.
In the meantime, how compatible will our exisitng Access 2010 be?
1. Front-end Access 2010 with extensive VBA (totals 80 MB)
2. Back-end SQL Server 2010 linked with SQL Server Native Client
3. A few (small) local Access tables
4. No Access reports. Extensive VBA using Excel Object Model programming.
5. Deployed nationally via Citrix
After primary support objective (e.g. will Access 2010 actually migrate to Access 2013), we are looking forward to moving functionality over to Sharepoint.
Will Access 2010 be fully compatible with Access 2013?
Thanks. We really need to plan and your experience would be very valuable.
Related to the perfect MS Access 2013 Development Workstation setup.
Saw the Access 2013 demo at Microsoft. The presenter had his portable (Surface) running a VM for SQL Server, SharePoint, and Access with Windows 8 Enterprise. There might have been some other things.
For us programmer DB-developer types, setting up a server (and VM) can be a challange.
Can you or your team please create a HOW-TO document for best practices for Access 2013 Developer?
I just bought a new PC to set up as a development workstation.
It would be great to set it up correctly the first time.
Our first objective is to migrate (if possible) existing Access 20210 over to Access 2013 desktop and keep current projects supported.
From there, we are anxious to use the same SQL DB and the Access 2013 web development to enhance the application.
This would be a huge step for all of us MS Access developers.
Best Regards
We use Oracl’s VM software for setting up labs: https://www.virtualbox.org/
You will of course need a windows license and a copy of Access 2013. I don’t expect problems with your 2010 desktop migration. If you wish to use the web features you would need to re-write the app.
Good Luck
In access 2013, is it SQL based when I choose “Blank Desktop Database” or only when I do a web app?
If it is only when I choose web app, is SQl Express server enough or do I have to have sharepoint also if I want to have the database be on a local network?
Thanks for the help!
Hi John!
No, desktop app is not that same as web app
No, SQL Express is not enought, you need SharePoint too.
Thanks!
Juan
MS Access Web Apps are great! Except for this……(I’m using Office 365 Small Business Premium Subscription.)
When you send the web app tables to MS Access 2013 via Create Report module, the property Read/Write does not work. In other words, When you build your reporting UI you can NOT update a record to web app sql in the MS Access Report Module. If I wanted to print invoices and had a field in Web App that held data to show that the invoice was printed, an error comes back saying that SQ Native Client can not update server. I called MS and talked to someone who was very knowledgeable about Web Apps. He didn’t even know this feature would not work. The property is there to set read/write but always defaults to read only. (Yes, I’m the Admin). This could be a deal-breaker. The best of all worlds would be that MS creates SQL Report Server functionality for Office 365 MS Access Web Apps.
Thoughts???
You could use reporting services right now, since the data is already stored in SQL Server. Thanks for the great tip!
Juan
Microsoft Access 2013 has dropped support for legacy file formats such as dBase. I support a application that imports and exports the dBase file format. I have been testing on a Windows 8 32 bit machine using Access 2013. Converted both front end & back end to 2013 format. I have been trying to open a ADODB record set using connection strings that work in Access 2010, but will not work in 2013. Any insight would be greatly appreciated. Have tried every connection string that I can find (connectionstrings.com) but, to date, no luck. BTW Great Blog!