Category Archives: Access 2007

How to Connect Microsoft Access to QuickBooks

QuickBooks is an accounting application that many individuals and small business owners use to manage their finances. It allows you to maintain a variety of database files such as customer contact information, budgets and inventory records.

Microsoft Access, on the other hand, is a broader database application, which can store everything from mailing lists to financial data and everything in between. While many users enjoy the convenient financial tracking that QuickBooks provides, they also like the flexibility and advanced query options that Microsoft Access offers.

The good news is that you can merge these two different applications. This allows you to record your financial and business records efficiently, while still being able to create a variety of queries for analytical purposes.

In order to connect Microsoft Access to QuickBooks, you will need to use an ODBC driver like QODBC. While this driver does not import files from or into these applications, it does allow Microsoft Access users to view their QuickBook files externally.

You will still be able to be able to create all the tables, queries, reports, modules and macros using data stored in your QuickBook files, just as you would your files stored in Microsoft Access. Data within these files is updated instantly whether you are working in QuickBook or Microsoft Access.

The process for connecting Microsoft Access and QuickBooks is fairly easy and can be completed within just a few minutes. Below is a look at the seven steps you need to take to connect these two applications.

2016-09-20_9-14-19

  1. Start by opening up your Microsoft Access application. You can do this by clicking the Start button on your desktop, selecting Microsoft Office and choosing Microsoft Access. Select the Blank Access Database Click the OK button.
  2. A default name will appear in the File Name Feel free to edit the name of the file to better meet your specific needs. This name cannot match any other file name within your Microsoft Access database. When you are done, click the Create button.
  3. Click the New tab at the top of the box.
  4. The Microsoft Access database wizard box will appear. Select Link Table. Click the OK
  5. Locate the Files of Type box at the bottom of the screen. Use the drop down box to change the Files of Type from Microsoft Access to ODBC Databases.
  6. The Select Data Source box will appear. Select QuickBook Data or another DSN Data Source you set up with your QODBC driver. Click on the OK
  7. The Link Table will appear, which lists all the tables from QuickBook that you can now access with Microsoft Access. Select all the QuickBook tables you want to connect to Microsoft Office. Click the OK

You will now be able to view all your QuickBook tables in Microsoft Access. It is important to note that this process technically does not import your QuickBook files into Microsoft Access. Instead, Microsoft Access will open these external files through the QODBC driver.

This allows changes to be updated instantly whether you are working in QuickBooks or in Microsoft Access. This creates fewer errors and ensures your records stay up to date at all times.

If you create a table in QuickBooks, you will need to repeat steps one through seven to make sure these new files are connected to your Microsoft Access.

Posted in Access 2007, Access 2010, Access 2013, Access 2016, quickbooks hosting, Remote Desktop, Remote Desktop Hosting | 1 Comment

Download the Access 2016 Runtime

The Microsoft Access 2016 runtime has been released.

The wait is over for Access 2016 Runtime. Here are the download links for the last 4 versions of the MS Access Runtime environment. Remember that you can use these runtime packages to deploy Access Applications free of charge. They make a great low cost solution for putting legacy Access applications on the internet, the iPad, and most Android devices WITHOUT the hassle and expense of conversion using our Remote Desktop Hosting and SQL 2012 Hosting Plans.

Access 2016 Runtime Download

Access 2013 Runtime Download

Access 2010 Runtime Download

Access 2007 Runtime Download

Remote Desktop Hosting Plan Free Trial

SQL 2012 Hosting Plan Free Trial

 

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

Learn about the Differences Between MS Access and QuickBooks

When your business is growing, you need to keep regular track of your finances. Microsoft Access and QuickBooks are two programs that can help you achieve this, but which system will work best for you? You want to make the right choice to fulfill your business needs while saving time. Discover the key differences between these two data management programs to help you choose.

Customization Capabilities

Both Microsoft Access and QuickBooks are useful for collecting and managing data, especially for accounting. Both programs are also available in different formats to accommodate business size, but they differ in customization capabilities.

Small businesses often find QuickBooks to be a simple tool to manage their financial transactions. Alternatively, Microsoft Access is a more robust database management system that allows its users to build and develop applications unique to their business needs. It allows users to customize databases and templates and automate apps without the need of a developer.

There are even times when a MS Access database solution needs to integrate with Quickbooks and pull in financial data.

Ramp-Up Time

The time it takes to learn a system can affect a company’s productivity. If your business is implementing tools that require extensive research to use, then you have to assess whether it’s worth using that tool. QuickBooks is a popular accounting tool many small businesses use because it’s a simple accounting software tool to get started with when you have only a few business transactions to track.

On the other hand, Microsoft Access can be complex for some businesses starting out because of the knowledge required to use some of the functions and the time it takes to acquire this knowledge. For example, if a user wants to automatically sync his apps to the cloud, he must understand SQL compatibility formats. If he doesn’t understand how to convert his applications to a format that is compatible with SQL, then he’ll need to learn SQL or outsource the job. Luckily for you, Access Hosting has this expertise and can make this process easy for you.

Limitations on Users

Both Microsoft Access and QuickBooks have a multi-user support team, but the programs differ in how many people can use the system concurrently. For example, QuickBooks is limited to 30 users, as of 2016, while Microsoft Access can handle more than 200 users at a time on an enterprise level or with an Access Hosting dedicated machine.

Cloud Capabilities

Both Microsoft Access and QuickBooks offer cloud-based versions of their software programs, but these cloud capabilities are limited to the size of the business. For example, QuickBooks does not offer cloud-supported software for its enterprise level edition.  Access Hosting is here to help you every step of the way as you move your data into the cloud and has a variety of solutions built around MS Access and Quickbooks. Private Cloud  hosting is becoming a necessity for many businesses as the need to store data online keeps growing.

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

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: 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 , | 2 Comments

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

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

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

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

New Robust Backup Options for Remote Desktop Hosting

941_amazon

Access Hosting is happy to announce a more robust and granular backup option for our Remote Desktop Hosting customers.  We have always done daily backups on the entire server which helps us recover from any failure but does not help customers looking to rollback a specific Access or Excel file to a previous version.  We have spent the last month integrating our server architecture with Amazon Web Services and are happy to report that we are now able to backup individual ms Access, Office, and other files on a daily or even hourly basis based on your desired needs.

Any current Remote Desktop customers can upgrade their plan with these new backup options.  You ONLY need 1 backup plan – it will cover all of your users and files.  You DO NOT need a backup plan for each RDP user account.  These backup plans can also be added and applied to a dedicated Remote Desktop Server.

Daily Backup – $10 per month

With this backup option, we will archive, store and backup all of your files offsite on our Amazon AWS servers on a 24 hour cycle.  If at any time you make a mistake or a MS Access database or other file gets corrupted, we can roll back a specific file to the previous day’s version.

Order Daily Backup Now!

Hourly Backup – $20 per month

With this backup option, we will archive, store and backup all of your files offsite on our Amazon AWS servers on an hourly basis.  If at any time you make a mistake, we can roll back and restore a specific file to a previous version that was saved every hour.

Order Hourly Backup Today!

If you have any questions or specific backup needs, please contact our technical support team.

Posted in Access 2007, Access 2010, Access 2013, Amazon Web Services, Excel 2013, Remote Desktop, Remote Desktop Hosting | Tagged , , , , , | Leave a comment

The Top 10 Reasons to Split an Access database

Splitting your Access database offers numerous advantages, including increased flexibility, security, efficiency, and scalability.

Access is an amazing and powerful desktop application that lets you store data and interface with that data in the same file. You can even design your own applications with Access.  Unfortunately, the only way to utilize the full power of MS Access  is to develop traditional local desktop based applications.  Luckily, you can take your powerful MS Access applications with split frontend and backend to the cloud with Access Hosting’s Remote Desktop Hosting.

Most developers agree that a split database is easier to protect and maintain but unfortunately this powerful approach is completely incompatible with SharePoint.  When you split an Access database file, you end up with two files instead of just one (often the backend is an .mdb file):

  • The backend stores all of the data in relational table (you can always move the backend to SQL for even more power and accessibility)
  • The front end stores all of your interface objects: forms, reports, queries etc.
  • By linking the two files, users can view and manipulate the data in the backend via the forms and reports in the front end. This arrangement solves a number of problems inherent to the Access file structure.

Now onto the top 10 reasons to split a Microsoft Access Database:

1: Multiple users share the data
Perhaps the biggest incentive for splitting a database is to supply data to multiple users over a local network or on the cloud via Access Hosting. By storing the backend on a local file server, SQL or other cloud based solution and distributing the front end to workstations and users, many users can access and manipulate the data at the same time without running into record locking and other problems.

2: Everyone’s using the same data at the same time
By splitting a database, you know that all users are accessing the most current data because everyone’s accessing the SAME data. Not only are they all accessing the same data, they can all update it at the same time. That means a change made by one user is almost immediately available to all other users.

