Thursday, August 14, 2014

What does NULL mean? How is it different than the Empty String?

One database concept given to much misunderstanding is the NULL value. What is it?

Null means that the value is unknown. This is different from the Empty String. The Empty String ("") means we know what the value is, and the value is, well, empty. That is, we know that there ISN'T a value.

Consider the case of the middle initial. Some people have a middle initial and some don't. Of those that do, we may or may not know what it is. If I leave it Null, it says that I don't know whether they have one or not. However, I can set the field to "" (Empty string) which says that they DO NOT have a middle initial.

Why is this difference important? Primary keys, for one thing.

Let's assume that my primary key is a composite key composed of FirstName, MiddleInitial, LastName. A primary key CANNOT have a Null as a part of it, thus we can set the value of MiddleInitial to "", and it will accept it. But we cannot leave the value Null.

Why can't a Primary Key have a Null value? Well aside from the fact that Access won't let it, the fact that Null means "I don't know the value" means that it cannot positively guarentee that the field is unique, which is one of the properties of the primary key.

The following four subs illustrate the differences:
Sub test()
If Null = Null Then
MsgBox "True"
Else
MsgBox "false"
End If
End Sub

Running this sub will ALWAYS evaluate to False. Why? Because we don't know what the value of NULL is, so we can't say if it's equal to NULL.

Sub test2()
If IsNull(Null) Then
MsgBox "True"
Else
MsgBox "false"
End If
End Sub

This evaluates to True because the IsNull() is specifically designed to test for NULL.

Sub test3()
If "" = "" Then
MsgBox "True"
Else
MsgBox "false"
End If
End Sub

This evaluates to True because the empty string is a known value.

Sub test4()
If IsNull("") Then
MsgBox "True"
Else
MsgBox "false"
End If
End Sub

False because "" is known, therefore it cannot be unknown.

As I said, NULL=NULL always evaluates to False. Since we don't know the value of Null, we can't say what it equals. This is why you never use NULL this way, but always use the IsNull() function.

Further, Len(Null) does evaluates to Null, as indeed do most things you *compare* with or *do to* Null, ie. Null=Null, X=Null, Null=True, cint(Null), etc.

Len("") ("" representing the 'Empty String' or 'Zero Length String') evaluates to 0 (zero). Because the length of a string of zero length is zero (duh!, my daughter would say).

The problem with explaining NULL, is all the good words are used for something else. You can't define it with itself, so saying "Null is Null" is null content (forgive the expression). You can't use "nothing", because that is an object pointer used to de-allocate object variables. You can't use "empty", because that is a constant specifically for use with a Variant variable. You can't use Zero either, because that represents a specific numeric value, rather than the absence of a value.

I guess we'll have to blame E.F. Codd who declared that any relational database implementation must have a special value called "Null" which represents the "absence of anything" or “unknown” condition for all datatypes.

The best we can do is to say that the value of Null is 'we don't know', which is not to say that we don't know what Null is ... I'd better stop there.

Tuesday, August 5, 2014

Featured Sample: Really Bad Design Decisions

Really Bad Design Decisions

This is a case study of a really bad database design . Often, initial design decisions can have a cascading effect, creating multiple, secondary design errors.

One such error, commonly made by novice developers, is to slavishly follow a pre-existing paper form to determine their table design. But paper forms are not created with proper database design in mind. They are designed to make it easy for humans to fill out.

This design can be at odds with established design principles. By blindly following the paper form to determine the database design, the developer can create a system subject to numerous data and relational integrity errors.

Sample contains an explanatory document and two databases: "ReallyBadDatabase.mdb" and "ReallyBadDatabaseReborn.mdb". The former shows the initial design, the second shows an improved design. Sample include both the data model and application.

You can find it here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=291

Featured Sample: At Your Survey (AYS)

At Your Survey

Author: Duane Hookom

At Your Survey (AYS) is a full featured application that allows users to create their own surveys by designing the questions and providing a lookup of possible responses. ATS uses a fairly normalized table structure so the same tables, forms, code, queries, and reports can be used for any number of surveys. There is a brief manual to help you get started as well as a sample survey with data.

Find out more here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

Friday, August 1, 2014

