Category Archives: Tips & Tricks

First Look at Access 15 / Access 2013’s Metro Design

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.

Posted in Tips & Tricks | Tagged , | 2 Comments

Introduction and Getting Started Video for RDP Hosting

This video introduces you to our Remote Desktop Hosting service. It shows you how easy it is to get started using RDP hosting with our free trial and how to easily copy/paste your local Access database up into the cloud.

To learn more visit our website

Posted in Tips & Tricks, Video Tutorial | Tagged , , | Leave a comment

Moving Your Microsoft Access Database to the Cloud

AccessHosting.Com offers a number of different approaches for moving Access 2003, 2007 and 2010 databases off of your desktop or corporate network and onto the web. Moving your Access database to the cloud has wide ranging benefits including increased security, high availability and support for multiple users and devices. This document will describe the pros and cons of each approach and present a series of features to consider based on the individual requirements of your application.

Solution #1: Running Access 2010 against hosted Access Services/SharePoint 2010 Enterprise

Pros: Browser based forms/reports. One version of the application automatically syncs changes to all users. Can develop hybrid applications with a mix of web and client functionality. Mobile Device Support via browser.  Secure Active Directory logon with self-service password management. Multiple backup options available.

Cons: Limited to performance constraints of SP 2010 lists. Conversion to SharePoint compatible format required for existing databases. Existing client based forms and reports must be rewritten for web support.

Solution #2: Running Access 2003, 2007, or 2010 applications in a Remote Desktop.

Pros: Extensive device support via Remote Desktop clients for iPad, iPhone and Mac OSX. No need to modify existing applications or convert database – quickest way to get up and running. Multiple users supported with secure logon and common drive configuration for Front End/Back End deployment. Backup files to any cloud based storage service (Amazon S3 Recommended).  Secure Active Directory logon with self-service password management. Support for Access 2003 applications.

Cons:  users must run Remote Desktop client (no browser based application support). Backup must be performed by the Access administrator or inside the application due to locking scenarios.

Solution #3: Running Access 2007 or 2010 against hosted SQL Server 2012

Pros: Best Scalability and Performance with the power of SQL 2012. Secure Active Directory logon with self-service password management. Multiple backup options available. Upload large amounts of data.

Cons: Cannot sync application changes automatically to front end clients. ACCDB or MDB files must be converted to SQL compatible format.

Posted in Featured, Tips & Tricks | 2 Comments

Demystifying currentwebuser(x)

As many of you know currentwebuser(x) can be used inside your Access Web Database as follows:

0 gives current user’s member ID.
1 gives current user’s display name.
2 gives current user’s login name
3 gives current user’s e-mail address.

The FAQ that we have been getting is “How do I define this information to SharePoint 2010?” or “How can I allow my users to self-maintain their current/preferred email address and use that as a variable inside my Access 2010 Web Application?”

Here is a short video which shows how to do this and how to change your time zone to eliminate the errors that occur when your native time zone is out of sync with the server.

Posted in Featured, Tips & Tricks, Video Tutorial | Leave a comment

Get an Access Database on the web in less than 5 minutes

How it Works
When you publish a web database, Access Services creates a SharePoint site that contains the database. Every database you publish will have it’s own unique URL (i.e. demo.accesshosting.com/database1/). All of the database tables, forms, reports and queries move into SharePoint as part of the publishing process.

After you publish, SharePoint visitors can use your database, based on their permissions for the SharePoint site. They can visit the web database’s URL or you can distribute an .accdw file that they can open in Access 2010 or the free Access Runtime.

Get a Web Database online in 5 Minutes!
After receiving your credentials from Access Hosting, you can get a valid web database online in minutes. Simply download our Contacts Web Database template and follow our simple step-by-step instructions:

Step 1: Download Access Hosting’s Contacts Web Database
Step 2: Open the Database in Access 2010
Step 3: Publish the Database to the Web (watch video)
Step 4: Refer to the ‘Getting Started’ Tab for help

This database is a simple shared address book in the cloud. You can add/edit contacts, search the database, add comments and print reports.

Posted in Tips & Tricks | Leave a comment

Charting inside Access Web Databases using SharePoint 2010

This tutorial walks through the process of creating an Access Web Database, connecting tables in that database to a SharePoint Chart Web Part, and then incorporating that chart back inside an Access 2010 Web Form:

Posted in Tips & Tricks, Video Tutorial | Leave a comment