Having a backend moves all the data into a single database file (.mdb) or backend ODBC connection (SQL, mySQL etc). That means there’s only one copy of that data to manage and protect. Changes are immediate and available to all authorized users. Any administrative and development duties are implemented in the backend file, once.

3: Your data is better protected
Whole books have been written on database security, but it’s enough for you to know that you must protect your data. One of the easiest ways is to split your database. Placing your tables in a backend file protects your database design because users can’t directly access the tables via the interface objects in the front end.  Therefore, they can’t alter or delete tables, even accidentally. Most of the users working in the front end won’t realize they’re actually working with two separate files, so splitting the database will have NO Negative impact on your users.

However, this arrangement is not a comprehensive security lock on design. Users who know what they’re doing can still open the backend, if they have access to it. Just bear in mind that splitting the database will minimize accidents — but it won’t stop someone who’s determined to get at your tables.

If security is a of utmost importance, Access Hosting’s Remote Desktop hosting adds an additional layer of security – requiring users to login to the MS Access frontend.  Users don’t even need to have Access installed on their computer!  We even offer HIPAA compliant dedicated server solutions for healthcare and medical data.  Moving your data to a SQL backend also adds additional security and features to an Access application.

4: You can easily scale your application for the future
If there’s any chance that your Access database will grow out of its skin, consider splitting the database. It’s easier to upsize a split database to SQL Server (or some other larger relational database system) because you can easily link the existing front end to SQL Server tables. That way, the organization has the advantage of storing data in a larger database with most of the perks that come with doing so, while still using the interface and all of the reports and queries that you originally designed in the Access front end.

5:  The user interface is easy to modify
Most databases grow and change with the business; they require new features or modified business rules. Changes to existing tables are rare, if you properly normalized them early on. Most changes will be in the front end in the form of new or modified forms and reports.

As long as your database is split, testing and implementing changes to the front end can occur with little or no disruption to users. You simply link the development front end to the production backend and test away. This won’t always be the case, of course, but testing new interface objects is easier in a split database configuration.

6: Deploying a new front end is a snap
If the user interface and data are stored in the same database, you must REPLACE the entire database EVERY time changes are made in a local environment.  With our RDP solution, you can keep your database and app contained all in one file and more easily replace them on our server in 1 location, but it’s still not ideal.  That’s a lot of unnecessary work and is especially problematic if no one in-house has the expertise to do it for you or if you have to visit each user’s workstation.

In a split database configuration, you simply replace the front-end ms access file and relink the tables. It takes a few minutes and requires little interruption of users.  With our RDP solution, you can even login and do this for every user to completely eliminate any application support issues.

7: It makes life easier for offsite developers
A split database is easier for offsite developers to maintain and upgrade. The developer works offsite to implement changes and enhancements to the front end and then ships the new version to someone in-house (or uploads it directly) who has the technical expertise to deploy it. This latter process is a simple copy and relinking task that doesn’t require high-end expertise. You can train someone to do it or even talk someone through it over the phone. Many developers write a routine that automates the process. All the in-house technician has to do is double-click the installation file. This opens up a lot of long distance opportunities that a developer just couldn’t manage as easily with a single database file.  Access Developers out there should interested in making their lives easier and moving their clients to a cloud based hosting solution should contact us about our Partner and Referral program.

8: Geography’s not a problem
A split database allows users in different locations to access the same data. For example, the backend could be stored on our servers in our SAS70 data center in Philadelphia, but users from all over the country can access the data via their local systems connected to a SQL backend.

9: Corruption is limited
Access databases are prone to corruption. One of the easiest ways to avoid this problem is to implement a split database, which is less prone to corruption.  Nothing is worse than having a user corrupt your backend data!

10: It’s easier to get individual users back on track
Security in the front end is one way to limit user interference. However, some users require more flexibility than others and there are always trade-offs. Some applications will require tight front-end security, while others will allow more freedom to tinker.

When a user tinkers to the point of destruction, a split database is easier to repair. Rather than bringing the entire application and all its users to a screeching halt, you have only one user who’s unable to work, momentarily because they broke their specific Access frontend and not the entire application. The fix is usually as simple as recopying the front end and overwriting the changes that the person made.

I hope this was helpful to people who have an Access application at their business.  If anyone is looking to learn more about our Remote Desktop and or SQL backend hosting options, please do not hesitate to contact us or sign up for a free trial to try it for yourself.

