Data Center & Security
Do you have questions or need help?
Latest Blog Posts
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.
This video will introduce you to our Remote Desktop hosting service which allows you to run legacy Access applications in the cloud WITHOUT MODIFICATION. In many ways the Apple iPad is the perfect client for running your Access Database. If you are short on time and need to bring your Access database onto the iPad quickly without the hassle and expense of database conversion this service is for you. Limited support for Access 2003 is also available, contact us for details.
We have been busy testing the latest Service Pack for SharePoint 2010 and Access 2010 and will be making this upgrade available to our customers in the near future. In the meantime we thought it would be useful to review some of the SP1 feature enhancements that will impact Access Web Databases.
New Feature: Site Recycle Bin
In SharePoint 2010 it is very easy to accidentally remove your entire site collection. This feature allows a quick and easy restore of the site collection instead of having to revert to a restore of your latest backup file. The backup/restore method was time consuming and required a rollback to the last site backup, this new approach will quickly bring your site back right where you left off before the accidental deletion.
New Feature: Improved Storage Space Allocation
SharePoint 2007 enabled granular management and insight into storage. In 2010 this feature was removed. In Service Pack 1 Microsoft is bringing back an improved StorMan.aspx, enabling users to better understand where their quota is going and act upon that information to reduce the size of their sites. In the case of web databases, this will allow you to see exactly where and how your storage quota is being consumed. The published/compiled size of an Access Database is often several times larger than it looks on the file system, so this new feature will help you visualize what is happening with your site collection storage allocation.
New Feature: Improved Publishing Performance for Client Forms with Embedded Images
This has been a common issue since the release of Access Services – in some cases the publishing process would just grind to halt if there were too many images on a form. Nice to see that it has been corrected.
New Feature: Offical Support for Chrome Browser
Many customers are already using Chrome successfully to render forms and reports inside the browser but it is nice to have Google Chrome offically recognized as a valid client platform for SharePoint 2010 and Access 2010 Web Databases.
As a final note, Office 365 is currently running on the latest Cumulative Update for SharePoint 2010, not Service Pack 1.
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.
This blog post is a follow up to a very important post/question: How much SharePoint 2010 storage will my Access application consume ?
Unfortunately, there is still no way to figure out how much space your local Access 2010 database will take up on the web before it is published to SharePoint. The good news is that after working with clients on this and their databases, I can tell you that queries are a big reason for the ballooning of the database file size. The more queries that you have in a Database, the more your database will increase in size once it is published to Access Hosting and SharePoint 2010.
The best way to describe this problem is to give a real word example. Recently a customer had a database that was about 40MB that they had built from scratch in Access 2010. The Database and tables passed the web compatibility checker (BTW – Microsoft’s Checker doesn’t catch everything, but that’s a story for another post). The database had over 500 Queries. This local Access database that was 40MB (under 10MB zipped) grew to be over 100MB after it was published to SharePoint. It more than doubled in size and took over 10 minutes to upload!
Please take into account that Queries seem to massively increase the storage space needed on SharePoint. The additional load times is also something to be weary of. If you are making a database from scratch, the best thing to do is create a few forms, tables, and queries and then publish the site to Access Hosting. The first publish takes the longest. After you have the basics published and your database started, you can continue to develop your database. As you work in Access to add new queries, tables and reports, all of these changes will be uploaded and synced to the server seamlessly.
TIP: Don’t build a huge complex database locally and then attempt one large (and long) publish
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.
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: