Author Archives: Kevin

MS Access: How to Create Query Joins

Posted on October 27, 2015 by Kevin

IC285555

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.

Types of Queries

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.

  • Inner – An inner-query join allows you to search both tables and get results that match the query from either of the tables.
  • Left – A left-query join returns results that match the query from the left table and all matching rows from the right table.
  • Right – A right-query join works the same as the left join, but it uses the right table as the starting point.
  • Full – A full-query join returns any results where there is a match in one of the tables.

Exploring the “Join Properties” Dialog Box

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:

088f9d7e-3dea-4072-b5ff-38dab90cd459

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.

Query_Join

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.

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

Access 2007 and 2010 Running on the iPad

Posted on July 31, 2012 by Kevin

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.

 

Posted in Video Tutorial | Tagged | Leave a comment

Access 2010 and SharePoint 2010 Service Pack 1

Posted on July 18, 2011 by Kevin

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.

Complete list of Office 2010 SP1 fixes

As a final note, Office 365 is currently running on the latest Cumulative Update for SharePoint 2010, not Service Pack 1.

Posted in Featured, News | Leave a comment

Demystifying currentwebuser(x)

Posted on April 14, 2011 by Kevin

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

Important Tip: Queries and the size of your Access 2010 Web database

Posted on March 5, 2011 by Kevin

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

Posted in FAQ | Leave a comment

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

Posted on February 15, 2011 by Kevin

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

Posted on February 5, 2011 by Kevin

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