Category Archives: Access 2013

How to Decrypt a MS Access Database

As a reminder, it is critical that you remember your password  — if you forget your password, there is no method by which it can be retrieved.

Step 1: Select the Microsoft access database file that you want to open and choose Open Exclusive.

Open encrypted access database

Step 2: Once you select the encrypted access database and are ready to open it in Microsoft Access. A dialog would pop up to ask for database open password.

Step 3: Type database password in Enter database password box. Click OK.

enter-password-to-open-access-database

Step 4: Remove a password from a database

When you remove a password from a database, you can restore it at any time (or replace it with another password) by repeating the steps in our post about How to Encrypt an Access Database.

Go to the File->Info section and select Decrypt Database.

decrypt ms access database online

After prompting you for the current password one last time, all encryption will be removed and your database will no longer require a password to open.

Posted in Access 2007, Access 2010, Access 2013, Access 2016, Tips & Tricks | Tagged , | Leave a comment

How to Encrypt an Access Database

If you are using our Remote Desktop Hosting to host your database and you want increased security, you may want to consider protecting and encrypting your MS Access database with a password.  If you know the password for an encrypted database, you can also decrypt the database and remove its password. This article explains how to encrypt a database by using a database password, and how to decrypt a database and remove its password.

In earlier versions of Access, you could create user accounts and passwords using a feature called user-level security. This topic does not discuss user-level security, which is not available when you use the .accdb file format.

If you encrypt a database and then lose the password, you will be unable to use the database. You cannot remove a database password if you do not know the password. Access Hosting cannot recover or decrypt an Access Database on our servers without your database password.

How to Encrypt an MS Access Database File

This is a process of setting a password to encrypt access database, restricting access to Access database.

Step 1: When access database file is open in Microsoft Access, tab File > Info.

Encrypt Password location in Access 2010

Encrypt with Password location in Access 2010

Encrypt Access 2016 database

Encrypt with Password Location in Access 2016 is the same place

Step 2: Click Encrypt with Password button under the info tab for the database.

access-database-open-prompt-message

Sometimes, maybe you will receive a message to ask you to open the access database with Open Exclusive mode at first, otherwise you cannot encrypt database. When this happens, click OK and follow the prompting message.

Step 3: In Set Database Password dialog, enter a powerful complex password in the Password box and type it again in the Verify box. Click OK to finish MS access database encryption.encrypt-access-database-with-password

DO NOT FORGET THIS PASSWORD!

Remember that this is an optional and additional security feature for our Remote Desktop Hosting services and blocks access to your database even from our own support technicians.  If you forget this encryption password, Access Hosting cannot decrypt or restore your database.  If you have any further questions about this feature, please open a support ticket at http://support.accesshosting.com

Posted in Access 2007, Access 2010, Access 2013, Access 2016, Tips & Tricks | Tagged , , | Leave a comment

Microsoft Access Explained: Reports

Reports are the primary means by which you print information from your cloud database for use by company personnel and clients. You can certainly format and print in datasheet view directly from a specific form or table, but the best way to print and summarize large sets of data from multiple places in your database is through the use of a visually appealing report.

Reports are the best way to create a printed copy of information that is extracted or calculated from data in your database. Reports have two principal advantages over other methods of printing data. First, reports can compare, summarize, subtotal and total large sets of data. And secondly, reports can be created to produce attractive invoices, purchase orders, mailing labels, presentation materials and other output you need to efficiently conduct business.

When designing a report you are able to group data and present each group separately by defining separate headers and footers for each group. You can also perform complex calculations within a group or across several groups. As with other items in Microsoft Access, you can embed pictures of charts in any section of a report. You can also embed subreports, which are particularly useful for showing related details or totals of the records that make up specific rows of your report.

All of these functions can be set up in the design view of a report, but because reports are primarily used for printing hard copies of your databases essential information the primary view used to work with reports is the print preview view.

Remember that for Access 2013 Web Apps, Reports have to exist in the Microsoft Access client so anyone who wants to view them will need an Access Frontend installed on their computer and connected to Sharepoint 2013.  Access 2010 Web Databases offer browser-based reporting features with the powerful hybrid application.  All client based reporing works in our RDP hosting environment.

Being able to print attractive reports is an essential component of Microsoft Access and will benefit your company greatly. This way you can provide hard copies of essential information, which has been drawn safely and accurately from your company’s web database. Check back in with Access Hosting for more information on all the Microsoft Access web database software can do for you and your business.

 

Posted in Access 2003, Access 2010, Access 2013, Access 2016 | Tagged , , , , , | Leave a comment

Microsoft Access Explained: Queries

Queries are used to sort or filter data and display data from more than one table at a time. Although you can certainly build forms and reports that get their data directly from tables, most the time you will want to aggregate data from a number of tables at one time.

There are two basic types of queries within Microsoft Access.

A selection query takes information from the tables and queries in your database. When you define and run a select query Microsoft Access creates a recordset of the selected data. In most cases, you can work with a recordset in the same way that you would with the table or form. You can browse through it, select information from it, print it and even update data within it. Unlike a real item, a recordset doesn’t actually exist in your database. Microsoft Access creates the recordset from the data in the source tables of your query at the time you run the action and provides you with a readout of the desired information from your web or cloud database.

Action queries insert, update or delete data. These queries will be essential when learning how to work within web databases. However, it is important to understand the basic functions of queries and how they operate within simple databases because all the techniques you use for working with a single table apply equally to more complex multiple table queries and all action queries will in fact begin as simple selection queries. Action queries can be used to archive data that is no longer essential to your cloud database, so that, if the data ever becomes relevant again, the record can be easily recalled.

Within design view, you will notice that queries possess a few specific visual signifiers that you may not be familiar with as of yet. The first is the asterisk (*) symbol. At the top of each field list in the upper part of the query window is an asterisk. This symbol is shorthand for selecting all the fields in the table or query with one entry on the field line. You can simply add the astrisk to the design grid to include all the fields from a list, or you can use the check boxes seen in the show row to indicate the fields that will be included in your recordset.

