Microsoft Access

221K

Solutions

51K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Share tech news, updates, or what's on your mind.

Sign up to Post

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the result into the field.

In this short article, I show how to experience the same comfort in any text box, even when displayed in datasheet view. This trick is probably not suited for large scale or commercial applications, but it is rather fun, and perhaps even useful.



Overview

typing “=” after an expressionThe user types any mathematical expression, such as “312/0.8”, followed by the equal sign. The expression is replaced by its value, 390, which is stored in the field. Unlike in a spreadsheet, the expression isn't stored for future reference. However, typing the equal sign again restores the text of the last expression, so it can be corrected and re-evaluated.

If the user leaves the text box without pressing the equal sign, the default message is displayed: “The value you entered isn't valid for this field.” If the expression contains invalid syntax or is otherwise not understood, the textbox flashes in red, but no message is displayed. Besides that, the text box behaves absolutely normally, and can be used without ever triggering the built-in calculator.


Installation

The implementation is quite simple:
create or open a scratch database;
switch to the Visual Basic Editor and create a new module;
2

Expert Comment

by:Rosdi Ahmad
hi,

something i'm looking for like textbox calculator in QuickBooks. btw, i did test and it is not working.
0
I've known this trick for so long that I can't give proper credit any more. It might have been in the very first “Northwind Traders” database I studied, shipped with Access 2.0. Anyway, the idea is to display one thing in the edit portion of a combo box, and another in the drop-down section. Like this:
two column combo with concatenated columnsThe selected supplier is displayed as “Exotic Liquids, London”, but the list shows the same in two columns. This makes the list much more readable, and reveals the fact that the sort order is in fact by city and not by supplier name. This is incidentally a second trick, allowing the user to search the list visually by city, while still being able to select a supplier by name using “auto-expand”. Typing “sp” will select the “Specialty Biscuits” even if the list isn't sorted.

A demo database is probably superfluous, but here is one anyway. ComboMergeCols.mdb



How it's done


The combo box contains in fact four columns. The first is the key column with the supplier number, which will be stored as the value of the combo and perhaps in an underlying field; it is hidden entirely. The second contains the combined supplier name and city, and is almost hidden, in that it doesn't show in the drop-down list. The last two are the same fields in separate columns.

To almost hide a column, the width is set to the minimal accepted value, which should be below one pixel. Internally, all form metrics are calculated in twips, …
5

Expert Comment

by:developingprogrammer
Nice trick Markus! Thanks for sharing! = )
0
This article is a continuation or rather an extension from Cascading Combos and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous article first if you have little or no experience with the concept.

Cascading or Synchronized Combos are relatively easy to create and are quite ubiquitous in Access applications. However, the simple techniques in the previous article fail in continuous forms and datasheets, and there are frequent requests for solutions that work in them too.

I will try to explain the problem, and also why it is not likely to be solved in a future version of Access or any other database front-end environment. The situation is in fact best avoided altogether, for good reasons. If it needs to be done, there are no perfect workarounds, and developers who believe they need this feature are likely to be somewhat disappointed by the end result.

The attached demo file is identical with that of the previous article; you only need to download it once.
CascadingCombos.mdb


The Problem

intended resultBased on the previous article, the cascading combo boxes of the figure have been created on a continuous form. This is the intended look, and it works above because the current record has no category selected, meaning that all products are displayed in the products combo. However, during normal data entry or navigation, the form will look like this:
vanishing product names
6

Expert Comment

by:Zod12
Cannot seem to get the Parameter RowSource to work when the tables are linked to SQL Server, getting an ODBC error.
I think I need to convert the parameter to a Long or integer bu not sure how ?
0

Expert Comment

by:e2n e2n
To get around the top textbox having focus, I used the following event:

Private Sub TextBox_GotFocus()
Me.ComboBox.SetFocus
End Sub

Now when you click on the text box, you are instantly sent to the combo box behind it, where you can type.

Thanks for this great explanation above.  I'm trying to figure out a better way to come to the solution I need, as my subform has two of these next to each other, but I enjoyed learning a bit here.
0
This is another question that appears very often on EE, sometimes as “synchronized combos”. It is so common that Access now offers a demo database, downloadable from the “samples” section on the welcome screen, demonstrating two methods to achieve this result. “How to” pages exist for this since version 2.0 at least.

What result exactly? The user selects something from a first combo box, and is offered a filtered list of choices in a second. This can go on in a third, forth, or more “cascading” combos.

