Microsoft Access 2013 Upgrade 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. Instead of adding a search form and coding everything to get it working, it’s built automatically for free.
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!
Me again. I just tested another older Access database (also MDB) that had memo fields and now has long text
– I query within the database and viola! The entire long text displays.
For my previous post, note that the entirety of the long text data is in the table and displays correctly with a simple Open.
Now I’m double confused
Just got 2013 loaded at our company. I did not have too many old Memo fields but immediately notice that I cannot query a Long Text and get the entire thing. I suppose it is truncating to 255.
All of my code that queried Memo fields never had a problem, now I can’t figure it out.
Even when I execute a query in 2013 the Long Text is truncated.
– I’m using MDB databases. My databases are on a server and I could never make the connection to ACCDB databases
– At least I think not being able to connect on a remote site is a result of being remote and some piece of code missing from the web server (not my server). I forget
– I’m using ASP classic. These are web sites with many applications that have many lines of code and I don’t have the time or skill to convert to asp.net or some other language.
– I do plan to convert to SQL Server, eventually, but I have lots on my plate and have not found the time. Plus, Access lets some skilled users do things that they won’t have access to do on SQL Server. Admitted, the Access interface is pretty sweet. ( except for this Long Text debacle :-))
Help!
Now I want to add “meta name=”keywords” content=”Some Keywords, Keywords, Etc… and “meta name=”description” content=”Some Description”
Private Sub Report_GotFocus()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim MyPath As String
Dim Temp As String
Dim MyTitle As String
MyPath = “Z:\Local\Quotes\QuoteShort\”
MyTitle = Title
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT InvID, Title FROM TblQuoteHTMShort”, dbOpenDynaset)
Do While Not rs.EOF
Temp = rs(“InvID”)
MyFileName = rs(“Title”) & “.htm”
DoCmd.OpenReport “RptQuoteHTML-TitleShort”, acViewReport, , “[InvID]=” & Temp & “”
DoCmd.OutputTo acOutputReport, “RptQuoteHTML-TitleShort”, acFormatHTML, MyPath & MyFileName
Me.Report.Caption = Title
rs.MoveNext
Loop
DoCmd.Close acReport, “RptQuoteHTML-TitleShort”
Set rs = Nothing
Set db = Nothing
End Sub
Private Sub Report_LostFocus()
Me.Report.Caption = Title
End Sub
How do I Add “meta name=”keywords” content=”Some Keywords, Keywords, Etc…
and “meta name=”description” content=”Some Description”
To Report Code?
I have created the following code in Access 2013 that generate a series of HTML reports from a Table which works fine, and generates a Some Item.
Now I want to add
and
Private Sub Report_GotFocus()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim MyPath As String
Dim Temp As String
Dim MyTitle As String
MyPath = “Z:\Local\Quotes\QuoteShort\”
MyTitle = Title
Set db = CurrentDb()
Set rs = db.OpenRecordset(“SELECT InvID, Title FROM TblQuoteHTMShort”, dbOpenDynaset)
Do While Not rs.EOF
Temp = rs(“InvID”)
MyFileName = rs(“Title”) & “.htm”
DoCmd.OpenReport “RptQuoteHTML-TitleShort”, acViewReport, , “[InvID]=” & Temp & “”
DoCmd.OutputTo acOutputReport, “RptQuoteHTML-TitleShort”, acFormatHTML, MyPath & MyFileName
Me.Report.Caption = Title
rs.MoveNext
Loop
DoCmd.Close acReport, “RptQuoteHTML-TitleShort”
Set rs = Nothing
Set db = Nothing
End Sub
Private Sub Report_LostFocus()
Me.Report.Caption = Title
End Sub
How do I Add
and
To Report Code?
Hi..
I’m a student of computer application and is doing project in vb.net10 and was using access2007 until i formatted my laptop. Now it’s installed with access13. So i would like to know is access07 compatible with access13?? How do i do database connection to vb.net10?
Pls help a.s.a.p.
Thank you.
Yes you can open Access 2007 database in Access 2013
this video will explain you how to do connection:
https://www.youtube.com/watch?v=9HX8IYIMWiE
===============
here is full text:
To answer your questions, there are a number of different methods to “connect with MS-Access”, but I’ll assume for this conversation that you want to connect to an MS Access database and execute SQL statements against it. This is very easy to do in VB.NET and in the following code is a simple example of how you can create a VB.NET class to execute SQL statements against an Access database:
Code:
Public Class DatabaseConnector
”’
”’ Returns an OLEDB connection string to an Access 2010 database
”’
”’ The OLEDB connection string to the Access 2010 database
Private Function GetConnectionString() As String
‘ Create the Connection string
Dim strConnection As String
strConnection = _
“Provider=Microsoft.ACE.OLEDB.12.0;” & _
“Data Source=C:\Test\DatabaseFile.accdb;” & _
“User ID=Admin;Password=;”
‘ Return the Conntection string
GetConnectionString = strConnection
End Function ‘ End of: Private Function GetConnectionString() As String
”’
”’ Allow the user to execute non-record returning queries
”’
”’ The SQL Statement to execute against the database
”’ True if successful, otherwise False
Public Function RunSqlNonQuery(SqlCode As String) As Boolean
On Error GoTo HandleErrors
Dim bResult As Boolean
Dim cmd As OleDb.OleDbCommand
‘ Create the OLEDB Command object
cmd = New OleDb.OleDbCommand(SqlCode)
‘ Open the Connection
cmd.Connection = New OleDb.OleDbConnection(GetConnectionString())
cmd.Connection.Open()
‘ Execute the SQL Statement
cmd.ExecuteNonQuery()
‘ It looks like we’ve succeeded – return True
bResult = True
ExitFunction:
‘ Close the connection
If (Not IsNothing(cmd.Connection)) Then
If (cmd.Connection.State ConnectionState.Closed) Then
cmd.Connection.Close()
End If
End If
‘ Return the result and exit
RunSqlNonQuery = bResult
Exit Function
HandleErrors:
‘ Handle any errors here…
MsgBox(“An error was raised!” & vbNewLine & “Message: ” & Err.Description, MsgBoxStyle.Critical, “Error”)
Err.Clear()
bResult = False ‘ Return failure
Resume ExitFunction
End Function ‘ End of: Public Function RunSqlNonQuery(SqlCode As String) As Boolean
End Class ‘ End of: Public Class DatabaseConnector
So, in the above code, notice the “GetConnectionString()” function just returns a connection string to an Access 2010 database. Notice that this connection string provides the full file path to the database file itself, specified through the “Data Source” parameter. Then notice the “RunSqlNonQuery()” function. This function will allow you to execute non-record returning SQL queries against the database. So then to use this DatabaseConnector class to connect to an Access database and execute SQL statements against it, you could create the following VB.NET code in a form:
Code:
Dim dbConnector As New DatabaseConnector
Dim strSql As String
‘ Create an INSERT SQL Statement
strSql = _
“INSERT INTO [Table1] ( [Field1], [Field2] ) ” & _
“VALUES (“”Field 1 Value””, “”Field 2 Value””); ”
‘ Execute the SQL Statement against the Access database
dbConnector.RunSqlNonQuery(strSql)
So, in this above code, you just create a new instance of the “DatabaseConnector” class, create a SQL Statement (that in this case is non-record returning, like an INSERT or UPDATE statement), and then call the “RunSqlNonQuery()” method. Of course, if you wanted to run just a SELECT statement, you would have to create another method in the DatabaseConnector class that returns a DataReader (or something like that), but hopefully this example provides you with enough information to get you started. So, I hope all of this code helps, but please let me know if you have any other questions about this and I’ll do what I can to help.
any question write to iamengineer707@techie.com
I’m not sure why this had to be tied to Sharepoint. Why would we want to import our tables into Sharepoint. We want to use the tables in SQL server where they are stored natively. ADPs allowed you to do this. I don’t see a problem with moving deployment to the web, but why did Microsoft require that it be tied to Sharepoint. That pretty much makes the decision for us to move all things that we did within ADPs into something else.
The tables for Access web apps in 2013 are stored in SQL Server, not SharePoint. The only thing that is hosted on SharePoint are the webforms.
Thanks
Juan
Hi there. I have been using Access in my business for 20 years and in 2004 converted the Access file to an ADP file linking to Microsoft SQL Server. This has worked extremely well (we have about 15 PCs using various versions of the ADP file to link to SQL Server). With Access discontinuing support for ADP is there a quick and easy way to convert so that we can continue to use our data in SQL Server?
Hi Tom, I found this link to have ADP in Access 2013.
http://www.joakimdalby.dk/HTM/ADPX.htm
Access 2013 SQL is a joke. Stuff that worked in my sleep on DB/400 simply does not work in Access. I prefer db engines that do notrequire 90% of my time to be spent solving the quirks of the engine.
i use access for all my DB reporting and i love it. my only challenge is the 2 gig limit as i use access for gigantic applications b/c of the GUI interface.
does access 2013 have a solution for this challenge? is it to buy sql server backend even if it’s for a local installation?
Access has always supported using SQL Server or any other RDBMS as the data store for long time so if you are exceeding 2GB, you definitely should move the data from Access backend to a RDBMS product. Be aware there are several free RDBMS, including SQL Server Express which allow up to 10 GB or PostgreSQL or MySQL.
If you use those locally, then the performance shouldn’t be that different but when it’s shared with other users, you may find your Access program a bit slower after you move the data and use linked tables and would need to take some steps to optimize the application for server-client architecture. You can read up on this: https://accessexperts.com/starthere/
10 years ago I launched an full academic management system – enrolment management, student management, academic management, course management, alumni management, donor-base management etc. – all based on MS Access 2003. I designed it with full user-level security enabled. It was (and still is) deployed across our local network with a back-end .mdb and a number of customized front end .mdbs. Up until now our school has been able to limp along using the .mdb databases in A2007 and then A2010. Now, however, with A2013, I am not sure which way to turn. The application is still in active use but is in desperate need of an upgrade.
Do you have suggestions for me as to where I should go with this. I am not familiar with MS SQL Server or SharePoint. Any advice would be greatly appreciated.
Loren
Loren,
It depends, actually. You have to identify what goals you want to achieve out from your program and which technology more closely aligns with your needs both present and future.
If you have a small database and the data model is relatively shallow, meaning that you don’t have many tables that relates from A to B to C to D but only A to B and A to C and A to D, then you will find SharePoint much more accommodating. You can migrate your data to SharePoint with minimum change to your program and use it as a linked list. This also give you ability to work offline and is easy to service.
On the other hand, if your data model is complex, need more customizability and hold large set of data, then you might want to consider SQL Server. The downside is that you’d have to perform a number of tasks to successfully migrate your application to a different architecture. Most of time, the first time moving data from Access table to SQL Server tables results in a degradation of performance, especially if the program wasn’t designed for server-client architecture from start. This then becomes necessary to retrofit existing objects so that they can leverage the strengths of server-client architecture efficiently. Obviously, this takes more investment upfront but in long run, allows for much more flexibility and extensibility.
Consider contacting us via the Contact Us if you need additional assistance. I hope this helps.
Hi
Surely MS Access is about small, relatively local database apps… a SQL backend and ability to use a host of other MS products (SharePoint, Excel etc) to do the job is just missing the point.
Its great if you didn’t want Access in the first place (because you were delivering larger-scale solutions)…
Removal of pivot tables in A2013 just resulted in me removing the software and asking for a refund.
S.
With Access 2013, I have found that you have the best of both worlds actually. You can still use the Access client to maintain a local database with the functionality of the previous versions. The new PowerPivot features in Excel makes the Pivot table feature in Access obsolete.
Now if there only was a way to link the tables from two web app databases together…
Ryan, indeed!
RE: linking two web apps, there are two options you can consider.
1) You could have the shared data saved in a SharePoint list. Access 2013 web apps allow you to link to SharePoint lists as read-only data source and is usable even in web browser. You can then use a web browser control to point to a form if you need to edit data in the SharePoint lists.
2) You could manually synchronize data between two web apps. We cover that in Professional Access 2013 Programming book.
HTH.
I have not tested this yet but has anyone notice if you can implement a row-level security out of the box?
Being a web app, you can provide something akin by binding your views to a query that filters rows based on user’s name or email address. You need to consider whether this is appropriate (there are environmental considerations to account for; email address might be OK in O365 but not so great in an on-premises installation) and to do so, you have to use popup views. So yes, it’s achievable.
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!
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
As an O365 Enterprise subscriber, I am finding the same issue that Robert pointed out. Using Access “create reports” function to create a client-only database, the connection is read-only when linking to the SQL-Azure tables. Thus, I haven’t found any way to perform action queries (append / delete / update) or write SQL commands through the Access client. Are we supposed to use SQL Management Studio to perform these queries?
Hi Juan and RyanR,
I confirm what RyanR wrote about the Read/Write problem using Access 2013 Custom Web Apps with an Offic 365 E3 account.
If you go to File-Tab -> Info -> Manage Connection you should be able to set the access to ANY LOCATION and enable READ/WRITE but this won’t work.
If you try to access the database from an Access 2013 Desktop database using the SQL Native Client 11.0 as suggested, you are not able to get a connection. The error message tells you to fix the firewall on Azure in order to get access. But that’s what the ANY LOCATION button is for.
Juan, do you have any information on where the database is actually stored and how to access this? It must be Azure but if you log into the portal with the O365 credentials, the db is not there!
The web app concept is really compelling but I think it is a beta version. You are not able to store attachements or edit the features of the form fields.
I hope we see some more features in 8.1!
Lars
Lars,
I was able to get access to the back-end through SQL Server Management tools, and was at least able to query records using T-SQL. I haven’t tried to write/change records in the DB yet. The interface with the SQL Azure DB through Management Studio is impoverished as many of the features such as “intellisense” won’t work. You basically have to write commands in T-SQL. I am considering signing up for a trial account on Windows Azure to see if I can gain access to my DB and perform action queries for there. The interface is supposed to be a lot richer (google “Project Houston”) Anyone try this to see if it will work?
RyanR,
The Problem is not to get READ Access to the backend. This is working well once you click the reporting button provided at File-Tab -> Info -> Manage Connection.
My aim is to get access through a desktop app with linked tables.
I also tried to use the Management Studio for the Connection but this failed as well as with Access 2013.
The other issue is to access the database from any IP as I don’t have a static IP available.
Regarding your attempt with Project Houston I would love to see how it works and how far you get.
Lars
Robert, Ryan and Lars –
Reports uses the Reader’s credential, not the Writer’s credential.
The simplest way to update the connection so that the Writer’s credential is used. Use the following VBA snippets to get you started.
To get the current connection string, execute in Immediate Windows:
?CurrentDb.TableDefs(“NameOfLinkedTable”).Connect
Then use this procedure:
Private Sub QuickAddTable(strTableName As String, strConnectionString As String)
Dim tdf As DAO.TableDef
Dim db As DAO.CurrentDb
On Error Resume Next
DoCmd.DeleteObject acTable, strTableName
On Error Goto 0
Set db = CurrentDb()
Set tdf = db.CreateTableDef()
With tdf
.Name = strTableName
.SourceTableName = “Access.” & strTableName
.Connect = strConnectionString
End With
db.TableDefs.Append tdf
End Sub
Use this untested aircode to test and adjust your tables. You can wrap it in a loop if you needed but hopefully that give you enough to get the working connection string and change the user credentials.
BTW, I have to advise that it may not be necessary secure to save the user and password as part of connection string. If you want to avoid that problem, refer to this article for tips which enable you to NOT save the user/pwd in the connection string.
http://blogs.office.com/b/microsoft-access/archive/2011/04/08/power-tip-improve-the-security-of-database-connections.aspx
Best of 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
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
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.
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.
[…] working nature. AccessExperts blogger Juan Soto and his friend Ben Clothier expounds more on the improvements that we can expect on the latest version of […]
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!
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
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
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
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.
Hi Ben
For a Microsoft Access 2013 database with all functionality localised within one place including automation and complex calculations, having to “jump out” to Microsoft Excel is a step backward…especially considering it used to be there.
Joe
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.
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.
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
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?
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.