Next is the exclamation point (!) symbol. This symbol can be seen in most of the query commands pictured in the ribbon, and simply serves to remind you that a query must be run before it can create a recordset based on the design stipulations. When you begin to work with queries it is important that you identify the run command located in the ribbon within the query group.

Queries are the best way to focus on the specific data you need for the task at hand. You’ll also find the queries are useful for providing choices for combo and list boxes, which makes entering data in your database much easier.

For more information on running queries and other basic functions of Microsoft Access check back with the Access Hosting blog or contact Access Hosting customer support for immediate assistance with issues concerning your cloud database.

Posted in Access 2003, Access 2007, Access 2010, Access 2013, Access 2016, Beginners Guide | Tagged , | Leave a comment

Microsoft Access Explained: Forms

Forms are the primary means by which Microsoft Access allows users to review the data within your database. Forms serve several functions. Although they can simply be a means to change and input data into your database just like tables, forms are usually bound to an underlying table or query.

The primary function of forms is to present information in a customizable and easily understood manner. You can set options in a form that make all or part of your data read-only, fill in related information from other tables automatically, calculate values to be displayed, or show and hide data depending on the settings selected by a user. Remember that there are forms that will only work within the Access desktop client and then specific web compatible forms (Access 2010 Web Databases) and Web App forms (Access 2013 Web Apps) that offer less functionality but work with Sharepoint Hosting environments.

You can design forms that work with macros to automate the display of certain data or the sequence of certain actions. You can create special controls on your form, called command buttons, which run a macro or a basic visual procedure when a user clicks them. With these commands you can open other forms, run queries or data macros, restrict the data that is displayed, execute commands from the ribbon, display customized ribbons, print records, or perform a host of other actions.

You can also display messages on forms. Microsoft Access provides a MessageBox macro action and basic visual function that you can use to display information, warnings, or error messages. Although you will inevitably design and use reports to print most information about your relational databases, the ability to print with custom messages on forms is sometimes more desirable. Also, you can specify one set of options when Microsoft Access displays a form and another set of options when Access prints a form, so a form can serve a dual role. For example, you might design a form with two sets of headers and footers, one set for entering an order and another set for printing a customer invoice from the order.

To create a form, simply open the desired table, click on the create tab and find the forms group in the center of the ribbon.

At first the form might look a lot like a regular table, but if you enter design view you can see that it provides a many things that are unavailable in table design. Its structure is broken into three parts: header, footer, and detail.

Headers and footers are common features in forms, but the detail section is the most important. This is the part that is repeated for each record in our tables. To change the properties of the fields (which fall under the details bar) right click your chosen field and find the properties command. This will open up a property sheet on the right side of the screen. From here you can see all of the properties you can control with Microsoft Access, for whatever your selection type may be. You can also view the properties of the entire form by entering the properties command of the entire form. Here you can change the text that appears in the header of your form, as well as several of the more advanced properties.

Experiment with forms and you will quickly begin to realize all the benefits this aspect of Microsoft Access can contribute, and don’t forget to check back in with Access Hosting for more helpful hints about all that Microsoft Access can do for your business! Please feel free to experiment with any of our 30-day free trials.

Posted in Access 2003, Access 2007, Access 2013, Access 2016, Beginners Guide | Tagged , | Leave a comment

Microsoft Access Explained: Creating Items and The Wizard

To create a any new item in Microsoft Access, simply open the desired item from the navigation pane, click on the create tab in the ribbon, and find the appropriate group from the selections in black lettering at the bottom. Within each group there are four main ways of creating a new item.

The first is the basic command. This allows you to create a new form, table, query or report, and allows you to enter information for one record at a time. The next is the design command. This command opens the new item in design view and allows you to layout the new item in exactly the way that you want by adding controls and restrictions or tweaking the properties of the new item. Then there is the blank command, which gives you a completely blank version of the new item and allows you to begin working completely from scratch.

Finally, there is the Wizard. It’s a very helpful way of creating new items in Microsoft Access.

The Wizard is a command that allows you to create a new item from a series of dialog boxes. The first dialog will give you a list of all the available fields within the table that you can provide. To select a field simply highlight the name on the left side of the dialog and click the right arrow button to move the selected item over into the selected fields category. After clicking the next command at the bottom right of the dialogue box, you are given a choice of layouts. From there, you can name your new item and you choose to modify its design.

To change the properties of the fields, right click your chosen field and find the properties command. This will open up a property sheet on the right side of the screen. From here you can see all of the properties you can control with Microsoft Access, for whatever your selection type may be. If you were looking at a text box you would be able to see the name and the control source. There are also more common commands such as text align or font color or size.

Finally, you should also be aware of the navigation command within the groups under the create tab. This will allow you to control the front end of your database and what users are allowed to do with in your web database’s parameters.

Check back in with Access Hosting for more valuable information about how to optimize your Microsoft Access experience, get the most out of your company’s information, and better serve your business! Remember that we offer free trials and recommendations on how to get your MS Access database on the web.

 

Posted in Access 2003, Access 2007, Access 2010, Access 2013, Access 2016, Beginners Guide | Tagged , | Leave a comment

Working Offline with Microsoft Access online database

Becoming comfortable with working offline and syncing to an online database with SharePoint later is essential if you often travel or work in areas with intermittent Internet access. Newer versions of Microsoft Access make this transition nearly seamless and help you avoid losing any work. However, there are a few tips that you should know that will allow you to tweak how Microsoft Access works with any website database.

Before you begin using Microsoft Access, you should be aware of two common hazards of working offline. First, if you share a database with coworkers, they may accidentally make conflicting edits when you’re working on a database. When working online, you can recognize these conflicts almost instantly, but you may generate multiple record conflicts when working offline that will create additional work and double-checking. Second, synchronizing with an online database can time out, so you may have to limit the amount of records you edit at one time. The solution to both of these potential issues is to sync offline work with the online database on a regular basis.

Offline Mode with MS Access 2013 and 2010

Since the 2010 edition, Microsoft Access web database automatically places you in offline mode when the computer is not connected to the Internet. Please note that you’re still restricted to online-use only if you’re using the 2013 Microsoft Access web app.  Unfortunately, the offline functionality of Access 2013 Web Apps is non-existent.  If you are disconnected, your frontend reports and forms will stop working/rendering.  The 2013 Web App acts very similarly to connecting your backend data via ODBC or SQL – if that connection isn’t live, you can’t edit or access the data.