Posted in Access 2003, Access 2007, Access 2010, Access 2013, Remote Desktop, Remote Desktop Hosting, SQL Hosting, Tips & Tricks | Tagged , , | 1 Comment

Remote Desktop Storage Upgrade for 2015

Access Hosting is proud to announce that for 2015 we have increased the storage quotas on all of our Remote Desktop Hosting plans for customers seeking an easy way to move their Access Hosting Database to the web without any modifications.

Access Hosting, the leading hosting company for Microsoft Access services and solutions has doubled and tripled the storage on their Access Remote Desktop plans. Customers in need of bringing their Microsoft Access web application to the cloud can now do so without the need for expensive hardware or costly setup.

Interested customers will now enjoy 2GB of storage with the $29/month Access Pro Virtual Desktop and 3GB of storage with the $49/month Office Pro Plus Remote Desktop plan. Anyone interested in trying out this hosting service can sign up for a free 30 day trial at http://accesshosting.com/remote-desktop-hosting/free-trial

Remote Desktop Services allows you to keep your Access 2007, 2010, or 2013 Database running “as-is” online in the cloud. Using the latest advances in Microsoft Hypervisor technology we can create a seamless single click operating environment for your existing Access database that allows you to run your application without modification. This approach allows multiple users to connect concurrently to the cloud from any PC and experience the program as if it was running locally on their desktop. Pricing starts at just $19/month and we offer a free trial for qualified customers.  You can learn more at http://accesshosting.com/remote-desktop-hosting/

Posted in Access 2007, Access 2010, Access 2013, Access and the iPad, Access on the Kindle, Remote Desktop, Remote Desktop Hosting | Tagged , , , , , | Leave a comment

How to send email with Access without using Outlook

Building an Access database provides many benefits because it not only gives users an easy way to manage their data, but Access can also be used to manipulate external applications through code. One of those extremely helpful benefits is its ability to send out an email message. Access is not an email client, so it doesn’t really send out the email message itself. Instead, it can be used to automate external email client programs, such as Outlook, to actually send out the message on its behalf. But what if Outlook is not installed on the user’s computer? Normally, that is not a problem as long as the user has an email client installed on the machine. The SendObject method or the EmailDatabaseObject macro action will try to use whatever email client is installed as the default to send out the message. This article will address those situations where the user does not have any email client installed or if bypassing the security warning in Outlook is desired. (more…)

Posted in Access 2007, Access 2010, Access 2013, Tips & Tricks | Tagged , | 2 Comments

How to collect data in MS Access via email with Outlook

Beginning with version 2007, Microsoft Access included a feature that seamlessly links your database table to Outlook and makes it easier to collect user data via email messages. This quick tutorial will show you the steps for setting up the email template for collecting the data as well as the steps for processing the collected data.

Creating the email message

Follow these steps to create the email message:

  1. Select the table that you need to collect the data for from the Navigation Pane.
  2. From the External Data tab on the Ribbon, in the Collect Data group, click on the Create E-mail button.
  3. The Collect data through e-mail messages Wizard should open as show in Figure 1.
    collectdata1

    Figure 1

  4. Click Next. The next screen will present you with the option to choose the type of email form you want to use. You can only use either HTML or InfoPath forms (see Figure 2).
    collectdata2

    Figure 2

    Select HTML if your users do not have InfoPath installed on their machines.

  5. Click Next after you have made your choice.
  6. If your table contains existing data, the next screen that shows up is where you’ll choose whether the data you’re collecting are new information or for updating the existing data in your table (see Figure 3).
    Figure 3

    Figure 3

  7. Click Next after you have made your selection.
  8. The next screen (see Figure 4) allows you to select which fields from your table you would like to include in the email message for collecting the data. You can set the order of the fields or change the label captions for each field.
    Figure 4

    Figure 4

  9. After you have selected all the fields you want to collect, click Next to see the next screen (see Figure 5).
    Figure 5

    Figure 5

  10. This screen allows you to specify which Outlook folder will be used to store the email replies for the collected data. You can change the default location by clicking on the folder’s name and then switch to the Outlook window and select or create a new folder (see Figure 6).
    Figure 6

    Figure 6

  11. From the same screen (see Figure 5), you can also specify if you want all email replies to be processed automatically, which means that the data collected will be automatically added to your Access table. To control how the data is processed, you can click on the link labeled “Set properties to control the automatic processing of replies.” This would open up the Options screen (see Figure 7).
    Figure 7

    Figure 7

  12. From the screen shown in Figure 5, click Next to move on the next step.
  13. The next screen specifies how you would like to provide the email addresses for the recipients of your message (see Figure 8).
    Figure 8

    Figure 8

  14. If you select to provide the email addresses from an Access table, clicking Next will present the screen shown in Figure 9.
    Figure 9

    Figure 9

  15. Select the email address field from the current or related table and then click Next.
  16. The next screen allows you to specify the subject line and body of the message (see Figure 10).
    Figure 10

    Figure 10

  17. Click Next to see the next screen (see Figure 11). This screen is informational only.
    Figure 11

    Figure 11

  18. Click Next again. The next screen allows you to select specific recipients for the email message, send the message to all recipients (see Figure 12).
    Figure 12

    Figure 12

  19. When you’re ready to send the email message, click Send.
  20. When your users get the email message, all they have to do is reply back by filling out the form in the message with the information being requested.

