top
logo

Polls

Using Microsoft Access
 

Microsoft Most Valued Professional MVP - Access Expert

mvp.png

 

  Boyd Trimmell, aka HiTech Coach
  Microsoft MVP - Access Expert


What is a Microsoft  Most Valuable Professional (MVP)?
Microsoft  awards the Most Valuable Professional (MVP) in recognition of significant contributions made to technical communities by sharing high quality, real world expertise. Microsoft Most Valuable Professionals (MVPs) are a worldwide network of exceptional technical community leaders.

"If technology doesn't work for people, then it doesn't work."
- Kim Vicente

 
Why are there not many articles or examples for Access 2007, 2010, and 2013?
User Rating: / 111
PoorBest 
Written by Boyd Trimmell aka HiTech Coach   

Access VBA has not really changed that much in many years for desktop applications.. If something worked in 2000 or 2002/2003 then it will probably work in the newer versions.  There were a few new features introduced on Access 2007 that require the example to be in 2007 (.accdb) format. New in 2007 was the Ribbon, Rich Text, and Save as PDF. You will find that a lot of articles examples are done with the oldest version possible that it will support it.  This allows a single article or example can be created that is compatible with many Access versions and reaching the larges possible audience. It is common for Authors to only update their articles and/or examples if it requires a change for the newer version of Access.

 

That is why you see a lot more of examples that are in Access 2000, 2002/2003 and 2007.   There are a lot fewer in  2010 and even less in 2013.   When searching for examples keep in mind that all those Access 2000/2002/2003 examples will usually apply to Access 2007 and newer versions.

 

TIP: It is generally safe to assume that if an Access verion is specified in an artilce or example that is means that version and later.

 

 


 
Where is the DAO Library in Access 2007 and 2010?
User Rating: / 27
PoorBest 
Written by Boyd Trimmell aka HiTech Coach   
Thursday, 30 June 2011 04:50

 

In Access 2000 to 2003 to use DAO you needed to set a reference to the DAO Library

 

access2003dao

(Credit: Screenshot created with Techsmith's Snagit)

 

Starting with Access 2007 (12.0) and continuing in Access 2010 (14.0) there is a new Access Database Engine (ACE) that replaces the older JET database engine. DAO is now built into ACE. By default Access sets a reference to this new object library called Microsoft Office 12.0 Access database engine Object Library.

 

msofficeaceobject2007b

(Credit: Screenshot created with Techsmith's Snagit)

 

You should not set a reference to the older DAO 3.x object libraries in Access 2007 or Access 2010. You should use the new DAO library built into the Microsoft Office 12.0/14.0 Access Database Engine Object Library.

 

 
What is the Access Runtime Version?
User Rating: / 416
PoorBest 
Written by Boyd Trimmell aka HiTech Coach   

I have been using the Access runtime since 1997 when I purchased the Access Developer edition.  The Developer edition included a license to distribute the Access runtime. Starting with Access 2007 Microsoft has made the developer extensions and a license to distribute the runtime free.

 

The Access Runtime is like a database viewer. It is similar to the Adobe Acrobat Reader. Acrobat Reader will open a PDF. It will not allow you to create a PDF. Similar you can use the Access Runtime to “view” a database. You can use the Forms and Reports that have already been created. It will run existing queries, macros and VBA code. You can enter data. You can’t get to design mode for any objects. To create an Access database and design object you will need the full version of Access.


It is common in software development platforms to have two versions:

1)  The Developer Edition  - used for creating applications. Normally this must be purchased.

2) The Runtime Engine - used to run the application. This is not always free. 


With Access the Full/Retail version is your developer edition. The Access runtime version is a runtime engine. The runtime version is definitely [b]NOT[/b] the same as the full  Retail version. It does have some limitations. Since it is free you do not get the paid feature of Office like the spell checker, Office Clipboard, etc. 

Who should use the Access Runtime version?

The Access runtime doesn’t include the all built in features of Access’s UI available on the Ribbons or Menu. Some of the advanced features like Filter-by-Form and spell check are not available. That is reasonably since you have not paid for them. The Access Runtime is great for databases where a developer has created the entire feature set they want the user to have. This is done without relying on the built in features available through the Access UI.

 

The way I have always viewed using the runtime is that it is FREE Lite version.   It is a database viewer for people that do not need all the full power of the retail version of Access. It is great for data entry users. It is not good for the power user that needs a lot of the advanced feature, like Filter by form. In my opinion this is fair since you are not paying for the more advanced features. If you need all of the advanced  features in the  retail (paid) version then it is probably best to purchase the full retail version. If you do want the same features included in the paid version in the  free lite runtime version then you will need to have a developer (normally means additional  cost) create the required features.  If you need all the features of the full/paid version of Access then the Access runtime may not be for you. Especially if this will be a small deployment.  It may be cheaper to just purchase full licenses of Access.  For large deployments it can have a cost savings to use the free/runtime version.  The extra development costs are offset by the the reduced licensing fees. In the long run it can have a significant savings with large deployments.

 

How to obtain the Access Runtime version

Starting with Access 2007 the Access Runtime version and developer extensions are free. With prior versions you have to purchase the Developer Extensions which included to the Access Runtime setup and a license to distribute the Access Runtime version.

 

See:

Microsoft Access 2007 Download: Access Runtime

Microsoft Access (Office) Developer Edition FAQ

 

Where to install the Runtime version?