The Microsoft Access web database saves any table changes locally using a jet table. The program indicates offline mode with a status bar at the bottom of the screen. Then when you reconnect to the Internet, Microsoft Access will immediately try to reconnect with SharePoint 2010. If Microsoft Access is successful in connecting with the server, you will see a yellow box that asks if you want to send your changes to the server, close open objects and synchronize with the server. If you want to upload your changes to the server, click on the “Synchronize” button.

If you do not see this prompt, go to the “File” menu and choose the “Reconnect All Tables” option.

If you prefer to work offline, bypass online mode in Microsoft Access by clicking on the “External Data” tab. Select the “Web Linked Lists” group and click the “Work Offline” option.

To resume working online, navigate back to the “Web Linked Lists” group. Then click on the “Work Online” option, which will be exactly where the “Work Offline” option appeared before.

To sync database changes, select the “Synchronize” command in the “Web Linked Lists” group on the “External Data” tab.

To discard database changes, select the “Discard changes” command instead.

Working with MS Access 2007 or Earlier

Older versions of Microsoft Access do not have the automatic offline mode. Instead, you should specify when you want to work offline on a SharePoint database, and Microsoft Access will use XML caching to save your work. When working in areas with an intermittent Internet connection, consider working in offline mode and regularly syncing your work to avoid losing any data. Another option is to use one of Access Hosting’s Remote Desktop clients to protect your work and automatically backup your files to any cloud-based storage service when online.

To work offline, open the database on SharePoint and click the “Work Offline” option located on the External Data tab.

To resume working online, go back to the “External Data” tab and select the “Work Online” option.

To update the database with your work and see any changes made by coworkers, select the “Synchronize” option on the “External Data” tab.

If you prefer the greater control of the earlier XML caching, you can turn off the caching in the Microsoft Access 2010 and 2013 versions by navigating to “Access Options,” selecting “Current Database” on the left side and unchecking the box next to the option that says, “Use the cache format that is compatible with Microsoft Access 2010 and later.”

Posted in Access 2007, Access 2010, Access 2013 | Tagged , , | Leave a comment

3 Quick Ways to Verify the Integrity of Your MS Access Database

Many businesses rely on Microsoft Access to make critical decisions about their operations, from customer service production to lead and order management. The integrity of your online database, in terms of quality and security, is critical to the overall benefit that MS Access provides. For instance, if the information entered contains errors or referential data is missing, any queries or reports generated will contain issues.

For this reason, businesses must find ways to monitor the quality and security of their MS Access database. Here are three quick and easy ways to check MS Access database integrity today.

1. Check Places With Common Errors

First, it is worthwhile to review the quality and functionality for areas of the MS Access database that are liable to face issues from human error. By focusing on places and fields most likely to encounter errors, administrators can more easily and regularly check for potential issues that compromise database integrity. One of the features that most commonly faces integrity issues is anywhere referential data is used, since these data segments can allow one error to multiply and cause other issues.

For instance, in an MS Access database that is tracking active customer orders, a small mistake could have been entered in the client’s mailing address. If this information remains uncorrected, future product shipments and promotional mailings will fail to reach this customer. Furthermore, all data points and reports generated from this mistake will be inaccurate. This includes the conversion rates for marketing promotions, the number of customers who call to check on their order, and the demographic and location breakdowns of customer geographies.

2. Utilize Strategies to Secure Your Database

The overall security posture of your database dictates its integrity. Without a thoroughly secured database, the data contained within can be corrupted or accessible by others. There are three strategies that most MS Access users can deploy to better secure the database.

  • First, reset settings using an AutoExec macro. You can accomplish this by naming a new macro AutoExec. This will check and reset the security properties that the last work session may have changed.
  • Second, local databases should remain password protected to prevent unauthorized access. Anytime a user is terminated, a new password should be chosen. Enable your MS Access database password by visiting the security section from the tools menu inside of MS Access. Select the option to “set database password” and enter your new password.
  • Third, consider the security of the database itself. If some of the information needs to be accessible to only certain users, split the database using the Database Splitter feature to limit who has access. Remember that split databases don’t work with Sharepoint but are are compatible with our RDP hosting solution.

3. Verify Quality of Backups

No matter what internal policies and procedures are used to secure a database, something could potentially always happen. This is why it is critical to never assume that backups are completed correctly and that they are accessible. For this reason, one quick way to monitor the integrity of the MS Access database is to check on the quality and existence of backups. This is even true for those using a SharePoint app to host their database and make it accessible to other users. Double coverage is never problematic and may prevent major disruptions from occurring in the production environment.

MS Access databases can be powerful tools for business. They offer a streamlined way to manage and report on the data that businesses rely on. However, just like any other technology or resource, they can be prone to challenges if not properly maintained and tested. Using these three quick tips is a great way to start checking the integrity of your database. Once these tips are part of your regular maintenance routine, feel free to explore additional ways across this site to improve upon your business’s understanding and usage of the great tools that MS Access databases offer.

Posted in Access 2007, Access 2010, Access 2013, Access 2016, Tips & Tricks | Leave a comment

Microsoft Access Explained: The Ribbon

access ribbon

The ribbon is a Quick Access Toolbar shown at the  top of the Microsoft Access main screen. The ribbon contains some of the most widely used commands in Microsoft Access, and is divided into three main sections: tabs, groups and buttons or commands.

You may notice in small black lettering at the base of the ribbon each of the six main tabs are broken down into small groups. Have a look at these group names to assist you in finding the command you are trying to execute. Enabled commands are shown in black or full-color while other commands are greyed out. That is because these are contextual commands that are not always applicable to what you are trying to do. If the command you are looking for is grayed out be sure that you have the appropriate field or object selected.

Similarly, there are contextual tabs that only appear in certain view modes, and contextual menus with their own lists of commands hidden within certain parts of the ribbon. To access the contextual menus simply right click and a drop-down menu will appear next to your mouse cursor.