Access 2010 Tips: 5 Tips to increase Productivity

1. Get to Know the Ribbon

If you skipped Office 2007 and are making the jump from Access 2003, the Microsoft’s Ribbon is a new feature and potential annoyance.  I know that when I first switched from Excel 2003 to Excel 2007, I hated the ribbon.  However over the years, I have found that it is actually a lot better than the old menu system once you become accustomed to it.  Of course the biggest problem in the transition is not knowing where anything is.  Luckily Microsoft has released an interactive reference guide to help you find the new location of commands in Access 2010.  You can get it here.

The most important tabs are Home, Create and Design.  Home is the….home of a lot of the common tasks and menu items found in other Office programs (think Cut, Copy, Paste, Text Formatting, Find/Replace).  Create is the starting point for all your new queries, tables, forms, and reports.  Design appears when you create a new objects and Access is smart enough to auto-select it after you create a new object.

2. Don’t like the Ribbon? Get it out of your way.

Simply Right Click the ribbon and select Minimize Ribbon to hide it and increase your work area in Access 2010.  You’ll still get to the ribbon, but it won’t always be displayed and taking up valuable screen real estate.

3. Customize the Ribbon

Right Click the ribbon and click the Customize Ribbon button to change it exactly to your liking.

4. The Quick Access Toolbar makes things Quick

Even when you get used to exactly where things are in the Ribbon, switching tabs to find menus and select them can require a lot more clicks than were needed in Access 2003.  Right click the Ribbon and select Show Quick Access Toolbar to display this small little shortcut of things.  You can right click the Quick Access Toolbar or the Ribbon and select Customize Quick Access to add shortcuts to all your frequent tasks.  We recommend adding things like Print, Save, Sync, Copy/Paste to the toolbar since they require 2 or more clicks and are very frequently used.  Sync is essential if you’re developing a web app, but you don’t have to take our recommendations – download our custom Quick Access Toolbar Right Here.

5. Don’t Miss the Quick View Change

You can quickly jump between Report View, Design View, Layout View and any other valid views by looking in the bottom right corner.  Each view is always just 1 click away!  You can also right click on any tab and change the view (2 clicks) rather than navigate the ribbon (3 Clicks) to change the view.

Posted in Subfeature, Tips & Tricks | 1 Comment

Access 2010: Interactive menu to ribbon guide

Click here to download the Reference Guide

If you’re getting lost in Microsoft Access 2010’s Ribbon then this interactive reference guide is for you.  It will help you transition from Access 2003’s menu system to the Access 2010 ribbon.  It’s a visual, interactive reference guide to help you find the new location of commands in Access 2010.  It’s a Silverlight/HTML program that lets you select a menu item in Access 2003’s menu and then plays a video showing you the location in Access 2010.  Very useful for folks making the transition.  You can download it from Microsoft here.

Posted in Subfeature, Tips & Tricks | Leave a comment

How to Create a Web Database from Excel

Lately we have gotten a lot of questions about how businesses can take their old access databases or excel spreadsheets and get them on the web with Access Hosting. The tutorial video above will hopefully demonstrate some of the issues with web compatibility and how to get your database ready for the web, but here’s a quick step-by-step breakdown on how to create a new web database from data that you have in Excel.  If you have your data already in an Access 2010 table, you can skip to STEP 10, but to get better conversion results you should check out www.access2010converter.com since it will preserve your forms, queries, etc.  This tutorial focuses purely on getting your data into a web compatible format so that you can upload it to Access Hosting and start enjoying our great service.

  1. Create a New Web Database in Access 2010
  2. Open up your Excel File that has your data
  3. Copy the data in Excel
  4. Switch over to Access
  5. Right Click in the left sidebar and select paste. This should paste over your MS Excel data into Access 2010
  6. Now before a Database is ready to be published to Access Hosting, it has to pass Access 2010’s Web Compatibility Checker
  7. Goto File -> Save & Publish -> Publish to Access Services; Click the Web Compatibility Check Button.
  8. View any Web Compatibility Issues and Fix them.  In the video above, my fields contained illegal characters such as ‘Pts/G’.  Changing these headings to ‘Points per Game’ fixed nearly all of the web issues.
  9. Run the Web Compatibility Again.  You should only get 1 error for the unique identifier ‘ID field’ requirement.  SharePoint requires that each record have a numerical ID associated with it.
  10. Highlight and Copy all the data in your table
  11. Create a new Web Table.  This should create an ID field that will automatically assign a numerical ID to each record.
  12. Right Click your table and select ‘Paste as Fields’
  13. Close and Delete the other non-web compatible table
  14. Run the Web compatibility checker – there should be zero web issues
  15. Publish to Access Hosting!
