Thursday, October 30, 2008
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
Tuesday, October 28, 2008
Suppose you have a macro (mcrImportFiles) that imports data and you want to schedule it for the middle of the night each night -- say 1 am. How would you do it?
First of all, to do this, you need to leave your Access database open all the time (or at least start it every night) because it must be open to work.
Next, you need to create a form that will stay open all the time. Have this form open automatically at Start Up (see How Do I Configure My Database Start Up? ). It can be hidden if you want. (see How Do I Hide a Form But Leave It Running? ).
Every form has at Timer Property and an OnTimer event. Set the Timer property to 60000 (60 seconds). This will cue the form to run the OnTimer event once each minute.
Now, add some code to the OnTimer event. Something like this:
Private Sub Form_Timer()
If Time() >= #1:00:00 AM# And Time() < #1:01:00 AM# Then DoCmd.RunMacro mcrImportFiles End If End Sub This will run the macro each day at 1 am. To make this process even more robust, convert the macro to VBA code (see How Do I Convert A Macro to VBA Code? ) and insert it directly into the OnTimer event. This way, you can trap for any errors and handle them gracefully instead of simply letting your application hang as it would with a macro.
Monday, October 20, 2008
Author: A. D. Tejpal
SQL strings having embedded form based parameters do not readily lend themselves to creation of recordsets (Error 3061 - Too Few Parameters). Conventional approach involves concatenation of hard values represented by form controls into the SQL string.
Concatenation of hard values into SQL string suffers from the following drawbacks:
- (a) Proper delimiters need to be used for non-numeric data (Single quote for text type data and hash (#) for date type data).
- (b) If text data has embedded quotes, these need to be fixed before concatenation.
- (c) For date type data, the hard value has to be converted into "mm/dd/yyyy" format before undertaking the concatenation. This is meant to prevent inconsistent results, if the regional settings for short date on the local computer happens to be not as per U.S. date format.
- (d) Care needed in handling Null values.
The limitations brought out above, point to the desirability of devising a workable solution for utilizing SQL strings having embedded form parameters, as source for recordsets. Function Fn_FixFormParamsForRecordsets() is meant to fix such SQL strings, making them suitable for use with recordsets. This eliminates the problems outlined in preceding para.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=437
Tuesday, October 14, 2008
Author: A. D. Tejpal
The ModulesAddIn_BulkImportFromFiles for MS Access is a utility that enables bulk import of .bas or .txt files as general modules.
You can find it here:
Saturday, October 11, 2008
Author: A.D. Tejpal
Subqueries (or user defined functions based upon recordsets) for calculation and display of row numbers and running sum, are found to be slow in case of large data sets.
Use of increments to global variables offers a faster alternative. This method is preferably used through an action query, as calculated values directly displayed via select query tend to be volatile (the results keep on changing as one navigates up and down the records.
Two alternative methods based upon incrementing global variables are demonstrated as follows. In each case, two styles are covered, namely (a) Straight simple sequence and (b) Group-wise sequence. If it is simple sequence, the user defined functions have provision for specifying the starting number or sum - if desired:
1 - Row number and running sum - via update action.
2 - Row number and running sum - via make table action.
For academic interest, depiction of row numbers through generation of autonumbers has also been demonstrated as follows (this approach is however not as fast as that involving increments to global variables):
3 - Row number - via append action..
4 - Row number - via make table action.
(a) Use of incrementing global variables (items 1 and 2 above), apart from being the fastest approach, has the added benefit that it is not dependent upon availability of primary key.
(b) Four user defined functions utilized in implementing this method are given in the general module.
(c) Use of these functions in WHERE clause of pertinent queries is meant to initiate the sequence in desired manner.
(d) If filter has been applied to displayed data, generation of row number and running sum gets implemented on the filtered data, as demonstrated in the sample db.
You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=433&PID=429#429
Thursday, October 9, 2008
- How do I Create an Application in Microsoft Access?
- How Do I Convert A Macro to VBA Code?
- How Do I Configure My Access Database Start Up?
- How Do I Bypass Start Up Options?
- How do I run a macro or code when the database first starts?
- Can I Create an EXE from my Access Application?
- How Do I Decompile a Database?
- How do I add a value to a combo box with Not In List?
- What To Do When You Take Over A Database Application
- What Does It Mean to Compact My Access Database?
- How Do I Compact an Access Database?
- Should I use the Compact On Close feature of Access?
- How Can I Compact the Current Access Database in Code?
- How Can I Compact my Access Database Less Often?
- Help! My Database is Corrupted, and I Can't Repair!
Domain Functions Demystified
- Domain Functions Demystified: Introduction
- Domain Functions Demystified: Criteria Expressions
- Domain Function Example: Simulate AutoNumber with DMax
- Domain Function Example: Numbered Query With DCount
- Domain Function Example: Running Sum with DSum
- Domain Function Example: "Difference Between" in Query
- Domain Function Example: Rolling Average in Query
- Domain Function Example: Begin Date and End Date from Effective Date
- How do I calculate a Median in Access? Part1
- How do I calculate a Median in Access? Part 2
- Date Functions
- Date Stuff–Using the Functions
- Date Stuff – Useful Examples
- Now() Function in Default Value Stores the Wrong Time
- Really Bad Design Decisions: A Case Study
- What is a Primary Key?
- What is an Index?
- What does NULL mean? How is it different than the Empty String?
- Normalizing City, State, and Zip
What is Normalization?
- What Is Normalization, Part I: Why Normalization?
- What Is Normalization, Part II: Break it up.
- What Is Normalization: Part III: Putting It Back Together
- What is Normalization: Part IV: More Relationships
- What Is Normalization: Part V: Many-to-Many Relationships
- Entity-Relationship Diagramming: Part I
- Entity-Relationship Diagramming: Part II
- Entity-Relationship Diagramming: Part III
- Entity-Relationship Diagramming: Part IV
The Normal Forms
- The Normal Forms: Introduction
- The Normal Forms: First Normal Form (1NF)
- The Normal Forms: Second Normal Form (2NF)
- The Normal Forms: Third Normal Form (3NF)
- The Normal Forms: In a Nutshell
What’s Wrong With Repeated Columns?
- The Problem of Repeated Columns
- Querying Repeated Columns: Multiple ORs
- Querying Repeated Columns: Multiple Unions
- Querying Repeated Columns: Multiple Joins
- Querying Repeated Columns: Multiple IIFs
- Querying Repeated Columns: Impossible Joins
- Aggregating Across Repeated Columns: Summing
- Aggregating Across Repeated Columns: Counting
- Aggregating Across Repeated Columns: Averaging
Normalizing Repeating Columns
- Normalizing Single Repeated Column (Part1)
- Normalizing Multiple Columns (Part1)
- Normalizing Yes/No Fields (Part1)
- Normalizing Repeated Columns With VBA
- What Is A Query?
- This Recordset is Not Updateable. Why?
- Searching for a Wildcard in a “LIKE” Criteria (Quick Tip)
Select Queries Series:
- Part 1: Simple Queries
- Part 2: Restricting Rows - the Where Clause
- Part 3: Sorting and Grouping (ORDER BY, GROUP BY)
- Part 4: PARAMETERS, TOP, DISTINCT, and TRANSFORM...PIVOT
- Union Query Part 1: Simple Union Query
- Union Query Part 2: More Union Queries
- What is a Join: Part 1 (Introduction)
- What is a JOIN: Part 2 (Inner Join)
- What Is A Join: Part 3 (Cartesian Joins)
- What is a Join: Part 4 (Equi-Joins in the WHERE Clause)
- What Is A Join Part 5: Outer Joins
- Why Do I Get the "Ambiguous Outer Join" Error
- Delete Query
- Update Query
- Append Query
- Make-Table Query
- Featured Sample: Action Queries In Code
- Featured Sample: UnboundSQL.mdb
Data Definition Language (DDL) Queries:
Top Queries Revealed:
- Simple Top Query
- Aggregate Values and Top Values By Group.
- Top Query Problem - Ties
- Top Query to find Random Records
- Parameters in Top Queries
Count Distinct In Access Series:
- COUNT DISTINCT in Access: Part 1
- COUNT DISTINCT In Access: Part 2
- COUNT DISTINCT In Access: Part 3
- COUNT DISTINCT In Access: Part 4
- COUNT DISTINCT in Access: Part 5
- What is the fastest way to return the Record Count of a table?
- What is the fastest way to return the Record Count from an Access Query?
- What is the difference between HAVING and WHERE in Aggregate Queries?
Showing Query Parameters in a Report (Quick Tip)
- Bang Vs. Dot In DAO
- Bang Vs. Dot in Forms
- Referencing Forms, Subforms, and Sub-subforms
- Data Definition Language (DDL): DDL Using DAO
- How Do I Find The Path and Filename For My Database?
- How Do I Replace System Error Messages With Custom Messages?
- How Do I Convert A Macro to VBA Code?
- How Do I Hide a Form But Leave It Running?
- How Do I Run A Process Automatically Once A Day? Part 1
- How Do I Run A Process Automatically Once A Day? Part 2
- What’s the Difference Between Early Binding and Late Binding?
- How do I calculate a Median in Access? Part1
- The Data
- How Can I Put My Access Database On The Web?
- Web Compatibility
- How can I create a Compound Unique Index?
- Creating Relationships
- Uploading to SharePoint
- Viewing Tables In SharePoint
- Other Compatibility Errors
- The Application
Tuesday, October 7, 2008
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:
If Null = Null Then
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.
If IsNull(Null) Then
This evaluates to True because the IsNull() is specifically designed to test for NULL.
If "" = "" Then
This evaluates to True because the empty string is a known value.
If IsNull("") Then
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.
Monday, October 6, 2008
Author: Peter Hibbs
This demo program shows how to display a Crosstab query in a Flex Grid control on a form. The problem with displaying Crosstab queries on a form (say in a ListBox control) is that some queries have a variable number of columns as well as rows. Using a Flex Grid control allows you to set the number of columns at run time as well as giving all the other facilities that you get with Flex Grid controls such as colouring cells, fixing columns or rows, etc, etc.
See Peter's other demo for more information on Flex Grid commands.
This demo works with Access 2000 onwards.
Find this sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=429
Friday, October 3, 2008
"Please keep Access out of this. Native via SQL tools only. No pass through. Sorry, but it's icky and gross in this case."
Of course, this is simply prejudice and ignorance on his part. Properly created, an Access application works perfectly well against a SQL Server back-end database. And running an Access Pass-through query is no different than executing a query in SSMS or the older Query Analyzer. Microsoft spent quite a lot of time and effort to make sure this is true.
But, I can't honestly say I'm all that surprised. I've encountered this attitude before, often from Oracle DBAs, but surprisingly also from SQL Server DBAs. And this got me to wondering where this antipathy comes from.
I think it comes from two sources.
First of all, creating an application for SQL Server in the same way you create one for Jet, can indeed get you into trouble. Filling a bound form with an entire dataset and filtering the result (as less experienced Access developers tend to do), can lead to very slow performance. In a multi-user environment, bound forms can place a burden on the database server by holding open too many connections and record locks.
Fortunately, there are methods around these and other problems. It has nothing to do with Access, but the way in which the application is created. As with anything, different problems require different solutions, and using the wrong solution causes problems.
But the second reason for this antipathy goes deeper than the technical reasons. I think there is a natural tendency for people who use a more powerful/complex product to dismiss those who use one that is easier to learn/use. C++ developers look down on VB developers who look down on Access developers.
This is unfortunate and really does nothing positive for anybody. It's time to stop sniping at each other and treat each other as professionals, each competent in their own areas.