After you have familiarized yourself with the many functions of the ribbon you may like to take a look at the contextual drop-down menu by right clicking on one of the tabs. From there you can minimize the ribbon to give yourself more space to work in your main window or customize the ribbon to include the commands that you are using for a specific task.

Customizing the ribbon can save you precious time and improve your efficiency when doing repetitive tasks specific to the database you happen to be working on. Simply right click on the ribbon, click the “customize ribbon” command, and select “add new tab” from the menu screen. From there you’ll be given a list of all the commands Microsoft Access has to offer and you can add your most used or favorite commands into your own specialized tab. When you close out of the menu your new tab will appear on the ribbon next to the others.

If you enjoyed this explanation, don’t forget to check back in with Access Hosting for more helpful hints about operating the Microsoft Access software.

Posted in Access 2010, Access 2013, Access 2016, Beginners Guide, Tips & Tricks | Tagged , , , | Leave a comment

Microsoft Access: Why It’s the Best Database Solution for Small Business

Microsoft Access Web Database Northwind traders 2013

Microsoft Access, a database management system (DBMS), has been around a long time — since the early 1990s. It has stood the test of time, as it’s still considered to be the best of the breed. MS Access has become even more important to businesses as a result of the evolution of databases and developers. While it is often shunned by IT professionals and the software has a few disadvantages, Access offers a number of benefits and remains very popular. Learn how to use Access as an inexpensive solution to increase ROI and integrate with other software programs without the need for complex programming.

No Complex Programming Required

MS Access is a top choice for business leaders, data managers and marketers who need an advanced way to collect, manage and report data. It is easy to install and create databases because it doesn’t require programming knowledge. In as little as a few minutes, managers can have a working database set up and ready to go locally or with one of the many cloud database solutions included in MS Access. It’s the perfect solutions for a wide range of users, from individuals to medium-sized companies. Power users have the ability to use it to develop application software and software applications to meet the database needs of larger clients.  Be sure to check out the power of our Access Cloud services with a free trial.

Inexpensive Solutions to Increase ROI

Unlike other database solutions, Microsoft Access is a very inexpensive option. It can save companies hundreds of dollars while offering much of the same capabilities. Smaller companies with limited budgets can take advantage of all that MS Access has to offer, such as implementation of an online database using a remote desktop client (our solution starts at only $29/month). There is no need for costly development, modifications or changes to any databases already in existence. Overall, this helps managers to increase their ROI, whether they use it for general business applications or to market their products and services.

Easy Integration With Other Microsoft Products

One of the most powerful features of Access is that it can be easily integrated with Microsoft Office, SharePoint and even other non-Microsoft programs like Oracle. SharePoint integration helps managers to share data, in addition to collecting and generating reports. When set up as an online database using cloud-based Sharepoint hosting, the opportunities are endless. Multiple users can access the database at the same time online without the need for separate desktop applications for each user. In addition, MS Access makes it a breeze to import data. For example, data can be conveniently collected via email and entered into the database through Microsoft Outlook.

Advantages Outweigh Any Disadvantages

The pros of using Microsoft Access far outweigh the cons, but a few are worth mentioning. File storage tops off at about 2GB for an Access Database, after which managers will run into some limitations or need to move the data to Microsoft SQL. These disadvantages typically won’t present problems for small- to mid-size companies, so there’s no reason not to consider using Access web databases.

Whether big or small, business managers can attain their bottom line with the help of Microsoft Access databases. Online database solutions and software integration can be implemented well within company budgets. While Microsoft Access is not without a few disadvantages, its ease of use and quick setup make the decision to use MS Access a no-brainer.

Posted in Access 2007, Access 2010, Access 2013, Access 2016 | Leave a comment

How to Connect MS Access to InfoPath

With Microsoft Office InfoPath, you can design a form that is connected to a Microsoft Office Access database. By using these two programs together, you get all of the data collection advantages of InfoPath along with the data storage advantages of Access.

InfoPath advantages include forms that can be filled out while users are offline, a rich set of controls that make it easy to design and fill out forms, and data validation rules that are automatically imported when the form is connected to the Access database.

When you connect an InfoPath form to an Access database, you can choose whether you want to set up the database as the form’s main data source or secondary data source. If you want to edit and add records to the database by filling out fields in the InfoPath form, you should set up the database as the form’s main data source.

To use an Access database as the primary data source for an InfoPath form, you must start with a new form. You cannot modify an existing form to add a connection to a primary data source. Use the following procedure to create a new InfoPath form based on the sample database in Access:

infopathdesignform

  1. From the File menu under New, click Design a Form.
  2. In the Data tab of the ribbon, click Data Connections and then push the Add button.
  3. Select receive or submit data and then select Database as the data source (Microsoft SQL Server or Microsoft Office Access only), and then click Next. Remember that you can always use our SQL server hosting as well to keep all your data connected and stored in the cloud rather than in a local MS Access file.
  4. Use the browser to find and Select Database.
  5. In the Select Data Source dialog box, browse to a valid Access database and select it.2-3-2016 9-00-43 AM
  6. Then Select the Table that you would like to collect data via InfoPath2-3-2016 9-02-47 AM

In the screenshot above, I have selected the Authors Table from my Books Database. By selecting the Authors table first, I have setup this table as the primary table for the data connection. I can continue to add other tables via this data connection method to MS Infopath. Additional tables create a one-to-many relationship. In Access, this relationship is defined by using a key field, which is a field that associates the records in one table with the records in another table.

When connecting your form to multiple tables in a database, InfoPath requires that the tables be connected by key fields. Also, the primary table in the data connection must have either a one-to-many or a one-to-one connection with any additional tables that you add.

By default, InfoPath connects to every field in a table. However, you might want to exclude a field, either because it uses a data type that InfoPath cannot connect to or because you do not want to work with a certain field’s values in your form.

Posted in Access 2010, Access 2013, Access 2016 | Tagged | Leave a comment

MS Access Tips: Creating Relationships Between Tables

MS Access Table Relationships

One of the things I like most about MS Access is its ability for data storage, recall, and analysis and its ability to build relationships between multiple data tables. Relationships increase flexibility in working with data. For example, a table that includes names, address, and ZIP codes for customers could be linked to a table that includes accounting information for customers, making it easy for a business to generate and mail invoices.