Posted in Subfeature, Tips & Tricks, Video Tutorial | Leave a comment

5 Reasons why Microsoft Access Schools Quickbase

Back in 1999, Intuit launched Quickbase – a web-based collaborative database application that allows business people to create their own custom applications without writing code. The application is hosted by Intuit and sold by subscription.  At the time, Quickbase had quite a few advantages over Access 97 (even Access 2000).  It was web based, allowed for collaboration, and didn’t require any programming knowledge (Visual Basic for Applications – VBA).  Did I mention that Access 97 was ugly too:

Access 2010 has evolved though and Microsoft is ready for a rematch.  Here’s 5 Reasons why Quickbase can’t touch Microsoft Access 2010 & Access Web Services:

1. Access 2010 can do everything Quickbase can with no programming.  Anyone familiar with Microsoft Office will instantly feel comfortable with Access’s user interface.  You can easily add records, create queries and with Access Hosting – you can easily collaborate with other users and sync your data online without any programming knowledge.  Unlike Quickbase, Access 2010 is still compatible with VBA and other programming languages – so power users shouldn’t feel snubbed.

2. Access 2010 is software – not Browserware. That means you don’t NEED an internet connection to work with your database.  Just like Quickbase, and Access App can work in the browser and sync online, but has the added advantage of working locally as well.  If your internet connection goes down or if you are on the road, you can continue to add records to your database.  Next time you connect to the internet, Access will sync with your online database and upload your new records.

3. Access Web Services has a Back-Up Plan.  With Access you don’t have to worry about service outages (Bad Quickbase). Unfortunately, Quickbase works entirely in the cloud on Intuit’s servers.  Imagine if your Small Business can’t access its application or data for days because of an outage (Intuit suffered outages on between June 16 and June 17, June 20, and July 14).  With Access 2010, everything is stored locally and can run natively in a Windows Client. You can still get some work done even if your server/host goes down.

4. Access has Macros.  Macros allow you to automate a lot of routine reports and  actions. In 2010, Macros may be represented with XML which lends them more credibility with programmers.

5. Access is Cheaper than Quickbase. Quickbase’s monthly subscriptions start at $299 for 10 users and 1 GB of space and they own/control the hosting, the online software, and your data.  You buy Access 2010 Once and can get hosting from us for $49 and get 1GB and 5 Users.  That’s $250 Savings per Month! Did I mention that we have 99% Uptime too – no outages here.

Posted in Subfeature, Tips & Tricks | Leave a comment

How to Delete Your Published Access Web Database from SharePoint 2010

This short video will walk you through the steps necessary to remove a published database from SharePoint 2010. This process should be used with caution as the database, when removed completely, is no longer recoverable from the recycle bin.

Posted in FAQ, Subfeature, Tips & Tricks | 3 Comments

Demystifying the Access 2010 runtime client

The introduction of web based forms and reports in Access 2010 is a huge step forward for the product, but there are many scenarios which dictate that an application be developed natively in Access 2010. Fortunately, the Access 2010 runtime client provides a mechanism for accomplishing several things at once:

1)      Native Access applications can be created and distributed to end users WITHOUT the need for a full blown copy of Access on the desktop. The Access 2010 runtime is a 175 megabyte download that allows you to distribute your Access application without buying a copy of Access 2010 for each client. This free download can be found at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=57a350cd-5250-4df6-bfd1-6ced700a6715&displaylang=en

2)      You can use accesshosting.com to securely store your tables and the Access runtime client will connect to SharePoint and authenticate the user when invoked.

3)      You can still have web based forms and reports alongside of the native functionality that was setup in the runtime client. This hybrid application approach brings you the best of both worlds (web client objects and native Access code) all running against SQL and SharePoint 2010 for optimal performance and scale. Lightweight users can simply use browser based functionality while power users tap into all the Access 2010 features using the runtime client.

If you have a copy of Access installed but want to see what the client experience looks like from the runtime side of things, just throw a switch on the command line like this:

C:Program FilesMicrosoft OfficeOffice14MSACCESS.EXE” “C:dashboard.accdw” /runtime

