Go to Top

10 Things we LOVE about Access 2013

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)

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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!

About Juan Soto

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.

61 Responses to "10 Things we LOVE about Access 2013"

  • Chris
    January 18, 2016 - 10:52 pm Reply

    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

  • Chris
    January 18, 2016 - 10:20 pm Reply

    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!

  • Jon Beal
    March 22, 2015 - 10:52 pm Reply

    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?

  • Jon Beal
    March 22, 2015 - 10:39 pm Reply

    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?

  • akanksha
    February 21, 2015 - 9:19 am Reply

    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.

    • Techy guy
      January 14, 2016 - 4:26 am Reply

      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

  • Coston
    October 18, 2014 - 12:16 am Reply

    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.

    • Juan Soto
      October 20, 2014 - 8:28 am Reply

      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

  • Tom Evans
    November 10, 2013 - 10:25 pm Reply

    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?

  • Joe Smith
    November 7, 2013 - 10:25 pm Reply

    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.

  • todd f
    September 11, 2013 - 2:10 am Reply

    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?

    • Ben Clothier
      September 11, 2013 - 2:20 am Reply

      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/

  • Loren
    September 6, 2013 - 10:29 pm Reply

    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

    • Ben Clothier
      September 10, 2013 - 6:03 pm Reply

      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.

  • Steve
    August 2, 2013 - 12:32 pm Reply

    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.

    • Ryan
      August 5, 2013 - 8:38 am Reply

      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…

      • Ben Clothier
        August 23, 2013 - 7:33 pm Reply

        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.

  • Miguel
    June 16, 2013 - 1:29 pm Reply

    I have not tested this yet but has anyone notice if you can implement a row-level security out of the box?

    • Ben Clothier
      June 18, 2013 - 3:36 am Reply

      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.

  • Joe Quinn
    March 24, 2013 - 10:13 am Reply

    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!

  • Robert Curry
    March 16, 2013 - 10:28 pm Reply

    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???

    • Juan Soto
      March 18, 2013 - 12:16 am Reply

      You could use reporting services right now, since the data is already stored in SQL Server. Thanks for the great tip!

      Juan

    • RyanR
      June 5, 2013 - 9:22 pm Reply

      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?

      • Lars Hesse
        June 10, 2013 - 9:02 pm Reply

        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

        • RyanR
          June 10, 2013 - 9:18 pm Reply

          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?

          • Lars Hesse
            June 10, 2013 - 11:33 pm

            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

          • Ben Clothier
            June 11, 2013 - 1:50 am

            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!

  • John
    March 11, 2013 - 5:12 am Reply

    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!

    • Juan Soto
      March 11, 2013 - 7:53 pm Reply

      Hi John!

      No, desktop app is not that same as web app
      No, SQL Express is not enought, you need SharePoint too.

      Thanks!
      Juan

  • Rx_
    March 8, 2013 - 2:24 am Reply

    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

    • Juan Soto
      March 8, 2013 - 11:48 pm Reply

      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

  • Rx_
    March 8, 2013 - 2:16 am Reply

    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.

  • Amit Dangwal
    February 26, 2013 - 11:30 pm Reply

    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

    • Ben Clothier
      February 27, 2013 - 7:54 pm Reply

      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.

  • Microsoft Access 2010 and 2013 Differentiated :
    February 18, 2013 - 4:02 pm Reply

    […] 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 […]

  • Mike McNett
    February 12, 2013 - 8:42 pm Reply

    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

    • Ben Clothier
      February 13, 2013 - 12:26 am Reply

      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!

  • Daniel Kuhlmann
    February 2, 2013 - 6:40 am Reply

    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.

    • Juan Soto
      February 2, 2013 - 6:53 am Reply

      Hi Daniel

      You can try out AccessHosting.com free for 30 days. Disclaimer: we are partners with them.

      Sincerely,
      Juan Soto

  • Rui
    October 2, 2012 - 1:41 pm Reply

    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.

  • Yjk
    September 11, 2012 - 10:52 pm Reply

    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?

    • Juan Soto
      September 11, 2012 - 11:42 pm Reply

      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

  • david
    September 7, 2012 - 3:13 am Reply

    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.

    • Ben Clothier
      September 7, 2012 - 3:20 am Reply

      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.

      • Joe
        November 11, 2014 - 3:39 pm Reply

        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

  • Andy
    August 28, 2012 - 3:32 pm Reply

    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.

    • Ben Clothier
      August 28, 2012 - 6:53 pm Reply

      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!

      • Andy
        September 4, 2012 - 12:58 pm Reply

        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

        • Ben Clothier
          September 7, 2012 - 3:21 am Reply

          Thanks, Andy!

          The more feedback we give to Microsoft, the better. Hopefully that’ll be done sooner than later.

  • rene prado
    August 25, 2012 - 5:32 pm Reply

    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

    • Juan Soto
      August 25, 2012 - 5:57 pm Reply

      Hi Rene
      Which country do you live in?

      Juan

      • rene prado
        August 26, 2012 - 9:43 pm Reply

        Juan Good day to you, I live in the city of Davao, Philippines.

  • LInda Ewen
    August 22, 2012 - 9:20 pm Reply

    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.

    • Ben Clothier
      August 22, 2012 - 9:46 pm Reply

      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.

      • Rene Prado
        August 24, 2012 - 9:28 am Reply

        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

  • Giorgio
    August 22, 2012 - 2:42 pm Reply

    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?

    • Ben Clothier
      August 22, 2012 - 7:34 pm Reply

      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.

      • Giorgio
        August 23, 2012 - 1:58 pm Reply

        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?

  • Patrick Wood
    August 21, 2012 - 11:00 pm Reply

    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.

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
[gravityform id="16" title="false" description="false"]