Managing Automatic Reply Processing

After you have set up your data collection email, you can go to the Message Options screen to change its settings. To do so, follow these steps:

  1. From the External Data tab on the Ribbon, in the Collect Data group, click on the Manage Replies button. The Manage Data Collection Messages dialog window should come up (see Figure 13).
    Figure 13

    Figure 13

  2. With the data collection message template you want to modify highlighted, click on the Message Options button.
  3. The Collecting Data Using E-Mail Options dialog window should open up (see Figure 14).
    Figure 14

    Figure 14

  4. From this screen, you can check or uncheck the options you prefer.
  5. Click OK to close the Options window.
  6. Click Close on the Manage Data Collection Messages dialog window.

Processing Replies

If you did not set up your email data collection to be processed automatically by Outlook, or if a reply failed to be processed, you can manually process each reply by following these steps:

  1. Locate the folder you designated for storing the collected data in Outlook.
  2. Right-click on the user’s reply you want to process and select Export data to Microsoft Access.
  3. On the Export data to Microsoft Access dialog window, verify the data to be added to the table and click OK.
  4. If the export is successful, you should get the confirmation window shown in Figure 15.
    Figure 15

    Figure 15

  5. You must repeat these steps for each reply you want to process.

Conclusion

Since Access 2007, database developers can now automate external data collection via email messages using Outlook. If you have users who need to send out surveys or questionnaires and want to avoid the manual process of collecting feedback, you might consider giving this new feature a try.  More importantly this type of integration is actually possible via a web browser and in the cloud with Access hosting’s remote desktop hosting. All you need to do is sign up for an Office Pro plan with MS Access and Outlook to try it free for 30 days.

Posted in Access 2007, Access 2010, Access 2013 | Tagged , , | 11 Comments

Running MS Access Applications on an Android Kindle Fire

Running your Access application on the Kindle Fire can be accomplished in just a few simple steps.

img_52b899958a37e

Here’s what to do:

1. Download the Microsoft Remote Desktop APK and move it over to the Kindle Fire via USB or emailing it to yourself. Note that this is a zip file and you will need to unzip it before moving it over.

2. Change your default Android Kindle settings to allow This is known as “sideloading” your application into Android. You may need to setup the Kindle to accept unknown applicaitons first by doing this:

Fire Settings

Click your settings tab

Fire Applications

Navigate to Applications

Fire Settings 2

and allow applications to be installed from unknown sources (set to On)

3. Install the Microsoft Remote Desktop application to your Kindle.  You can do this by navigating to your Downloads folder (if you emailed it to yourself) or the folder that you placed it in via your USB connection.  We’ve found the free ES File Explorer app to be incredible helpful way to browse your Kindle’s Harddrive.

img_52b888ad593f2

3. Pin the newly installed Microsoft Remote Desktop application to your home screen

new RDP

4. Configure the Remote Desktop Connection. If you are using our Remote Desktop Hosting Service  this information will be provided via email.

rdp config

5. Connect the Remote Desktop and run your Access application. Our Remote Desktop Hosting Service is available for Access 2007/2010/2013 and the Access 2010/2013 Runtime

This new version of the Remote Desktop client supports all of the Kindle gestures and features easy to navigate keyboard and mouse touch screen equivalents. Your users will find that the Access .mdb and .accdb applications they know and love work just fine on the Android Kindle Fire Tablet.

 

 

Posted in Access 2003, Access 2007, Access 2010, Access 2013, Access on the Kindle, Remote Desktop, Remote Desktop Hosting, Tips & Tricks, Video Tutorial | Tagged , , , , , , | Leave a comment