Microsoft Access lets you build numerous types of relationships. You can link fields from two tables in a one-to-one relationship: one record in field A is always associated with the same record in field B. You can create one-to-many relationships: one record in field A could be associated with numerous records in field B. Finally, you can create many-to-many relationships.

To create one-to-one or one-to-many relationship tables in Access, begin by opening your database and clicking on Database Tools in the top menu. Below, you’ll see the Relationship ribbon. Click on the Relationships button, and select the option Show Tables. In the Show Tables dialogue box, select one of the Access tables you plan to work with and click Add. Repeat that process, adding all of the tables you plan to work with.

When creating a one-to-one or one-to-many relationship, simply click on the field in the first table that will be related to a field in the second table. Holding the left mouse button down, drag the pointer to hover over the related field in the second table. Access automatically creates a relationship, and all you have to do is ensure the fields you are relating have the same data type.

To create a many-to-many relationship, first create a new table in Access. The new table should have an ID field and fields for the two elements you plan to relate from two other tables. After creating the new table, follow the steps for showing tables and adding tables to the design box. Then, follow the steps for relating fields with mouse clicks. This time, click on a field in table one and relate it to the appropriate field in the new table you created. Next, click on a field in table two and relate it to the appropriate field in the new table you created. Again, ensure all related fields have the same data type.

Relationship tables make your database more than a glorified spreadsheet. Creating related tables lets you build powerful Access databases you can use for a variety of business and personal functions.  Best of all you can test all these powerful function using Access Hosting’s specialized MS Access hosting services with a free trial.

Posted in Access 2003, Access 2007, Access 2010, Access 2013, Access 2016 | Leave a comment

5 Unconventional Ways Microsoft Access Can Power Your Business

original (1)

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:

1. Employee Training

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.

2. Customer Relationship Management

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.

3. Displaying Access to Data

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.

4. Business Intelligence

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.

5. Development Waterfalls

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

Posted in Access 2010, Access 2013, Access 2016, Access Developer, blog, SQL Hosting, SQL Server 2012 | Tagged , , , , | Leave a comment

Using Google Chromebooks with our RDP hosting Plans

Chrome RDP

We recently had a question about using Chromebooks to access our Remote Desktop Hosting and if they work.  The answer is that they absolutely do if you grab an RDP App and install it on your Google Chromebook.  It’s actually a great and cheap way to give your employees a laptop where they can still access a file server and the Microsoft Office Suite since all they need is an internet connection.  Here are the very few quick steps to use a Chromebook with your RDP hosting plan:

1. Download the Chrome RDP App from the Chrome webstore: https://chrome.google.com/webstore/detail/chrome-rdp/cbkkbcmdlboombapidmoeolnmdacpkch?hl=en-US

2. Write down or copy the IP address and port from the RDP link that you received when you signed up for an account.  Type/paste that information in the first startup screen:

Chrome RDP

3. Click connect and then enter your login credentials.  Your username prefix should be used as the domain.

logininfo

4. Click OK to connect to the Windows Server and your RDP hosting environment.

Windows on Chromebook

 

 

Posted in Access 2007, Access 2010, Access 2013, Remote Desktop, Remote Desktop Hosting | Tagged , , | Leave a comment

What is the differences between Access Hosting’s SharePoint and Office 365

I was recently having a very good chat with a potential customer that had some very good questions regarding Access and Office 365.  This person already had an Access app and was looking to share it online in the browser and was thinking that SharePoint was the best solution but was confused about what they needed exactly since they had an Office 365 plan with a few users with just email and a few more with Sharepoint and more.  It was such a good conversation that I thought it would be good to clarify some of the differences between Office 365 and our SharePoint 2013 Enterprise hosting plan for Access Web Databases and Web apps.

Cost Savings over Office 365

The most confusing part of the comparison is that the varying plans for Office 365 are quite confusing in and of themselves.  Office 365 is a sort of all encompassing term used to describe a lot of different products from Microsoft.  It can be as simple as a subscription to Microsoft Office software or a complicated enterprise plan with access to Exchange, Sharepoint, Yammer, and more.  If you’re interested in Access Web Databases or 2013 Web Apps, the first thing to determine is whether your Office 365 plan even includes SharePoint Enterprise.  You need to have either an Office 365 Business Premium plan ($12.50-$15 per user per month) or an Office 365 Enterprise Plan ($20 per user per month) to have access to the correct version of SharePoint with Access Services.  Obviously if you have a small business where you have 10 users or less and need email, exchange and everything in between, Office 365 is the better deal, but for a lot of people they need something more flexible.

Let’s say you have Office 365 and are paying $15-$20 per user and have an Access database that you want to host in SharePoint as a web app and share with some of your clients outside of your organization.  How do you do that if they don’t have their own Microsoft account/Office 365 account?  Our $99 SharePoint hosting plan has been tailor made for Access Services 2013 and 2010 and offers substantial cost savings over Office 365.  Right off the bat our $99 plan includes 10 users ($50 – $100 less than Office 365), and each additional user is only $3 per user per month vs. the $15-$20 per user Office 365 plans.  Best of all, you don’t need to pay for all the extra features of Office 365 that you don’t need for you and your clients.

Access Services 2013 and 2010 running Side by Side

Another cool feature about our $99 Sharepoint plan is that you can run 2013 Access Web Apps right alongside Access 2010 Web Databases since our 2013 SharePoint environment is running both versions of Access services.  We’ve already written about the differences of this technology in a previous post: picking between Access 2013 Web Apps and Access 2010 Web Databases.

Comparing Access Hosting to Office 365