Access 101: Can I Create an EXE from my Access Application?

There is no way to create an EXE from an Access database. However, there is a way to install what's known as the "Access Run-time engine" that will allow the user to run the app without having Access installed on their system. The Run-Time is NOT the same as an EXE. For one thing, anyone who has Access on their system will still be able to open the application in their copy of Access (assuming they have the right version).

The Run-Time engine is installed as an option in the Package and Deployment Wizard. In Access 2000, this wizard came with the Access Developer's Edition. In Access 2003, you can get it in the Office Developer Extensions
(http://msdn.microsoft.com/en-us/office/aa905403.aspx). In Access 2007, you can download the 2007 Developer Extensions and Runtime (http://office.microsoft.com/en-us/access/HA102188681033.aspx). To run the Wizard, go to the Visual Basic Window, choose Tools, select the wizard, and follow the directions.

There are a few things to keep in mind when creating an application that will be used with the Run-time:

  1. NEVER use macros. If the macros fail, you don't get the HALT screen, the whole app just crashes.
  2. Error trap EVERYTHING. You want a graceful exit from every error. This is good practice regardless.
  3. Don't rely on the native Access toolbars or menus. You must provide all functionality yourself. For instance, if you rely on the Find button (binoculars) to search for a record, forget it. This button won't be available to the app when using the Run-time.
  4. The install disk created with the wizard will be very much larger than your program. However, in this day of CD burners, this is not the issue it once was.

Now, one thing you can do to keep your users from modifying the application is to convert it to an MDE. This is not an executable either, but it DOES have all of the source code removed. That means the user cannot open a form, report, module, or macro in Design view. Tables and queries can still be modified though.

So between converting the app to an MDE and deploying it with the Access Run-time, this comes very close to the functionality of the EXE you are looking for.

Thursday, July 24, 2014

Ambiguous Outer Joins

The Outer Join can be a powerful tool for querying data in Microsoft Access. When you have only two tables, there is usually no problem. When there are more than two tables, however, using an Outer Join becomes more complicated. Sometimes Access allows it, and sometimes it gives you the not-very-descriptive "Ambiguous Outer Join" error.

Why? Well, first we'll look at what an Ambiguous Outer Join is, and then see how to correct it.

Microsoft Access has three types of joins: the Inner Join, the Right Join and the Left Join. Both the Right and Left joins are known as Outer Joins. An Inner Join shows only those records that exist in both tables. However, an Outer Join (both Right and Left) shows all of the records from one table (the Base Table) and just the matching records from the other (Secondary Table).

When Access processes a multiple table query, it needs to determine the order in which joins should be made. Should it join Table1 to Table2 first and then join Table3? Or should it do it in some other order? This is part of the Rushmore technology of the Jet engine. It tries to determine the most efficient way to process the query.

In the case of standard Inner Joins, the result set will be the same, regardless of the order in which they are joined. However, this is not the case with Outer Joins. There are times, when using an Outer Join, that the result of the query will be different depending on the order in which joins are created. In this case, Access cannot determine the order to join the tables. This is an Ambiguous Outer Join.

So how do you know when an Outer Join will result in an error? The easiest way to understand it is in terms of what you see in the Query Builder grid.

A table which participates in an Outer join as a Secondary Table (that is, the arrow is pointing *towards* it) cannot participate in either an Inner Join, or as a Secondary Table in another Outer Join. Figure 1 shows two types of queries that will result in an Ambiguous Outer Join error.

Figure 1: Two illegal Outer Join Queries

However, the table participating in the Outer Join as a Secondary Table can participate in another Outer Join if it is the Base table of the other Outer Join (that is, the arrow points *away* from it). Figure 2 shows a query that will not result in an Ambiguous Outer Join error.

Figure 2: A legal Outer Join Query
So what do you do if you need to create a query like case 1 or 2? You have to split the query into a stacked query, that is, two queries, the second of which uses the first. This is exactly what the Ambiguous Outer Join error message suggests.

Create a query joining the first two tables with an Outer Join and save it as a named query (i.e. Query1). Then, in a second query, join the first query to the third table.
Figure 3 shows how to build a stacked query.

Figure 3: Shows how to split the query into two queries to avoid an Ambiguous Outer Join.

So the Ambiguous Outer Join error is not really all that confusing. It simply means that the database wants you to decide which join it should create first. In Access, you do this by spitting the query into a stacked query.




Friday, December 13, 2013

Access Web Databases: 2010 vs. 2013

Access web databases are dead.  But there’s no cause for alarm.  Now we have Access web “apps”.

With the introduction of Access 2013, Microsoft has made substantial changes to its vision of how to put Access database applications on the web. The differences between Access 2010 web databases and Access 2013 web apps are major, and you should consider them carefully before embarking on an Access web project.

Before I go further, I should point out a relatively minor terminology change.  Microsoft refers to 2010 web projects as “web databases”, while it refers to 2013 web projects as “web apps”.  I don’t think this will help much to differentiate the two products, but it’s at least worth noting. I’m going to continue prefacing the terms with the Access version, just to be clear.

So what are the differences, and why are they important? 

Differences

The most important difference is where and how the data is stored.  Both 2010 web databases and 2013 web apps require SharePoint (although different versions)  However, 2010 stores the tables and application in SharePoint lists, while 2013 stores them in SQL Server tables.  The minimum SQL Server version for working with Access 2013 web apps is SQL Server 2012. No earlier versions of SQL Server will work in this setup.

Secondly, both require SharePoint Access Services, but 2010 uses Access Services 2010 while 2013 requires Access Services 2013.  These services are very different and mutually incompatible. However, you can have both services running on the same SharePoint site, so you can host both access 2010 web databases and 2013 web apps as long as you have both services running.

Importance

This incompatibility means that 2010 web databases and 2013 web apps are entirely incompatible.  It that if you create a 2010 web database there is no upgrade path to 2013.  You cannot convert a 2010 web database to a 2013 web app.  It will have to be re-created from scratch.  You will still be able to maintain it in 2013, but you cannot create a new project.  There is no guarantee that future versions will even be able to do that.

On the other hand, you can easily migrate your data (structure and data) from a 2010 web database to a 2013 web app, but the application (forms, etc) will have to be rebuilt from scratch.

Recommendations

There are, of course, many other differences between Access 2010 and Access 2013, just as there are between any two versions of a product.  But in terms of deciding which is right for you, these are the most important.

If you already have an Access 2010 web database, there’s no need to immediately redevelop it in 2013.  But if you’re planning on using it in the long term, you should begin making plans to move to 2013.  All of Microsoft’s future development will be in the 2013 web app arena and future versions of Access may not even support 2010 web databases.

If you are starting a new project, there is no question you should be using Access 2013 web apps.

Thursday, June 20, 2013

You CAN use quotes as a text value delimiter in T-SQL

SET QUOTED_IDENTIFIER OFF

Even though I said in a recent post (Access SQL Delimiters vs. T-SQL Delimiters), that T-SQL uses only the apostrophe (') as a text delimiter, that's not entirely true. It IS possible to use the quote (") as a text delimiter, just like Access. To do this, you must set a T-SQL option called QUOTED_IDENTIFIER to OFF. This must be done at the beginning of the pass-through query:

SET QUOTED_IDENTIFIER OFF

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField="Roger Carlson";

SET QUOTED_IDENTIFIER ON

Because T-SQL (and therefore pass-though queries) allow multiple statements to execute, you can turn the option off, and then back on again in the same query.

Please note that I DID set the option back on at the conclusion of the query. That's because it will set it OFF for every pass-though query in this Access session, and you may not want that. It is always best to set an option back the way it was to avoid confusion.

This can be useful when you are converting an Access SQL statement (that has a lot of delimited text values) to T-SQL.

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField IN ("Roger Carlson", "Sue Carlson", "Bob O'Brien");

Instead of replacing the quotes with apostrophes (remembering that Access Query Builder does not have a find and replace feature), you can simply bracket it with the QUOTED_IDENTIFIER option.

SET QUOTED_IDENTIFIER OFF

SELECT MyTextField, MyDateField, MyIntField
FROM dbo.MyTable
WHERE MyTextField IN ("Roger Carlson", "Sue Carlson", "Bob O'Brien");

SET QUOTED_IDENTIFIER ON