The Access Runtime version should only be installed on a machine that does not already have the full version of Access installed. I would not recommend installing the Access Runtime version of the same machine that has the Full version of Access installed. I like to use Virtual PC for testing the installation of the Access Runtime version.

I do whatever I can to insure that only one version of Access is installed on a User's machine. If there is already a compatible version of Access installed then I recommend using it. I would not install another version of Access if at all possible.

 

I just installed the Access Runtme version. Where is it?

You will not see the Access Runtime on your Start Menu with the other Microsoft Office applications.  The Access runtime is not used in the same way you would use the full version of Access. The Access Runtime can only be used with existing databases. It is possible to use Windows Explorer to find the database file and double click on it to open it in the Access Runtime. I find it better to create a shortcut to launch the database with the Access runtime.  You can place the shortcut on the desktop and/or the Start Menu.

 

Forcing the full version of Access into Runtime Mode

You can simulate the Access Runtime environment with the Full version of Access.  With Access 2000-2010 you can create a shortcut that start Access and loads a database using the /runtime command line switch.

 

Example Shortcut for Access 2003:

"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE"  "C:\my2003DB.mdb" /runtime

 

With Access 2007 and 2010 it is even easier:. You can simply rename your database from the .accdb or .accde to a .accdr.  Now double click on the .accdr file and Access will load in Runtime mode.

 

TIP:  You can also rename a .mdb or ,.mde to a .accdr to get Access 2007 or 2010 to open it in runtime mode.

 

It is important to note that this only simulates the runtime version.  It is close but not a 100% match to the Access Runtime Installation. There are features that will work in with the full version of Access in runtime mode that do not work the same with in the actual Access Runtime version installation. From the best I have been able to figure out is that there are things installed with the full version that will still work when forced into Runtime mode. These same features (files/reg keys, etc.) are not installed with the Access Runtime version installation package. So these features don’t work in the Runtime installation like they did with the full version in runtime mode.

 

The only way to truly test the Access Runtime environment is to install and run the Access Runtime on a clean machine. I like to use a VM. I have only used Virtual PC. The roll back feature is great for testing an installer package.

 

Errors and the Advantages of using an MDE/ACCDE with the Runtime

With an MDB in the Access runtime errors can cause Access to shut down or appear to crash. If it does not shutdown then it will probably reset all memory variables to 0 or Nothing/Null. One of the advantages to using an MDE with the Access Runtime is that errors that are not trapped don't cause variables to get reset or the Access Runtime to shut down. With errors that are not trapped with an MDE in the Runtime mode you application keeps running but it is very difficult to figure out what cause the error. It is important to have error handling to trap errors.

 

When deploying your application with the Access Runtime, or full Access in Runtime mode, I recommend converting your Applications (Front ends) into an MDE/ACCDE. It will make your applications more stable if they encounter errors that are not trapped.   Error Handling is still important and should not be neglected.

 

How to set up a machine with the Access runtime

You can install the Access Runtime from the free download at Microsoft (You can find links here)  . Copy your front end database (back end also if needed)  to the machine.  Open the database.  No different that if you were to install the full version of Access and set up your database. You do not have to create an installer package unless you want to bundle it into a neat little installer package.

 

Packaging your Front end with the Acces Runtime

Using the Acess developer extensions you can create an installer package that will isntall your Access database and the Acess runtime.

Additional Resources:

Access 2007 runtime deployment - FAQs

Microsoft Access (Office) Developer Edition FAQ

Trust Center in Access 2007 and 2010

Basics for Building Access 2007 Runtime-Based Solutions

Obtain and deploy the Access 2003 runtime

 

 
How to Manage Your Linked Tables
User Rating: / 95
PoorBest 
Written by Boyd Trimmell aka HiTech Coach   

Once you have learn how important it is to Split your Access database into application and data you will need become very good at managing your linked tables.  This is important if you want to be able to switch to a different back end. I am regularly switching between live data (back end) and test data (back end). You will also need to manage your linked tables if you move your current back end to a new location. When you move your back end or want to switch to a different back end you must tell Access when to find the back end you want to use. Access does not automatically locate the back end for you.

When you create a linked table Access stores the full path the back end database for each table. When you need to switch to a different back end location you must do one of the following:

 

1)      Delete all the linked tables. Recreate the linked tables.

2)      Use the Linked Table Manager to change the current linked tables to a new location.

3)      Use VBA code to relink the tables. You could automatically relink the table to a known location. You could also

provide a File browser dialog to allow the user to locate the backend.

 

Relinking tables in Runtime Mode

One of the issues with using the Access Runtime is how to provide an option for a user to select the location of the back end.  I normally use my own custom Link Table Manager using VBA code to handle the relinking.


VBA Code for relinking your tables:

J Street Access Relinker

Boiler Plate Database - By the Utter Angel -  includes table reattachment

ReLinker: multi-BE, Label Table Description to show Connect info  -  UtterAccess.com

Relink / Swap Between LIVE, TEST and LOCAL Data  - UtterAccess.com

Reconnect Attached tables on Start-up - by Peter Vukovic

Relink Access tables from code - by Dev Ashish

 

Additional Articles and Resources:

Get Back End database Location  - VBA code

Microsoft Jet Utilities

Reconnect Attached tables on Start-up

 
« StartPrev123456NextEnd »

Page 1 of 6

Sponsored Links

Login Menu




bottom
top

Latest News

Popular


bottom

Powered by Joomla!. Designed by: Free Joomla 1.5 Theme, linux hosting. Valid XHTML and CSS.