Here’s a few more points about our implementation of SharePoint over Microsoft Office 365. An On Premise or 3rd Party Hosted Access Services 2013 implementation helps provide focused, reliable solutions for the following common customer needs:

  1. Web Based Reports: SQL Server Reporting Services can be used to create reports and link them back into the menu structure of the web app. Access 2013 web databases use the browser as the primary interface to the client. This environment provides an excellent cross platform solution that allows users to participate without a copy of Access on their desktop and create/read/update/delete records in the database, but it lacks a mechanism for reporting on that data. With SQL Server Reporting Services (SSRS) you have the ability to deliver browser based reports to your users alongside the Access 2013 web database forms.
  2. Anonymous Access: With this feature enabled Access web databases can be viewed by an anonymous read-only user without requiring authentication. We recently had a customer looking to provide an up to date inventory of their video game and pinball machine inventory to their web site visitors. With Access Services 2013 and anonymous access enabled inside the SharePoint web application, web visitors could view the entire catalog of available games and pricing information without authenticating to SharePoint. This is a powerful feature for any organization that wants to make their product inventory visible to any web visitor, delivering the information quickly and seamlessly without complicating the end user experience.
  3. Full control of SQL Server security: Access 2012 allows for the automatic creation of 2 SQL Server user accounts, one with read-only privileges and another with read-write.  While that is useful, some customers would like to extend this model to multiple user accounts. By creating multiple SQL logins via SQL Server Management Studio (SSMS)  you can create a much more complex security model for applications that want to leverage the Access 2013 web database information stored in SQL Server. Password complexity and aging requirements can also be enforced on these accounts.
  4. Full control of the network firewall: Many Access 2013 applications require extended security to comply with corporate or regulatory requirements. The Health Insurance Portability and Accountability Act (HIPPA) security requirements are a good example of this necessity. In a self-hosted or 3rd party hosted solution you have enough firewall configuration flexibility to lock down your entire database by imposing restrictions on the network perimeter based on TCP/IP address ranges, machine names, and network protocols. A combination of these restrictions can be imposed to address any security mandate.
  5. Render http links in the web browser control of an Access 2013 web database: The browser control is a powerful feature in Access 2013 web forms that allows you to call external resources into your application. Office 365 requires https for all external links which limits the use of browser based resources that do not support https.  This restriction can be lifted in a self-hosted or 3rd party hosted configuration.
  6. Customized backup: You can create a backup rotation scheme in SQL 2012 for the Access Web Apps that mirrors the site collection backup routine in SharePoint. This allows for a synchronized full fidelity backup of the SharePoint site and the Web Apps with multiple restore points. We recently assisted a customer who required a complete snapshot of their SharePoint and Access Services environment to be available for rollback to 24/47/72/96 hour recovery points. In addition, the second and fourth snapshots needed to be available in a secondary data center in the event of a problem with the primary facility. All of this is easily accomplished in a hosted configuration where both the SharePoint and SQL Server environments are completely controlled by the hosting organization.
  7. Ability to change Access Services configuration parameters and SharePoint web application settings:  When running Access Services 2010 alongside Access Services 2013 you maintain complete control of the SharePoint service parameters for Access Services. These parameters can be tuned to enhance the interaction between Access 2010 and SharePoint lists and  eliminate the “List Threshold Exceeded” errors that can appear when using the default configuration settings and manipulating large tables. Full support for web based reporting in published Access 2010 Web Databases and permissive file handling for things like PDF can also be enabled when you have full control over the entire SharePoint Central Administration console.
Posted in Access 2010, Access 2013, Office 365 | Tagged , , , , , | Leave a comment

Picking between Access 2010 Web Databases and Access 2013 Web Apps

A lot of casual Access users don’t realize the immense differences between SharePoint Access 2010 Web Services and Access 2013 Web Apps.   Access 2010 Web Services actually has a lot more features and is grander in scope than Access 2013, but it also has it’s limitations (and reporting never fully worked on Office 365 or BPOS) so it was scrapped and completely redesigned as Web Apps for SharePoint/Access 2013.   Basically Access 2010 Web Databases are dead in favor of Access 2013 Web Apps.  Hopefully this post will help inform you about their differences and help you pick which type of Access Web Database works best for your business.

The Power of Access Services 2010

The awesome thing about SharePoint Access Services 2010 is the way that you can easily build one database entirely in Access and as long as you keep passing the web compatibility checker, you can publish from this one file and have all the power of a hybrid application.  All your web forms, client reports, and client forms are all contained in one file that is hosted on Sharepoint.  It has built in support for working offline from Sharepoint and resycncing when you connect again online and inherently supports multiple concurrent users.  The power of the hybrid access database seems to have been too grand of an idea because it’s complexity is very demanding on SQL reporting services and was never something that worked on Microsoft old Business Productivity Online Standard Suite or the first iteration of Office 365.  Whether it was the processing requirements of the Access 2010 hybrid application, Sharepoint list architecture or just the simple fact that they could never get SQL reporting services to work, Microsoft decided to abandon this Web Database design for Access 2013 in favor of the Access 2013 Web App.  Access Web Databases

Unlike the 2013 web app which requires you to build an entirely new Access Application, Access 2010 Web Databases let you tweak an existing database to be Sharepoint web compatible  so that you can publish them online.  This means that if you have an older database with a bunch of useful client forms and reports, you can still get the entire file backed up and working online and while these client forms won’t work in the web browser, users will still be able to sync changes and use them if they open the database in Access.  You also have the benefit of being able to supplement your Access database with browser based forms and reports as you build them in your 2010 app.  Of course 2010 has it’s downsides as well.  It does not allow the use of VBA code and as we’ve stated earlier, Microsoft has abandoned this idea of a hybrid Access application in favor of the easier to host model in 2013.

Technical differences between Access 2010 Web Databases and Access 2013 Web Apps

The big change between these two technologies is the architecture and where the data is stored.  Both 2010 web databases and 2013 web apps require SharePoint (although different versions)  However, 2010 stores the tables and application in a custom SharePoint list type, while 2013 stores them in SQL Server tables.  This change in architecture means that 2010 web databases are completely incompatible with 2013 web apps.

If you create a 2010 web database there is no upgrade path to Access 2013.  You cannot convert a 2010 web database to a 2013 web app. You can easily migrate your data (table structure and data) from a 2010 web database to a 2013 web app, but the application (forms, reports, etc) will all have to be rebuilt from scratch.  That’s pretty much the problem with Access 2013 web apps to begin with.  You have to pretty much start from scratch building all of your forms/reports in Sharepoint 2013.