Given the frequency of the question, this article is bound to be redundant. Someone, surely, has already posted exactly what you are about to read? Strangely enough, no, not exactly. The solutions offered almost always suggest to rewrite the `row source´, which makes the process appear technical, and in fact raises a couple of technical questions rather artificially.

In this article I will show a very simple method first, and then move on to the more popular methods, to discuss their particular problems.

The demo database can be opened in parallel to follow the examples more easily. Make sure to switch to design view to examine the queries, the combo box properties, and the Visual Basic module.
CascadingCombos.mdb


A simple example


Imagine a form where the user will select a product in a combo box. Given the number of products, it would be easier to select a category first, thus filtering the list of products to only those belonging to that …
5

Expert Comment

by:developingprogrammer
thanks for sharing this harfang!
0
The first two articles in this short series — Using a Criteria Form to Filter Records and Building a Custom Filter — discuss in some detail how a form can be used as static criteria holder or to build a filter as a string, which is then passed or applied to other database objects.

This final article attempts to make these forms more versatile, by using the built-in function BuildCriteria, and discusses various problems encountered along the way. This is the most technical article in the series, and there will be little or no space for basic explanations. It is assumed that the reader is familiar with Visual Basic, at least passively.

____________________

The demo database is the same for all three articles. It contains tables from the well know Northwind Traders database, from a rather old version, as sample data. The forms are all variations on the common theme custom filtering.
FormFilters.mdb



The lack of Versatility


On a filter building form, let's imagine a control to enter a number. The user can type 10 or 42 to filter records with the desired value. In many cases, it is more meaningful to enter lower and upper bounds: <49, >=3, between 10 and 19... The solution is often to present an awkward and (to me) rather ugly cluster of controls. Open the conditional formatting dialogue and you will see what I mean. In that …
5
In the previous article, Using a Critera Form to Filter Records, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain open, and the data was never actually transformed into a filter.

In this article, Visual Basic code is used to build a filter as a string. It can be used to filter forms and reports, to search for records, or even to build dynamic SQL.

If the techniques shown here are not sufficient, more advanced solutions are discussed in the third article of this series, Custom Filters using BuildCriteria.

____________________

The demo database is the same for all three articles. It contains tables from the well know Northwind Traders database, from a rather old version, as sample data. The forms are all variations on the common theme custom filtering.
FormFilters.mdb



Why build a filter


A filter is an expression, which can become surprisingly complex, specifying which records should be included in a given context. Is is often described as a WHERE clause, without the word “where”. This means that the language used is SQL, or rather the SQL expression syntax. For example:
OrderID=10042
OrderDate>=#2011-01-01#
ShippedDate Is Null
Not Discontinued And UnitsOnOrder>0 And ProductName Like '*tofu*'
ShipCity='Paris' Or ShipCity='Reims'
CategoryID In (2,3,5)
UnitPrice Between 100 And 200
Salary > (Select Avg(Salary) From Employees)
9

Expert Comment

by:developingprogrammer
thanks harfang for the fantastic article!

just one quick point (for myself and also other who may not be very proficient in programming like me) -

The use of “+” as operator ensures that “AND” is inserted only between predicates. Other solutions work just as well, such as:

using the + to concatenate the string ensures that if there is a null within the string, the entire string becomes a null. in this case there is a null in the string because the Where is set to null at the start

Dim Where As Variant
   
    Where = Null

hope this helps! = )
0
This is such a frequent request that literally hundreds of pages can be found describing one technique or another to achieve the desired result. I particularly like one example from Microsoft, Adding (All) Options to Combo Boxes or List Boxes in Access 2007, which is by far the most convoluted solution I have ever seen, although it is remarkably generic as well. At the time of writing, a user comment on the page expresses the general feeling: “why does such a simple thing require 50 lines of code?”

The vital question that is often overlooked, or asked only in passing is: “why do you need this option, and how do you intend to use it?” Depending on the answer, the solution can be surprisingly simple.

The aim of the article is to show not one but several solutions to the request itself, and to concentrate equally on the way the “all” option is used.

A very simple demo file is included, but the article is hopefully understandable without downloading it.
ComboAddAll.mdb
Note: The database in in Access 2000 format, but was created in Access 2007. After opening the database in an earlier version, if the demo doesn't work, please remove any missing reference: from Visual Basic Editor, choose (Tools | References) and uncheck any libraries marked as missing. Then find and check both libraries named “Microsoft DAO ?.? Object Library” and “Microsoft ActiveX Data Objects ?.? Library”; use the highest version if you find several versions.



Look! The option is already there!

8

Expert Comment

by:KAMAL DEV
OH CODERS.! PLEASE DO THINGS EASILY.
0

Expert Comment

by:Savannah Winstanley
Hi!
This article was really helpful in getting a form passing parameters to a query to work the way I wanted.
However - I had a question on something that keeps happening when using the fixed query design (first section) -
When I enter the values in my form, it yields the right query with the fields filtered accordingly - however - one record - a particular location which is the first record in the referenced table is returned despite not matching those criteria - and more worryingly - it changes those fields to match the criteria. Any ideas on what's happening here?
0
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often need to add their own specialized tools.

This article will show how an unbound form (not linked to a table) can be created to let the user enter criteria for one or several fields, for example before opening anther form or a report. The central idea is to use the controls on the form directly as criteria for a query, which is then used as record source for a data form or a report.

Novice developers need to learn this method quite early, and it can luckily be applied with almost no Visual Basic coding. More advanced techniques are described in the next two other articles: Building a Custom Filter, and Custom Filters using BuildCriteria. Although they are meant to be read in sequence, more experienced developers will probably not do so.

____________________

The demo database is the same for all three articles. It contains tables from the well know Northwind Traders database, from a rather old version, as sample data. The forms are all variations on the common theme custom filtering.
FormFilters.mdb



Built-in Filtering


When a form bound to a table is opened in the interface, several shortcuts become available to create and manage both the filtering and the sorting of the data. They manipulate the form's current filter
15

Expert Comment

by:developingprogrammer
whao harfang, really really good article and tips! = )

by the way guys, this short quote from the follow webpage helped me understand the + and & difference better! hope it helps!

     
In VBA and Jet/ACE SQL the & concatenation operator ignores Null (Null & "" = "") while the + concatenation operator propagates Nulls (Null + "" = Null). Null concatenation is quite useful in expressions like Mid(("12 + LastName) & (", " + FirstName), 3), but you have to be careful not to try to use it with numeric fields (or strings that can be implicitly coerced to numeric values), since while "12" + Null will propagate the Null, "12" + "8" MAY add the numeric values of the two strings (it depends on the context). – David-W-Fenton Sep 10 '10 at 19:30

http://stackoverflow.com/questions/3627526/vba-problem-with-string-concatenation
0

Expert Comment

by:Laura Sheldon
I learn better when using the actual example databases, but when I downloaded this one (FormFilters.mdb) from above, I kept getting the error, "access 2013 the expression after update you entered as the event property setting produced the following error: The expression you entered has a function name that Microsoft Access can't find." when trying to use the forms. Can you tell me why or if there's something I need to do before they will work?
0
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather which page to read? At the time, there was no “intellisense”, so it could take me a couple of minutes to find for example “.ItemData()”, even if I had used it before.

Another more obscure problem was that I could never trust myself to use the right indexing in the right property. I would type “column(2)” with hesitation and an urge to check if shouldn't be 1 or 3 instead. I learned the cause much later. Row indices are zero-based or one-based depending on the property used; column indices are also inconsistent when column headers are involved. Before I became full aware of this fact, I had simply learned intuitively that, whatever indexing I used, it was wrong every other time.

In this article, I dissect a combo box and show when each indexing is used, and for what reasons. Properties are presented by theme, making them easier to remember. They are then used to perform some typical tasks, in a “how to” section.



Anatomy of a List


In the text below a list is simply the full content of a combo or list box, including any hidden columns. I will use list box if I want to designate specifically that type of control.
combo box and data tableThe innocent looking combo box at the left can contain an entire table
7

Expert Comment

by:developingprogrammer
thanks harfang for this fantastic article! this is my favourite article after Understanding the ComboBox because it clarifies so many things and allows us to FINALLY use VBA with ComboBoxes. to build reuseable function to handle header and non-header situations.

the 1st picture you showed showing how Access numbers the rows and columns is FANTASTIC. that can already sum up so much. really, really, fantastic.

thanks so much harfang!! = ))
0
It often makes sense to show lists as comma-separated items, especially in reporting. For example:

available colours: red, green, blue.
However, the reporting engine is biased towards tabular layout:

available colours:
red
green
blue
The latter is easy to achieve, even using only the report wizard. The former requires some programming.

The other day, I needed to produce a comma-separated list on a report, and was about to download from another article, Access & VB's Missing Domain Lookup Functions, the function DList(), designed for exactly this purpose. I was reluctant to add a module in this particular database, so I thought a little longer and realised that the report I had created already did 99% of the job.

Two lines of code are all that is needed to switch from the tablular layout to a list.


Reporting mechanics

report in design viewThe tabular layout uses a header, for example a group header if several item lists are displayed, and a detail section. The footer would be used to display sub-totals or to provide some white space at the end of each group. The sections are printed in sequence: header, detail(s), footer, header, details(s), ... Each group will have at least one detail section. However, depending on the record source and the data, there might be no item to display in a given group. There will be a detail section, but …
2
LVL 58

Author Comment

by:harfang
> It had never occurred to me...

Shortly after writing this article, I read an open question where that Asker wanted the “detail section of a report to print horizontally”. Two experts, als315 and boag2000, had already provided the exact solution detailed in this article, along with two others (using a concatenation function and using a columnar subreport).

It's like after you learn a new word: you seem to hear it everywhere. I searched some more and, sure enough, the technique has been described in Microsoft's knowledge base a decade ago, for example in: ACC2000: How to Concatenate Data from the 'Many' Side of a Relationship. My code is perhaps more concise, but only marginally better...

(°v°)
0
In the article Objects – Part 1, you learned the basics of working with objects, properties, methods, and events. In this article we step it up a level. You learn how to work with object variables and how to work with collections of objects. You also learn how to pass objects to subroutines and functions, and we explore special properties that refer to objects. As with the techniques covered in Part 1, the techniques that you learn in this article make you much more powerful as a VBA programmer.

Declaring and Assigning Object Variables

Object variables are variables that reference an object of a specific type, such as databases, recordsets, forms, controls, and even objects created in other applications. They allow you to create shortcut references to objects and pass objects to subroutines and functions. You can use them to streamline code by using short names to refer to objects with long names and to optimize code by supplying a direct pointer to a particular object.

First, you must declare an object variable; then you assign, or point, the object variable to a particular object, as shown in the following code:

Private Sub cmdChangeCaption_Click()
    'Declare a CommandButton object
    Dim cmdAny As CommandButton
    'Point the CommandButton object at the cmdHello Command button
    Set cmdAny = Me.cmdHello
    'Change the Caption of the control referenced by the cmdAny variable
    cmdAny.Caption = "Hello"
End Sub

Open in new window

8
Objects, properties, methods, and events are at the heart of all programming that you do within Microsoft Access. Without a strong foundation in objects, properties, methods, and events, and how you should use them, your efforts at Access and Visual Basic for Applications (VBA) programming will fail. This article introduces you to Access’s object model. You will not only become familiar with Access’s objects, properties, methods, and events, and how to manipulate them, but you will also learn concepts that will carry throughout your Access and VBA programming career.


Understanding Objects, Properties, Events, and Methods

Many people, especially those accustomed to a procedural language, don’t understand the concept of objects, properties, methods, and events. As mentioned earlier, you need a thorough knowledge of Access’s objects, their properties, the methods associated with them, and the events that each object can respond to if you want to be a productive and successful Access programmer.

What Exactly Are Objects?

Objects are all the things that make up your database. They include tables, queries, forms, reports, macros, and modules, as well as the components of those objects. For example, a Table object contains Field and Index objects. A Form object contains various controls (text boxes, combo boxes, list boxes, and so on). Each object in the database has specific properties that determine its appearance or behavior. Each object also has …
13
This article is part of the app development series, a series of articles on Experts-Exchange.com that explores common application development problems and their solutions.

This article presents code written in VBA executing on Windows platforms, but the techniques demonstrated here could be used with any language or application.

Level: Intermediate

The problem:

 Often when developing applications the need arises to control a resource so that other users can’t do something with that resource until you are through with it.

  For many developers, this starts out with the need to lock a specific record and for whatever reason, they don’t want to use the DBMS system to do the locking.  For example, your DBMS system might only lock on pages and you want to be able to lock individual records.   You might also want to “lock” a record in a logical context within the application; you want a customer record to be held from any changes because you are trying to contact the customer. In these cases, locking the record through the DBMS would be impossible or impractical for a number of reasons (i.e. simply cannot do it or length of time).

  Of course your first thought might be to place a flag in the record itself.  A simple yes/no field would work, although you might go one step further and record a user ID and a date/time.  This sounds great (and does work), but what happens if the power goes off?  When the app comes back up, you may now have a number of …
5

Expert Comment

by:Anton Greffrath
Jim

Fantastic article on 'Resource Locking'. Thank you. This addresses one of my biggest challenges in my MS Access applications.
One Question though, Where in the Application should the functions be called?

Thanks
Anton Greffrath
0
LVL 62

Author Comment

by:Jim Dettman (EE MVE)
Anton,

Glad you enjoyed the article.

<<Where in the Application should the functions be called?>>

  Anywhere you want/need them.   For example, if I have a single label printer that's shared, then I "lock" the printer just before I print, and clear the lock when I'm done.

  If I'm tracking users, then I place a "user" lock right after the login form and don't clear it until the app quits.

 Or if I have a call record that a supervisor needs to look at, it becomes "locked" on based on some condition, then is not cleared until a supervisor reviews the problem.

 Think of the locks more as a flag or reservation.

Jim.
0

Introduction

This article introduces the reader to the concept of a table (or query) of numbers, commonly called a Tally Table.  I detail different methods to create and populate a Tally Table in Access and show the new table used as part of a problem solution.

For years I've used an OneRow table as a part of my Access solutions, eliminating many duplicate values in a very efficient manner.  In the past couple of years, I have seen a similar special table in SQL Server and MySQL articles.  I have now participated in an EE Access question whose solution lent itself to a Tally Table.

Definition: A Tally Table is a record source (database table or query) that is a contiguous sequence of integer values in consecutive rows.  This is sometimes called a number table.

Why use a Tally Table: There are many situations where you need a sequence of values.  While it is easy to write loops in VBA and TSQL, there are no looping statements in Access SQL syntax.  While a Tally Table is a sequence of integer values, such values can be transformed into other Access/VBA data types (character, floating point, date, etc.)

If you are interested in using a Tally Table for a non-Access database, start by reading these articles.
SQL Server - http://www.experts-exchange.com/A_1221.html
SQL Server - http://www.sqlservercentral.com/articles/Tally+Table/72993
MySQL - http://www.experts-exchange.com/A_3573.html
SQLite - http://www.experts-exchange.com/A_3570.html


5
LVL 48

Author Comment

by:aikimark
Attention readers:

I caught an error on the first of the delimiter position queries.  The length parameter of the Mid() function was a literal.  It should have been equal to the length of the [Delim] parameter.  I also simplified the query, although it still does not perform as well as the InStr version of the query.

DelimiterPositions
PARAMETERS Delim Text ( 255 );
SELECT DISTINCT DataToSplit.PersonID, TallyTable.ID
FROM DataToSplit, TallyTable
WHERE ((TallyTable.ID<=255) 
AND 
(Mid([Delim] & [data] & [Delim],[id],Len([Delim]))=[Delim]));

Open in new window

0
LVL 48

Author Comment

by:aikimark
Here is a similar question, where a data source (query) contains startdate and stopdate columns and the member needed to populate another table with multiple rows, containing individual dates between these two date column values.

A new constraint in this question is the need to prevent duplicate sets of rows when the process was repeated.  Therefore, a new table was required (HasBeenCloned) to persist the key values involved in the copying process.

http:/Q_27663167.html
0
Why Debugging Is Important

A good programmer is not necessarily one who can get things right the first time. To be fully effective as a Visual Basic for Applications (VBA) programmer, you need to master the art of debugging, the process of troubleshooting your application. Debugging involves locating and identifying problem areas within your code and is a mandatory step in the application-development process. Fortunately, the Access 2010 Visual Basic Editor (VBE) provides excellent tools to help you with the debugging process. Using the Access 2010 debugging tools, you can step through your code, setting watchpoints and breakpoints as needed.

Using the VBA debugging tools is significantly more efficient than taking random stabs at fixes to your application. A strong command of the Access 2007 debugging tools can save you hours of trial and error. In fact, it can be the difference between a successfully completed application-development process and one that continues indefinitely with problems left unsolved.


Avoiding Bugs

The best way to deal with bugs is to avoid them in the first place. Proper coding techniques can really aid you in this process. Using the Option Explicit statement, strong-typing, naming standards, and tight scoping can help you eliminate bugs in your code.


Option Explicit

Option Explicit requires that you declare all your variables before you use them. Including Option Explicit in each Form, Code, and Report module…
2
I have seen a lot of questions from people around the internet about changing the ugly background of the Microsoft Access database window. : )

I’m going to explain a technique for changing the appearance of the background of a database- with almost no code at all!

The only code that will be added will be an open form command.

•I should clarify that this procedure will not literally change the Access background. To do that you would need to use an API.

Two API based solutions are:
http://www.mvps.org/access/api/api0035.htm
http://www.lebans.com/changemdibackground.htm

This is a technique that provides a good and simple work-around to a complex problem that produces almost the same result with nearly no code. I will let you be the judge of how it stacks up to an API approach.

The challenge to this is going to be grasping the concept.   The technique is for lack of a better term “layering” the forms. The best way to think of this is it is a splash screen that does not popup and does not close.  Another way to think of this technique is "framing a picture with a mat".

Now on to the steps:

- Create a new form.
- Set your desired background picture or choose a Detail back color.
- Save your new form.

Now set a few more settings in the form’s properties to make it appear correctly. In the form's properties set:
- Pop Up = No  (required)
- Record Selectors = No
- Navigation Buttons = No
- Scroll Bars = No
8

Expert Comment

by:Victor Sanchez Ramirez
This is a great work around. Thanks.
0
Access 2010 provides both users and developers with an excellent means of automating the databases that they build. Microsoft has completely revamped the macro designer in Access 2010. Furthermore, they have added a plethora of new features that make macros more powerful and utilitarian. Finally, using macros, you can create a database safe for running on the Internet. You cannot accomplish this using VBA (Visual Basic for Applications) programming code. This article explores the new macro designer, and other new macro features available in Access 2010. After reading this article, you will be able to take full advantage of what Access 2010 macros have to offer.

Let’s begin by exploring the Access 2010 macro designer. As you can see in Figure 1, the macro designer in Access 2010 looks significantly different than that of its predecessors. You can select an action from the Add New Action dropdown (see Figure 2), or you can drag and drop actions from the Action Catalog over to the macro designer.

Figure 1      
Figure 2
A great tip with the macro designer allows you to quickly place an If statement around existing macro actions. Without this tip, if you need to add an If statement to a macro you must drag and drop all the actions that you want to place within the If one statement at a time into the If statement. To easily surround existing statements in a macro with an If statement, you must first select the statements that you want to place …
13

Expert Comment

by:Juan Velasquez
Good Article
0
One of the hot computing terms of the 21st century, client/server refers to distributed processing of information. A client/server model involves the storage of data on database servers dedicated to the tasks of processing data and storing it.

The client/server model introduces a separation of functionality. The client, or front end, is responsible for presenting the data and doing some processing. The server, or back end, is responsible for storing, protecting, and performing the bulk of the data processing.

With its tools that assist in the rapid development of queries, forms, and reports, Access provides an excellent front end for the presentation of back-end data.

For years, most information professionals have worked with traditional programming languages to process and maintain data integrity in the application. This means that data validation rules must be embedded in the programming code. Furthermore, these types of applications are record-oriented; that is, all records are read into memory and processed. This scenario has several drawbacks:

If the underlying data structure changes, every application that uses the data structure must be changed.
Data validation rules must be placed in every application that accesses a data table.
Presentation, processing, and storage are handled by one program.
Record-oriented processing results in an extraordinary amount of unnecessary network traffic.

Deciding Whether to Use the Client/Server Model
5

Expert Comment

by:developingprogrammer
Great article TechMommy, thanks for sharing with us!!

Hrmm one question, how do I save this article into my personal knowledgebase? I want to go through it again in the future = )
0

Expert Comment

by:developingprogrammer
Oops apologies I found it! Using EE on the iPad and didn't see the Save button. Great article and really helps me!

Btw here's a comment by Christian on database servers that really helped me too! = ))

Hi,

yes, you can of course run an Access application on any server and so you would have an "active database server". But that's not really the same as a database server. Think of security: You can simply copy the backend file and make with it whatever you want. You can connect the database file from Excel and destroy any data you want, the only "security" Access has is a database password (which any user would have to enter if he want to use it, so the user who destroys the data maybe already has this password...). In most cases the user must have write access rights to the folder where the database file is located, so you could simply exchange the file if currently nobody is working with it and so on.

Think of performance: Any frontend would have to connect to the file, not the running Access on the server. So it gives you no performance advantage having a running Access (you would have a disadvantage because it steals performance if it does anything actively).

What does a database server for you?:
 - it secures the files needed to save the data because you connect always to the running server procedure which handles the security - you have no access rights to the data files if you're not a db admin. You can not even see the files or where they are located.
 - you have a system which optimizes anything at any time. It optimizes for example the number of connections with connection pooling, it optimizes the statistical data for accessing data through the indexes, it optimizes the indexes itself, it optimizes execution plans, all this with also a lot of caching, it optimizes which procedure will run first to get the best answer time, it deploys procedures to different processors, and so on... can you do that with a running Access as "server"?
 - you have a system which has a LOT of additional possibilities, in case of SQL Server for example the Integration Services which allows very complex imports which could be designed with a graphical editor (better than the query editor, think of it as a data flow diagram with programming possibilities), the Analysis Services, which can collect data warehouse informations and creates extreme complex possibilities of analyzing data in cubes with the Business Intelligence Studio which then can easily be used in Excel (with a free addin from MS), keyword: Data Mining. The Reporting Services which is similar to the reports in Access (a report designer) but with a LOT more possibilities like automatically deploying reports in mails only for the user who wants it, like a standard Web interface where the user (and only users which where given the rights) can work with all his assigned reports, can drill down them into other reports, can run on a dedicated report server independant of the database server and much more. The Notification Services which can forward any information you want to any device you want, including mails, mobiles or whatever.
 - SQL Server also has a compact edition which can replicate data to mobile devices so you can run your database there (but not with Access frontend) and replicate it back.

So that's enough advertising for Microsoft today...:-)
You can of course do many of these things also with other database servers like Oracle, MySQL, Firebird, PostgreSQL, whatever. SQL Server is only the best choice in case of Access as frontend as it naturally works best with software from the same producer.

You see, you can learn a lot about Access and VBA. But if you are as far as knowing ANYTHING about Access and VBA together, you can say that you can learn at least ten times as much about database servers, there are SOO many fantastic features where you now can't think of because Access does not offer that for you. But you can use these features to make the experience with Access a really better one. So really: You should forget Access as backend database, that's a choice for beginners and for some companies where the people who makes the decisions are not aware how easy it is to work with a very much better database and how many security holes they produce in letting internal people create "some little databases" with Access.

Cheers,

Christian

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28208447.html
0
This article explores the many different roles Access can take in an application design, explaining the pros and cons of each method.

The Front-End and Back-End as Access Database Files

One option when developing Access applications is to use Access as both the front-end and the back-end. The Access database is not acting as a true back-end because it is not doing any processing. Figure 1 shows the architecture in this scenario. The Access application resides on the workstation. Access uses the Access Database Engine to communicate with data stored in an Access database file stored on the file server.


Figure 1 - Access as the Front-End and Back-End
The Front-End as an Access Database File Using Links to Tables Stored on the Back-End

In the second scenario, you can link the back-end tables to the front-end application database (.MDB or .ACCDB). The process of linking to back-end tables is almost identical to that of linking to tables in other Access databases or to external tables stored in FoxPro, or other database formats. You can also treat the linked tables like any other linked tables. Access uses ODBC to communicate with the back-end tables (see Figure 2). For example, when you build an Access query, your application sends an Access SQL statement to the Access Database Engine, which translates the statement into ODBC SQL. The Access Database Engine sends this ODBC SQL statement to the ODBC Manager, which locates the correct ODBC driver and passes it the ODBC SQL …
7

Welcome to part two of my outlook view control tutorial/article/faq. In this tutorial I will show you how to interact with the ovc you have put on your form. if you are reading this and don’t have the ovc on a form yet go check out part one of this article http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4616-Outlook-View-Control-OVC-part-one-Putting-the-OVC-on-an-Access-2007-form.html

Ok first I will quickly go over the non programmatic way of changing the “folder”/ what you see and then I will get into the code that you can use to interact with the OVC in Access.

To change your “folder”/what you see
1.      Right click on the ovc
2.      In the right click menu that appears select “Properties”
3.      In the properties menu click on the “folder” line so that it is selected.
4.      After you have clicked in the “folder line go up to the textbox at the top of the properties window and in it type the word “calendar”
5.      After you have typed in the word “calendar” click the “apply” button located on the left side of the text box where you just typed in “calendar.”
Bam!! You should now see one of the views of your calendar.

If you want to change the view of the calendar goto the properties menu select “view” and in the textbox on the top of the properties window type in one of the following options then click the apply button.

Day/Week/Month
Day/Week/Month View With AutoPreview
All Appointments
Active Appointments
Events
Annual Events
Recurring
4
LVL 10

Author Comment

by:conagraman
Mark

awesome! thank you again!

Cheers,
conagraman
0

Expert Comment

by:elbitcho
I have tried to implement the same in a Access 2013 client form, however it appears the Folder and View properties can no longer be set, either manually or in code. Do you by chance have a similar solution for Access 2013?

Thanking you in advance! with fingers crossed

Cheers,
Dale
0
I have seen a lot of questions about putting calendars on forms and viewing your Outlook inbox in Access. Even though many people will tell you putting the activex Outlook view control on a form is not possible i am here to show you it is. Here are the steps.

1.      Create a new blank form and go to your forms Design View. if you’re not there already right click on your form and click “design view”.
2.      insert a "Microsoft Forms 2.0 Frame" active x control onto the new form that you just created.
3.      right click on the frame you just inserted into your from.
4.      in the right click menu that pops up move up to “frame Object” and select “edit”
5.      At this point you will see the “toolbox” menu popup. Right click inside the tool box “controls” tab. (If you don’t see a menu  its because you clicked outside of the “controls” tab area.)
6.      In the right click menu click on "Additional Controls"
7.      in the additional controls menu scroll down and check the box next to  "Microsoft Office Outlook View Control. (this may already be checked)
8.      after you have clicked in the box next to “microsoft Office Outlook view control” and there is an x in the box  click the OK button.
9.      Now in the tool box a new button will appear that looks like a list box.  if you mouse over it it will have a tool tip that says "ViewCtl." click on that button.
10.      After you click on the button click go back to your frame control  (the tan box) you have on your form  and draw/drag out a box a box.

Bam!! …
2
INTRODUCTION

Joining tables is a way to tell the DBMS (Access in this article) what data to hold in memory from a group of tables. The purpose of a DBMS is to store, retrieve, and analyze data. Join is a way to limit the retrieved data that is stored in memory for further processing.  Joins include cross join, inner join and outer join types.

This article addresses the inner join and introduces a way to help design the FROM clause of a SQL statement using INNER JOIN of many tables. It removes the confusion of ordering and bracketing pairs of tables in the join sequence. This is treated with reference to the way a relation is drawn in query design mode.

This is a step by step tutorial using the Access environment.  A sample database is included to reduce the effort of recreating the necessary setup needed to continue with the tutorial.

A new approach will be presented to compile the join part by knowing the order of relations between tables, and the start and end fields of each relation

AUDIENCE

You understand the need for joins between tables.
You know the types of joins between tables.
If you experience some confusion in writing  the SQL by INNER JOINing the five tables, keep reading.
If you are looking for a concrete way in writing the join clause, keep reading.


PREREQUISITES

It is assumed that you:
understand  the difference between a Table and a query;
know how to use the query designer to relate tables;
6

Expert Comment

by:funaroma
"That is fine, so giving the readers an example will not be difficult or time consuming."

When did this become MY job?  You're the "expert." who wants votes.  =)  I simply proposed that a real world example would reach more people.  I stand by that statement.

"As an instructor, concrete does not mean using Employees instead of E."

Depends on what, and whom, you are instructing.  You haven't mentioned who you feel your target demographic for this article actually is.  As an instructor, that's the first thing you should understand.  If you were to come back to me and tell me "this article is intended for an audience of Computer Science majors, at least 2nd or third year" then I'd not have an issue.  Beyond that, I think your article is difficult to approach for the average Access user.

"This is important because I am trying to write an article ".... from concrete to abstract"."

This would be great if it were true.  But you couldn't have started out any more abstract:

"For a database of 5 tables, A(a, r, x), B(a, b, r, x), C(b, c, r, x), D(c, d, r, x), and E(d, e, r, x), and a table of relations between these tables"

Notations do NOT lead to more concrete understanding.  Quite the contrary - they are just another layer of abstraction.

I've met a lot of people, right out of college, that studied with instructors "teaching" exactly this way.  Frankly, their problem solving skills left a lot to be desired, and they couldn't code (or troubleshoot, or even convey meaning) their way out of a paper bag.  If they can even remember which concept to apply (because they never had a concrete, real-world example to which their minds could attach the exercise or technique), they often apply it completely incorrectly.

This was not intended to be a pissing match.  Heck - someone already voted "yes" to your article long before I came along, and as of this comment, 5 of 6 people found it "useful."  So it looks more and more likely that I'm completely off base here.  I feel the suggestion I've made couldn't HURT you any...  I think you'll get your (valuable!!) message across to a lot more people, with a broader range of experience and needs, if you consider updating and changing your writing style for this or for future articles for this site.  This site is not a CS400 college text book, it's a site where people with much less skill and knowledge come to get help.  Writing for that audience is a skill all its own, and would highly compliment the skills you clearly already possess.  It's HARD to make things look easy... but the challenge is worth it.  If more college professors understood this, many more college students would graduate with more useful skills, right from the start.

Take it for what it's worth... I have no personal bone to pick with you of course.  It looks like I might be the odd man out on this one, but give the suggestion some thought anyway... perhaps just consider it an exercise for your OWN development as a writer.  I'd be interested to see what you came up with.
0
LVL 31

Author Comment

by:Hamed Nasr
funaroma:

My last comment on this side argument.

"When did this become MY job? '
I was expecting a real example of the type you think a user can better learn from.


"You haven't mentioned who you feel your target demographic for this article actually is"

In the second subheading in the article:

AUDIENCE

You understand the need for joins between tables.
You know the types of joins between tables.
If you experience some confusion in writing  the SQL by INNER JOINing the five tables, keep reading.
If you are looking for a concrete way in writing the join clause, keep reading.
0

Introduction

One of the leading measures of central tendency is the average, or arithmetic mean.  Microsoft Access makes it easy to calculate the average for a data set by using the Avg aggregate function.  However, the arithmetic mean is not the only type of mean that exists, and for certain analyses, other types of means may be more appropriate.

This article will demonstrate how to calculate several two other useful, yet less-commonly used means, using Microsoft Access:


Part 2 of this article will extend the discussion to include the following other types of means:


Note: the SQL statements required for these “special” kinds of means are somewhat complex, but should be within the grasp of any intermediate or advanced Access user.  For that reason, I have not provided VBA versions of these functions.  Further, by relying solely on native Access functions, your application will exhibit faster performance.


Sample File

For examples of all of the cases discussed below, please refer to the attached sample file:
Special-Means-Part-1.mdb
This file contains all of the data and query definitions used in the four numbered examples in this article, and you may find the file useful for extending these examples or creating your own new examples.


Weighted Average

A weighted average
5

Introduction

While the average, or arithmetic mean, is probably the most commonly used measure of central tendency, it is certainly not the only such statistic.

In my article Calculating Special Means in Microsoft Access Part 1, I discussed how to calculate the weighted average and harmonic mean.  This article will demonstrate how to calculate several two other useful, yet less commonly used means, using Microsoft Access:

Indeed, all four of those means, as well as the standard arithmetic mean, minimum, and maximum, are all specific cases of the generalized power mean, which we'll also examine in this article.


Note: the SQL statements required for these “special” means are somewhat complex, but should be within the grasp of any intermediate or advanced Access user.  For that reason, I have not provided VBA versions of these functions.  Further, by relying solely on native Access functions, your application will exhibit faster performance.

Sample File

For examples of all of the cases discussed below, please refer to the attached sample file:
Special-Means-Part-2.mdb
This file contains all of the data and query definitions used in the four numbered examples in this article, and you may find the file useful for extending these examples or creating your own new examples.


Geometric Mean

The geometric mean
3
How Cloud Computing Applies to the Microsoft Access Community

Cloud computing will be a huge benefit to the information worker and Access community. Instead of worrying about the hardware and deployment issues around applications, one can focus on building the solution and using the enterprise quality cloud platforms which previously didn't exist or were prohibitively expensive and difficult to use. With Microsoft Access 2010 and SharePoint 2010, Access applications (in limited form) can be deployed over the Internet. With Microsoft Windows Azure and SQL Azure, one can create .NET applications and/or SQL Server databases in the cloud.

Using Microsoft Access to Connect to the SQL Server in Azure

From a Microsoft Access database, you can connect to a SQL Azure database and use those tables the same way you could link to SQL Server database on your network or SQL Express on your desktop. For a fraction of the cost of buying and setting up a SQL Server box on your network, you can have Microsoft do it for you without worrying about licenses, downtime, hardware, etc., and it's available over the Internet to anyone you give the credentials for logging into it. It's pretty simple:

Open an Azure account and create a SQL Azure database
Install SQL Server Management Studio (SSMS) for SQL Server 2008 R2 on your machine
Use the ODBC administration tool to create a file containing the connection to the SQL Azure database

5
LVL 56

Expert Comment

by:Mark Wills
Good Article.

Went to a MS meeting the other day. Azure and the cloud are very important initiatives for Microsoft, so great to revisit this Article and vote "Yes".

Would like to see more here in EE :)
0

Microsoft Access

221K

Solutions

51K

Contributors

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.