Data Center & Security
Do you have questions or need help?
Latest Blog Posts
Access 2013 introduced a new way to make a database available in the browser, called the Access Web App. This first article focuses the general features and points for consideration when developing your Access database.
With the release of Access 2013, you can create now create two different types of database applications, the first is the traditional desktop database. This has traditionally been called an Access database and consists of one or more files stored on your computer, network, or remote server with Access or the Access runtime installed so that you can open and operate these databases.
The second type of database application you can create is called a 2013 web app (not to be confused with the Sharepoint 2010 Web Database). These Web Apps can only reside within Sharepoint 2013 Enterprise or newer. They are not created and do not reside on your local computer.
A web app is cleverly designed so that everything is hosted on Access Hosting’s private cloud, so while you are using a desktop copy of Access to work on changing your design, all the changes you make once saved are automatically saved up to our Sharepoint solution. This means that there is no publishing process, but it also means that you can’t easily undo your changes (or mistakes). Keeping a backup of your own work is very important, but Access Hosting also backs up your entire site collection (not just the web app) every night so we can restore your entire site from a major blunder.
Your web app can be placed in your personal folder in Office 365, or created in a Team Site/Subsite. Team Site/Subsites allow for you to both collaborate with other licensed users and what are known as external users. An external user is someone with a FREE Microsoft Online account (easily obtained), and you are allowed between 500/10,000 external users depending on your subscription.
Access Web Apps run in your browser. This is where most of your users will interact with your application. Web Apps do not have the same robust design features as traditional desktop based applications. When designing a web app you will find yourself switching between your installed copy of Access on your desktop computer and the runtime browser window. You’ll have to have Access installed on your computer to make changes and design the Sharepoint Web App, but will often want to reload your browser to see your changes take effect and to visualize the end user experience.
The MS Access interface used to design web apps is very different than the traditional design tool so you will need to spend some time getting used to the new interface. It is relatively simple and offers standard form views and formats to get an Access web app up and running quickly in Sharepoint.
When designing an Access Web App, it helps to forget everything you already know about designing a traditional ms access desktop database application.
Web Apps do not have the customization options and power of a traditional access application (if you have a powerful Access application developed for the desktop, you can look at our Remote Desktop hosting which lets you leave your robust database application or custom software as-is while you move it to our private cloud). Don’t worry though, your ms access database still stores data in tables. In an Access web app, you can easily jump from table to table using the navigation included on the left pane of the browser. Tables can be re-ordered, hidden, have the captions changed and a graphical icon changed. This acts as the primary method of navigating between parts of your application.
You relate your tables together using a lookup. For those familiar with desktop databases then think of a web app lookup as a combination of desktop lookups and table relationships. There is no place in the web app to view all the lookups together, these are managed individually as part of the table design process.
Once tables are linked by lookups, Access will automatically create views of the data which link the data together using the lookups. For example in an order processing system, an order will have a lookup to a list of products in that order. Access will then automatically create a view including a list of related orders for each product.
If you don’t use lookups, then you will miss out Access saving you time by the process creating views of your data automatically. Once you have these different views of your data, you will find that some of them are exceptionally useful for viewing data from a different perspective. If you find something that you don’t like, you can remove it that view from the web app.
When you select a table, then on the top right of the main screen area next to the table selector is the View Selector for the chosen table. Microsoft Access will automatically create a List (Details) View and Datasheet View (Big Excel Sheet). You can then add to, remove, re-order, re-title and change the views.
The default List View comes with a built in search bar feature. By default, your web app will search every field for whatever you type in. Once again, Access does all the hard work for you.
A web app only supports one kind of primary key which is an auto-incrementing number (this is similar to the Autonumber data type found in a traditional desktop database and the Access Web Services 2010 primary key restrictions). The key field will automatically be named ID, but you can rename it.
Access has great features for importing data, but you should note that upgrading a database to a Web App is very much starting from scratch; You can import your data into your web app, but you will have to design all your views, reports, forms, and other functionality from scratch (mainly because web apps can’t do everything that a desktop database can). Before you try an import an existing desktop database, make sure that you change your desktop database so that every table has an autonumber primary key, and every foreign key is a long integer. If you don’t do this then you will run into problems.
Certain legacy data types are not supported, and those fields will not be imported. OLE Objects and Attachments are not supported. Instead there is a new Image data type which supports .gif, .jfif, .jpe, .jpeg, .jpg, or .png formats (notice that the bmp format is not supported).
If you have data in Attachments or OLE Objects, then these will need to be extracted and held outside the database in separate files, the exception is for supported image formats in
The web app data is held in a SQL database on Access Hosting’s servers, these are automatically managed as part of your Subscription and hosting plan. You can create as many web apps as you like with our plan and are only restricted by storage (which can be upgraded at any time).
To program a web app you use macros (VBA is not supported!). There are two different kinds of macros. User Interface macros manage how a user interacts with you application interface. Data macros are used to perform operations on your data.
While Access Web Apps DO NOT support VBA programming or provide a browser based reporting capability, you can use the Access Desktop application to link to your data online to perform more complicated actions. You can quickly and easily connect to your Access Hosting Web App to create reports and more. Check out this tutorial and video on how to use this feature.
Since it’s inception, Microsoft Access has been the go-to database for businesses of all shapes and sizes. Not surprisingly, as Access’s features and cloud capability have rapidly expanded over the years, so too have the different ways the technology is used. Here are a few of our favorites:
There are few things more valuable to a business than properly onboarding and training new employees. While for most companies the ROI on hiring and app developer for a training application wouldn’t be high enough to justify the cost, building them in house is an appealing option. And as the company’s hiring scales, moving the backend of the app to a server like Microsoft SQL Server will be a low-cost way to scale your training app with it.
For many small businesses, CRMs like Salesforce or Microsoft Dynamics are either too expensive or too cumbersome to implement. However, having accurate, up-to-date customer and prospect information is crucial to running client-facing departments . As an MS Access user, it’s more than likely that much of this data already exists there. By standardizing this information and creating relational tables for objects like leads and deals, you too can have a CRM without the cost and learning curve.
While we’re on the subject of prospects and customers, many of them will want to access some of the data that you store in your web database – for example, recent invoices or the date of their next appointment. You can easily deliver this data easily by offering them a form they can fill out on your site. ASP.NET forms can query a Microsoft Access online database directly, so by implementing them on your site, you can surface this data to your customers with limited effort from your developers.
If you’re like many, you’re using an endless string of VLOOKUPs in Excel to report on related data from different tables or sources. This is really a job for Microsoft Access’ relational tables. Built on top of Microsoft’s Jet Database Engine, you can use these powerful tools to easily create queries that quite literally fly.
If you’re in software development, one common practice you’re likely familiar with is creating waterfall models to manage the progress of an application. Since you may be creating an application that leverages Access or SQL Servers as your backend, why not keep track of your app’s progress there as well?
These are just a few of many ideas for uses for MS Access – the beauty of the software is that it’s uses are really only limited to the imagination<.
The database market is divided among four major players: MS Access, Oracle, SQL Server and MySQL. Due to accessibility and focus on user interface, MS Access has garnered some negative press as being unsuitable for professional level database development. However, depending on the needs of the organization, there are many compelling reasons to choose MS Access as your business database software.
MS Access is bundled with the MS Office suite, making it the most widely available desktop database program in the world. This also makes it one of the most affordable options for businesses, which tend to purchase businesses licenses of MS Office. The common interface among all the MS Office programs makes it easy to import and move data to and from the database without having to resort to specialized software. Due to the popularity of the product, support for MS Access is also very good, with plenty of online tutorials and articles available, in addition to Microsoft’s excellent customer service.
One of the main benefits of MS Access is its ease of use. Even users new to database development can pick up the basics quite easily, as everything is presented in an intuitive manner. Much of the user interface in MS Access is designed to assist users with creating and editing tables, and there are many templates available to ensure that the database meets organizational needs. MS Access also provides options for importing data from other databases, and provides troubleshooting tips for every step of the way.
Despite this focus on ease of use, MS Access users have access to powerful SQL tools that allow for rapid development. MS Access is also .NET friendly, meaning that developers wanting finer control of software development can take advantage of the ease of use and functionality of MS Access.
Due to the widespread popularity of MS Access, there are many third-party plugins and developmental tools that are available. This means that MS Access is far more flexible than other databases, making it easier to customize and adapt to the changing needs of your business. In addition, there are many more external consultants well-versed in MS Access, and they are generally more affordable than consultants for Oracle and SQL Server.
MS Access is an excellent database choice if your organization requires a database that is easy to use and that has established, reputable support structures. MS Access is powerful enough to meet the demands of most small to medium businesses.
Best of all you can utilize Access Hosting superior hosting infrastructure to further bolster your Access Database and get it in the cloud easily and securely. All of our services offer a free 30 day trial so that you can easily test everything with Microsoft Access to make sure that it meets your needs.
The foundation for any database software, like MS Access, is the ability to send a query. Organizing information and making it accessible on demand is what makes database software so powerful. Understanding how to craft queries can be challenging at first, but once you understand how to use select, action, parameter and aggregate queries, then you’re on your way to expertise. For truly complex queries and more complete data sets, you need to understand what query joins are, what they do and how they work.
When working with an online database, there are four basic types of query joins: inner, left, right and full. These descriptors tell you where the web database is looking for the requested information.
When creating join queries, you must first create joins between tables and relationships between fields. When the “Join Properties” dialog box pops up in the Access web app, you will see three sets of options:
First, you will need to select the left and right tables you want to join. There will be a drop-down list with all of the available tables. Designate the first one you want to work with as the left table and the second as the right table.
Next, you will need to select the columns to look at for each query. This allows the database on the web to only search for information in relation to those columns. There will be a drop-down list with all of the column names available. Select the column on the left and right tables you will be working with.
At the bottom of the dialog box are three options. These options are for creating a left, right or inner query. Select the type of query you want to use and move on to creating output fields.
Using join queries effectively allows you to do things like segment your mailing list for targeted marketing, find customer accounts more quickly and accurately, build customized reports and more.
Microsoft did not bite off any major changes between Access 2013 and 2016. Nothing major changed (like the move from Web Databases to Web Apps from 2010 to 2013) and there are mostly just some enhancements and more robust design options. Here are some of the features outlines by Microsoft:
Do things quickly with Tell Me
You’ll notice a text box on the ribbon in Access 2016 that says Tell me what you want to do. This is a text field where you can enter words and phrases related to what you want to do next and quickly get to features you want to use or actions you want to perform. You can also choose to get help related to what you’re looking for.
Can’t find a button? Click inside the Tell Me box (it’s the box at the top, with the light bulb). Type a button or command, like “filter”, and you’ll see all of your filter-related options listed for you.
Keep in mind that the the Tell Me box is not available in the ribbon when designing Access web apps.
Export linked data source information to Excel
Have you ever wanted to get a nice list of all the linked data sources from your Access database application into MS Excel? If you are working on a complex Access application, for example, that includes links to many different data sources, it can be helpful to have a nice list of all the various data sources and their types. This exported list can be especially helpful if you are working on an Access application you did not originally design. Now with Access 2016, you’ll find this task much easier using new functionality built into the Linked Table Manager dialog.
Open the Linked Table Manager dialog by clicking External Data > Linked Table Manager. Select the linked data sources you want to list and then click Export to Excel.
Once again, please note that this feature is not available when designing Access Web Apps. Are you seeing a trend here?
Larger Show Table dialog
In Access 2016, the default height of the Show Table dialog has been increased so you can easily see more table and query names in your database.
To view the Show Table dialog in queries, click Create > Query Design. The Show Table dialog opens by default. You can also open this dialog in queries by clicking Design > Show Table. To view the Show Table dialog in the Relationships window, click Database Tools > Relationships. The Show Table dialog opens by default. You can also open this dialog in the Relationship window by clicking Design > Show Table.
Once again, please note that the Relationships window is not available in Access web apps.
New visual themes and templates for the Access program
As mentioned a lot of the improvements to Access 2013 come to aesthetics. There are now two Office themes that you can apply to the Access program: Colorful and White. To access these themes, go to File > Options > General, and then click the drop down menu next to Office Theme.
If you want to organize and manage your data with Access but you’d like some help getting started with designing the database, try using a desktop database template. In Access 2016, five of the most popular database templates have been redesigned to have a more modern look and feel. To try these templates out yourself, go to File > New, and then search for any of these:
Each of these templates includes a new Getting Started form with links to articles, videos, and other community resources.
Access Hosting is proud to announce that for 2015 we have increased the storage quotas on all of our Remote Desktop Hosting plans for customers seeking an easy way to move their Access Hosting Database to the web without any modifications.
Access Hosting, the leading hosting company for Microsoft Access services and solutions has doubled and tripled the storage on their Access Remote Desktop plans. Customers in need of bringing their Microsoft Access web application to the cloud can now do so without the need for expensive hardware or costly setup.
Interested customers will now enjoy 2GB of storage with the $29/month Access Pro Virtual Desktop and 3GB of storage with the $49/month Office Pro Plus Remote Desktop plan. Anyone interested in trying out this hosting service can sign up for a free 30 day trial at http://accesshosting.com/remote-desktop-hosting/free-trial
Remote Desktop Services allows you to keep your Access 2007, 2010, or 2013 Database running “as-is” online in the cloud. Using the latest advances in Microsoft Hypervisor technology we can create a seamless single click operating environment for your existing Access database that allows you to run your application without modification. This approach allows multiple users to connect concurrently to the cloud from any PC and experience the program as if it was running locally on their desktop. Pricing starts at just $19/month and we offer a free trial for qualified customers. You can learn more at http://accesshosting.com/remote-desktop-hosting/
Here’s a video demonstrating how to add new created Active Directory users (that paid hosting plan customers can request via our helpdesk) to your SharePoint 2013 and Access Services 2013 database.
We are happy to report that our Remote Desktop hosting can be configured to connect to Office 365 to use Access to report on your Access 2013 web apps. Our Remote Desktop hosting allows you to use the power of Office 365 as your backend and then you can use our HTML5 PowerRDP technology to get around Office 365’s inability to render Access 2013 reports in the web browser. This is a great way to get more out of your Office 365 subscription with MS Access 2013. If you already have a $29/month Access 2013 Pro or Office Pro plan, you should be able to follow the quick tutorial below to start building web reports for Access 2013 web apps.
Step 1: Open your Access Web App in MS Access 2013
Login to Office 365 and Navigate to your Access Web App. Click the customize in Access button to download the Access 2013 .accdw file and open it in Access 2013.
Step 2: Report on your Access Data
Navigate to the Info tab of your Access 2013 web app file (accdw) and select the “Report on my Data” button to create an Access frontend that can report on your Office 365 / SharePoint 2013 backend.
Step 3: Create Reports in your Access Frontend
Now that you are using linked tables to connect to Office 365 and your SharePoint 2013 Access Web App, you can take advantage of all the features of the full version of the Access 2013 desktop client. Create and design your reports, forms and queries. Remember to close and save all reports and save your Access frontend file on the Remote Desktop.
Step 4: Reporting now works on Office 365
Now your reports are connected to Office 365 and accessible from any machine, tablet, device and web browser via our Remote Desktop hosting!
One of the most important changes with Access 2013 and their new web apps is in the architecture itself.
In SharePoint 2010, Access Web Services was storing everything in SharePoint (and then SharePoint’s content was backed up by SQL). Access 2013 apps are hosted by SharePoint 2013 while the data is stored in SQL Server 2012. SharePoint 2013 provides authentication, authorization, and security for Access 2013 apps. The back-end tables, views, macros, and queries are stored in an SQL Server 2012 database. This change in architecture reflects a substantial change in the way Access Services works.
You will notice that SQL stores tables, views, macros and queries but there’s no mention about reporting. That is because Access 2013 Web Apps do not allow you to create traditional Access reports. The only way to do reporting is by connecting the local Access software program to the SQL tables directly or using some other compatible reporting software tool.
There are a lot of advantages of using Access 2013 with SharePoint 2013, but it is important for Access Developer to note the following:
Access Hosting is committed to supporting the Access Developer community and will continue to offer SharePoint 2010 Access Web Services hosting for as long as developers are interested. SharePoint 2013 Access Web apps do have a ton of advantages, and we have launched our brand new Access 2013 SharePoint 2013 hosting packages. If you are interested in a free trial, please sign-up here.
We don’t just offer SharePoint hosting either. We have great ways for every Access Developer and Access application to be hosted in the cloud whether it’s through SharePoint, SQL Server or our RDP solution which lets you host any Access 2007, 2010 or 2013 application without ANY modifications. It supports VBA code too.
We are happy to announce that our Remote Desktop Virtual Machine has undergone some price changes and is now cheaper than ever. Our RDP Virtual Appliance is only $299/month and comes with 4GB of RAM and up to 10 users. Access 2010 or Access 2007 will come pre-installed on every remote desktop. With RDP, there are absolutely NO CHANGES necessary to your Access Application to get it running in the cloud. Simply copy/paste it over to your Remote Virtual Desktop.
Additional users are now only $10 per month! and the entire Microsoft Office suite (Word, Excel, Outlook, and Powerpoint) can be added to any virtual desktop for only $10 per user per month! These plans come with No setup fee and NO long term commitment. Best of all, you can install your own software and completely customize your RDP environment.
When you first boot up the Access 15 Public Preview, you will be greeted with the option to create a blank web database, a blank desktop database or select one of the Desktop or Web templates that are available. It is interesting to note that databases now seem to be either Desktop or Web applications. I was not able to get any of the web templates or even a blank web database to load. It appears that you have to be connected to a hosting service or something to get this to work at all.
I was able to load up a Desktop template and while Access 2013 does feature a spiffy new Metro interface, the standard desktop features that everyone has come to expect in Access seem to all be intact.
The ribbon in this new version of Access 2013 is laid out nearly identical to Access 2010. Everything seems to be located in an easy to get to place and any users of Access 2010 should feel pretty at home even with the new Metro styled interface.