Both  require SharePoint Access Services, but 2010 uses Access Services 2010 while 2013 requires Access Services 2013.  These services are very different and incompatible. However, you can have both Access Services 2010 and 2013 running on the same SharePoint site so you can host both access 2010 web databases and 2013 web apps alongside each other.  Lucky for you this is exactly what Access Hosting does with its Access Services 2010 and 2013 offering!

Access 2013 Web Apps

So the bad news is that if you’re interested in building an Access 2013 App, you’re pretty much starting from scratch (other than saving your data and tables).  You will have to build every new form and query for your 2013 Access Web App.  Another setback is that Access 2013 does not allow for any reporting to be conducted in the browser – all reporting requires Access 2013 to be installed on a person’s computer and connected to the SQL table storing your Access Web App via ODBC.  Here’s a helpful post about how to connect your web app to an Access 2013 desktop frontend.


If you’re starting a new Access project though, it makes sense to use the most up to date software and if you are an experienced Access Developer there’s a lot of tricks that you can do to use Access 2013 Web Apps to add browser functionality to an already robust app. By creating a client/desktop Access 2013 frontend and connecting via ODBC you can utilize VBA code and other more advanced functionality and while this functionality isn’t available in the browser you at least have the benefit of all being connected and using the same raw data and tables.

If you’re interested in testing Access 2010 Web Databases or Access 2013 Web Apps, Access Hosting offers a free 30 day trial on servers that are running both services.  Plans include 10 users and are only $99/month.  Additional users and storage can be added to any of these base plans.

 

Posted in Access 2010, Access 2013, Sharepoint 2010, SharePoint 2013 | Tagged , , , | 2 Comments

Finding the right Hosting Solution for you Access Database

How do you know if you need Sharepoint, SQL or Remote Desktop for your MS Access Database? This is one of the most common questions we get here at Access Hosting and it’s not an easy one to answer since every customer’s online functionality goals, Access application and development skills are different.  In this blog post, I’m hoping to at least steer you in the right direction when it comes to getting your Access Database online.

What version of Access are you running? AKA assess your situation

This is one of the first questions that you may be asked by one of our sales associates.  The reason is that it can tell us a lot about your situation and road blocks moving forward.  If you’re running a Database in Access 2007 that has been around even longer, you’re going to have a really hard time jumping to SharePoint.  Does your MS Access application run Visual Basic code (VBA)? If it does you have a pretty complicated application that is completely incompatible with Sharepoint and web forms.

The most common roadblock we see is Access Developers coming in with a complicated and highly customized Access Application.  They’ve heard of SharePoint with Access Services, Web forms, Access 2013 Web Apps and Office 365 and they think they can easily move their application to the SharePoint cloud.  Wrong! Both Access Services 2010 and Access Services 2013 have very strict requirements to make a database web compatible.  If you have an older database or have VBA code, you have a lot of development work ahead and potentially a lot of functionality loss if you want to move to SharePoint.

Cut down on the Development Work!

These difficulties in moving your Access database to SharePoint are why Access Hosting expanded to offer other solutions for hosting web databases.  If you’re looking to host your complex access app in the browser and utilize all of your VBA forms, queries etc, Remote Desktop hosting is a great solution for you since it simulates the local Windows Desktop Environment that you have been using with your application AND allows for multiple concurrent users and the ability to connect to your Access application from any device. The best part is that you can leave your Access Application completely as it is and get it up and running on our private cloud in just a few minutes.  We offer a free 30 day trial for anyone who wants to give it a spin and have very competitive dedicated plans that can be customized to your business needs.

The downside to RDP hosting is that it can be expensive for a lot of users (especially if you don’t move to a dedicated server where there are cost savings) since we are licensing Windows, Microsoft Office and MS Access for each and every user.  If you already have Microsoft Access installed on the computers of people that need access to your Access Application, you may just opt to move all of your data to our SQL hosting.  For $49/month you can upload your database and have an unlimited number of Access users connect to the same backend SQL data.  All of your client forms, reports and VBA code still work locally on the desktop but your data is synced to our service so that everyone is working from the same records.

But I want Web Forms and a Web App!

SharePoint is still a great solution for a lot of Access Apps and if you have a more modern database (Access 2010 or 2013) that is web compatible, it is easy to publish to SharePoint or start creating an Access 2013 web app.  Deciding between Access Services 2010 and 2013 can be difficult though because you need to ask yourself what functionality is paramount to your application.  Check back to our blog later to learn more about the differences between SharePoint 2010 Access Services and SharePoint 2013 Access Web Apps.

 

Posted in Access 2007, Access 2010, Access 2013, Access Developer | Leave a comment

How to split an Access Database

In a follow up to our previous article about the Top 10 Reason to Split an Access Database, this post will give you the quick and easy way to get started with the process.

Splitting an Access database is essential when sharing a database via internal network our cloud hosting. If you have ever received errors when multiple users are editing your Access database, it is likely that you are not operating with what is called a split-database.

When splitting a database, you reorganize it into two separate files. The front-end database contains all the objects such as queries, forms, and reports while the back-end database contains all of the data tables.

Here are the direct instructions from Microsoft for how to split your database:

  1. On your computer, make a copy of the database that you want to split. Start with the database file on your local hard drive, not on the network share. If the database file is currently shared from your local hard disk drive, you can leave it where it is.
  2. Open the copy of the database that is on your local hard disk drive.
  3. On the Database Tools tab, in the Move Data group, click Access Database. The Database Splitter Wizard starts.
  4. Click Split Database.
  5. In the Create Back-end Database dialog box, specify a name, a file type, and a location for the back-end database file.
    NOTES
    • Consider using the name that Access suggests. It preserves the original file name, and indicates that the database is a back-end database by inserting _be into the name, just before the file name extension.
    • Do not change the file type unless some users will use an earlier version of Access to access the data.
    • You can enter the path to the local or network location in the File Name box, in front of the file name. For example, if the network location for the back-end database is \\server1\share1\ and the file name for the back-end database is MyDB_be.accdb, you can enter \\server1\share1\MyDB_be.accdb in the File Name box.
    • The location that you choose must be available to everyone who will use the database. Because drive mappings can vary, you should specify the UNC path of the location instead of using a mapped drive letter.
  6. When the wizard finishes, it displays a confirmation message.