This command loads an Access Web Database (see our post on ACCDW files for more detail on that) but invokes the end user experience of the runtime client. Make sure you have setup your native startup form before invoking this command or you may be staring at a blank screen. For more information on setting the startup form check out our tutorial at http://blip.tv/file/4075108

In summary, the runtime client is an inexpensive (free) way to combine the power of native Access 2010 functionality alongside seamless connectivity to Access Services and Web Databases in SharePoint 2010.

Posted in Featured, Tips & Tricks | Leave a comment

Distribute Your Access 2010 Web Application with an ACCDW file

This short video will show you how to distribute your Access 2010 Web Application to other Access users by creating and ACCDW link. The ACCDW file is a small shortcut file that allows users to connect to your web database and completely hydrate a local, connected copy of the web application on their desktop. This is a very easy way to take your Access 2010 application from its development stage into production across a set of widely dispersed users.

Posted in Subfeature, Tips & Tricks, Video Tutorial | Leave a comment

Create an Access 2010 Table Using Copy/Paste

One quick and easy way to create a new table in your Access Web Database is to use the clipboard and copy/paste from Excel or any other tabular data source.

Posted in Subfeature, Tips & Tricks, Video Tutorial | Leave a comment

Delivering Email from Your Access 2010 Web Application

We recently published a short video tutorial on using data macros to trigger an email alert in Access 2010 (see below).  Since this behavior assumes that you are running on SharePoint 2010 Enterprise Edition and relying on SharePoint for email delivery it is worth taking a closer look at the best practices we have established for making sure emails are delivered as expected from the SharePoint 2010 Server. If you have published your application to our shared hosting farm we run a standalone instance of the SMTP service with verbose logging enabled. The net effect of this configuration is that we get a detailed report of the conversations between SharePoint, the SMTP Mail Delivery Service, and the destination email server. This information can be invaluable in determining why an email generated successfully from Access does not make it to the intended inbox. Common mail delivery issues include lack of a reverse DNS record, SPAM quarantine, and typos in the users email address. Many of these issues can be properly diagnosed and corrected by using an SMTP instance dedicated to Access Services. Disclaimer: There are instances where the email message is marked as SPAM and non-delivered without any notification back to the originating server as to why the message was rejected. We won’t mention any names but the worst offender of this is spelled Y-A-H-O-O. That problem is easily corrected by finding an alternative free email provider.

Posted in Subfeature, Tips & Tricks, Video Tutorial | Leave a comment

How much SharePoint 2010 storage will my Access application consume ?

We recently had a customer using our Developer Sandbox hosting plan run out of storage space as they published a web application to SharePoint. Further investigation of this problem uncovered a very interesting dilemma for Access Services developers: The size of the application on your local disk is typically much smaller than the size of your web application after it has been published to Access Services.

In this case, a 13 megabyte local Access application was exceeding a 25 megabyte storage quota after being published to SharePoint 2010. This raises the question: how do you tell how much space is your application is consuming on the server? Unfortunately, there is no magical command or menu option that will estimate the application size before publishing – but here are 2 techniques for determining how much storage your application is using AFTER it has been published to SharePoint:

Technique 1: Use the SharePoint 2010 Usage Analysis feature to determine how much space your site is using pre and post publish. The delta between the pre and post publish space consumption would then represent your published application size. The drawbacks to this approach are 1) It assumes that you are the only one adding content to the site collection while publishing your application. 2) SharePoint 2010 usage analysis is not real time and you will need to wait for the statistics to be updated before getting an accurate reading of the space consumed. SharePoint 2010 defaults to a 1X/day update for usage analysis so a 12-24 hour wait is typical. Here is a screencast that show exactly where to find the usage anaylsis information in SharePoint 2010.

Technique 2: If you have control of your SharePoint 2010 Server, you can use Powershell to figure this out:

1. Open PowerShell as administrator
2. If you didn’t open PowerShell via the “SharePoint Management Shell”, then snap-in:
Add-PSSnapIn Microsoft.SharePoint.PowerShell
3. Type Get-SPSite to show all SharePoint site collections in the farm
4. When he finds the one he wants, type $site = Get-SPSite –Id
5. Now the site in question is stored in a variable called $site. So just type $site.Usage.Storage and hit enter. The total space being consumed is shown down to the byte. If you want MB, then do $site.Usage.Storage / 1024 / 1024.

Hopefully this issue can be addressed in a future release of Access Services.

Posted in FAQ, Featured, Tips & Tricks | Leave a comment