Once you finish with the process, your database will be far more efficient and reliable while also ready for shared usage.  You’ll be able to distribute the front-end and back-end files to either your local network storage or a cloud solution such as Access Hosting’s Access RDP Pro plan to start sharing the database with multiple users.

To get started with a 30-day free trial of Access Hosting’s MS Access RDP Pro plan, click here.

 

References:
https://support.office.com/en-us/article/Split-an-Access-database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc
https://support.microsoft.com/en-us/kb/304932
Posted in Access 2010, Access 2013 | Leave a comment

How to create an MS Access 2013 Web App from within Sharepoint Enteprise

Creating an online MS Access Web App is simple with our Sharepoint 2013 Enterprise Plan. If you’ve just signed up for a free trial or if you’re wondering how to get started creating Access Databases online, you’re at the right tutorial!

The first step is to login to your Access Hosting Sharepoint 2013 Website (typically this looks like yourname.accessontheweb.com).  So type your web address in a web browser (we highly recommend Internet Explorer or Firefox for the best sharepoint compatibility) and login with the credentials that you received from Access Hosting.  Once you login, you should see the standard Sharepoint 2013 homepage.

createapp1

The next step is to create an Access Web App.  Click the gear in the top right and select “Add an app” from the dropdown menu.  This will take you to a listing of all the Sharepoint 2013 apps that you can create.  Access Web Apps are actually on one of the last pages, so it’s easiest to just search for “access”.

accesswebapp2

Click on the Access App button and create a name for you new web database.

accesswebapp3

Click create to start building your web app on Sharepoint.

accesswebapp4

Once your app has been created it should appear in your Lists, Libraries and other Apps on your Sharepoint Intranet Site.  The MS Access web app that you created should be marked as new! Click on your newly created app.

accesswebapp5

You will be prompted to login to your MS Access Web Database.  Enter your login credentials that you received from Access Hosting.

accesswebapp6

There you have it! You have successfully created an MS Access Application on Sharepoint 2013.  The next step is to actually open the App and start building tables and web forms that work in the browser!

Posted in Access 2013, SharePoint 2013 | Tagged , , , , | Leave a comment

How to connect to your Access Web App

If you are using our Sharepoint 2010/2013 Enterprise Solution with Access Services you have the ability to publish your web database to Access Services 2010 or create an Access 2013 Web App in Sharepoint. The problem with SharePoint 2013 Web Apps is that you cannot create and run reports on your data in the browser (like in 2010). The solution is to create a desktop Access frontend that connects to your webapp’s data.  Here is how you connect to your MS Access 2013 Web App with Access Hosting.

How to Report on your Access Web App from MS Access:

1. Go to your Web App in Sharepoint (on accessontheweb.com) and click the “customize in Access” button to download an accdw file.

2. Open and log into your web app by opening the accdw file downloaded from your Access Hosting Sharepoint site.

3. Go to File->Connections (be sure that Read-Only Connection is checked) and click on Manage->View Read-Only Connection information.  Make a note of all the connection data.

readonly connection

4. Be sure to record all of the data shown in this connection information

sql server connection information

5. Under Report on My Data, click the create reports button

connectSP1

6. This will prompt you to create a new access frontend file.  Name the file and save.

7. The connection will fail because the server name is not a valid web location – so you will be prompted for the SQL connection information.  Change ahcombo1 to your Sharepoint site collection url WITHOUT http:// (i.e. example.accessontheweb.com)

frontend

8. A new access file should open that acts as you frontend to your Access 2013 Web App.  You can use this frontend to create reports and other frontend forms and tools!

Sign-up for a Free Sharepoint Access 2013 Web App Trial to try it yourself!

Connecting to your Access Web App with SQL Management Studio:

You can use the power of MS Access Services 2013 and Sharepoint to connect to your backend MS Access Web App directly using SQL Server Management Studio.  Watch the video above or try the more detailed steps outlined below for an ODBC connection.

1. Go to your Web App in Sharepoint (on accessontheweb.com) and click the “customize in Access” button to download an accdw file.

2. Open and log into your web app by opening the accdw file downloaded from your Access Hosting Sharepoint site.

3.  Under Connections, Click Manage and Select Enable Read-Write Permissions. (Note – If you select Enable Read Write Permissions again, you will disable Read-Write Permissions. When you turn on Read Write Permissions again in the future, the password will change.)

access view read-write connection

4.  Once Read-Write Permissions are Enabled, click the Manage button again and select View Read-Write Connection Information and the below dialog box will pop up. You will need to reference the permissions below to setup the ODBC connection.  Note the connection information and copy over to notepad.

password info

5. If you don’t already have SQL Native Client 11.0 Driver, you’ll need to download and install them.  Install and SQL Native Client 11.0 driver from Microsoft SQL Server 2012 SP1 Feature Pack. Don’t download the entire package, you only need on file which is the sqlncli.msi. You can download the file here: http://www.microsoft.com/en-us/download/details.aspx?id=35580.

6. Once downloaded, double click the file to install the SQL Native Client 11.0 driver.

7. To create the ODBC connection, launch ODBC Data Source Administrator by clicking your Windows start button and search for ODBC.  Select the Data Sources (ODBC) file that comes up.

add a new odbc data source

8. Click on Add to create a new data source.  Select SQL Server Native Client 11.0 and then click finish.

create a new sql data source

9. Enter Any Name and Description to describe your ODBC connection. For the Server enter the domain of your Access Hosting SharePoint site but DO NOT enter the http:// (so enter something like example.accessontheweb.com). Click Next.

ms web database information

10. Reference the View Read-Write Connection data that you pasted into Notepad from your Access Web App earlier.  Enter the User Name information in the Login ID and the Password in the Password field and click next.

sql server dsn configuration

11. Check the box Change the default database to and enter the Database Name from the View Read-Write Connection Information from your Web App and click next. Click Finish.

sql server 2012 configuration

Sign-up for a Free Sharepoint Access 2013 Web App Trial to try it yourself!

Posted in Access 2013, Access Developer, SharePoint 2013, Tips & Tricks, Video Tutorial | Leave a comment