New Sample: Query_A2K10_MultiValueFields

By AD Tejpal

    This sample db demonstrates query based approach to bulk appending / updating / make table actions involving multi-value  fields in Access 2010, without resorting to use of recordset or recordset2 objects, thus overcoming a known limitation associated with such fields.

    In this arrangement, an extra field named TempID is provided in the destination table. The append query is executed in two stages. In first stage, primary key values from source table are appended to TempID field in destination table. In 2nd stage, value elements of MVF field get appended, using an inner join between the source and destination tables (PK of source table equals TempID of destination table).

    Three styles are covered as follows:

•     (a) Bulk appending of selected records (having multi-value fields) from one table to another.
•     (b) Bulk updating of multi-value field elements (addition or replacement of values) in destination table based upon values held in source table.
•     (c) Make table action covering multi-value fields based upon selected records in source table.  

Version: Access 2010 accdb file.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic575_post593.html#593

Referencing Forms, Subforms, and Sub-subforms

 

Introduction

One of the powerful features of Microsoft Access is the ability to reference controls from other controls, in queries, and in code. And yet, while most beginning developers master referencing controls on forms relatively quickly, referencing controls on subforms and sub-subforms causes a lot of confusion. Most of this confusion is due to the way in which controls are named.

First, however, a word about naming conventions. I generally encourage the use of meaningful names for controls on a form. In this case, however, I am using generic names like Form1, Subform1, Subsubform1, subControl1, subsubControl1, Text1, and so forth. Objects that I create and name will have a number at the end (Text1). Form collections or properties (like Forms, Controls, Value, or SetFocus) will not have a number at the end.

 

Syntax

There are three different, yet equally correct, syntaxes. For instance, suppose I want to reference the value of a textbox named Text1 on a form named Form1. All of the following work:

Forms!Form1!Text1.Value
Forms("form1")("Text1").Value
Forms(0)(0).Value

 

Bang Syntax

The Bang (!) syntax is the most commonly used syntax in Access, and is generally recommended in Microsoft documentation and articles. The Bang Syntax uses both bangs (!) and dots(.). Bangs are used to separate levels in the DAO hierarchy and dots serve to separate an object from its properties.

So, in the case of our textbox, "Forms" is the Access collection of forms. "Form1" is the name of the form I'm referencing. And "Text1" is the name of the control (textbox).

Forms!Form1.Controls!Text1.Value

The bangs go between the object hierarchy levels. Form1 is a member of the Forms collection. Text1 is a member of the Controls collection of Form1. The dots separate the objects from their collections or properties: The Controls collection of the Form1 object, and the Value property of the Text1 object.

However, every object has a default property and it just happens that Controls is the default for a form and Value is the default for control objects. Therefore you don't actually have to reference them, so I can do something like this:

Forms!Form1!Text1

But if I wanted to reference the caption of the textbox, I'd have to explicitly name it.

Forms!Form1!Text1.Caption

As a general rule of thumb, Bangs (!) go before objects that you, the developer, name like forms, controls, and such. Dots (.) go before things that Access names, like properties and methods. For more on the difference between Bang and Dot, see my post: Bang Vs. Dot In DAO.

It is worthwhile to note that there are times when Bangs and Dots are interchangeable and at other times, it is not.  For instance, in:

Forms!Form1!Text1
Forms.Form1!Text1

the Bang between "Forms" and "Form1" can be replaced with a Dot.  However, the Bang between "Form1" and "Text1" can not.  Best practice is to use the Bangs and Dots correctly to avoid any problems.

 

Paren Syntax

The Paren Syntax simply surrounds each object with parentheses and the name of the object is surrounded in quote marks without bangs or dots:

Forms("form1")("Text1").value

This syntax is most useful in programming where you want to use a variable in place of the object name:

Dim strControlName as String
Dim strFormName as String
strFormName = "Form1"
strControlName = "Text1"
Forms(strFormName)(strControlName).value = "Hello"

Notice the variables are NOT surrounded by quotes.

 

Index Syntax

The Index Syntax is rarely used. It is most commonly used for programmatically looping through controls on a form. For instance, if I wanted to print out the names of all the controls on a form, I could do something like this:

Dim i As Integer
For i = 0 To Forms(0).Controls.Count – 1
   Debug.Print Forms(0)(i).Name
Next i

I could list the names of all the controls on all open forms like this:

Dim i As Integer
Dim j As Integer
For i = 0 To Forms.Count – 1
   For j = 0 To Forms(i).Controls.Count – 1
      Debug.Print Forms(i).Name & ": " & Forms(i)(j).Name
   Next j
Next i

The reality is, however, that there are better ways to do this by directly accessing the Forms Collection and the Controls Collection. Still, it's useful to know about because it can be used in conjunction with other syntax.

 

Mixing and Matching Syntaxes

The various syntaxes can be mixed and matched. For instance, all of the following work identically:

Forms!Form1!Text1
Forms!Form1("Text1")
Forms!Form1(0)
Forms.Form1!Text1
Forms("Form1")!Text1
Forms!Form1.Controls(0).Value
Forms(0)(0).Value
Forms(0)("Text1").Value
Forms!Form1.Controls!Text1.Value

In general, however, unless you have a good reason for it, it's best to stick with the Bang and Dot syntax.

 

A Word About ME

The ME object is a special construct used in forms and reports. It refers to the form or report which has the Focus. If you are referencing objects from within the form/report, you can substitute Me. or Me!

for Forms!Form1.

Me!Text1

Me.Text1
Me!Text1.value
Me.Text1.value
Me.Controls!Text1
Me(Controls)("Text1").value

It doesn't matter which.

If, however, you are referencing the control from outside the form/report -- say in a query -- then you must use explicitly reference the Forms collection and the Form name. That is: Forms!Form1.

For more on the ME object and using the Bang and the Dot in forms, see my blog post: Bang Vs. Dot in Forms.

To see specific examples of referencing subforms and subsubforms, download my sample: SubformReference.mdb from my website (www.rogersaccesslibrary.com).

Featured Sample: Training Registration Database

By Roger Carlson

This database is a fairly robust application for registering employees for training classes.  In it, you can create Courses, create Students, create Classrooms, schedule Courses to Classrooms, and assign Students to Scheduled Courses.   Your first stop will be the Main Menu, where you will have the following choices:

Schedule Classes

Clicking "Scheduled Classes" from the Main Menu brings you to the Scheduled Classes screen. It gives you a summary of the classes already scheduled.

From there,  select an Application and a Course Name to see a listing of all the scheduled classes.  You can filter the selection further by choosing the Past Classes or Future Classes buttons.  You can also schedule a new class with the New Class button.

To view or edit a particular class, click the GO> button to be sent to the Scheduled Class Entry screen.

The Scheduled Class Entry screen allows you to view, edit, or a class, and assign students to it.  By default, the fields in BLUE are locked to prevent accidental changes.  To unlock these fields, click the Unlock button.

Below the Class Information, you can assign students to the class.  The student fields are NOT editable (even if you unlock the other fields).  The only editable field is the Attended field to indicate the student attended the class. Even though you can't edit the information in the student fields, you can re-size and re-arraign the fields at run-time.

You can print a Class Roster from this screen and also certificates of attendance.  The certificates are bare-bones versions that you will want to modify for your company.

As you enroll students, the screen will track the number of seats taken and the number remaining.  It will allow you to over-book a class, but will give you a warning when you do.

By default, the Scheduled Class Entry screen shows just the records you selected on the Scheduled Classes screen.  Clicking the Show All Records button (bottom) will fill the form with all the classes for that Application.  You can use the Record Selector buttons to scroll between them.

Schedule Students

The Schedule Students screen allows you to enter students and assign classes in one step. 

The screen opens to the New Record all set for you to enter a new student.  If you want to edit a student, you can select one from the drop down box at the top of the screen.  You can also use the scroll buttons at the very bottom of the screen.  When you enter an existing student, the fields turn BLUE, which indicates they are locked.  This is to prevent accidental changes.  Click the Edit Record button to unlock the fields.

You can also assign classes to students in this form.  Just select the class from the drop down list.  The particulars of the class will appear below in the blue fields.  Thes cannot be edited in this form.  To do so, you must go to the Scheduled Classes screen.

Reports

The Reports screen allows you to run various reports.  Each report appears as an item in a listbox.  Select the item and click "Open".

There are two types of reports: 1) Simple Reports and 2) Parameter Reports

Simple reports show the same information all the time.  The "All Courses" report is an example.  It displays all of the current courses offered.

Parameter reports are a little more useful in that the user can select options from a drop down box to limit the output.  The "Classes By Instructor" report is an example.  When you run the report, a small form appears that allows you to choose an instructor.

Simple Reports:  To add a simple report to the Reports List, simply name the report with an "rpt" prefix.  By using a prefix, you can prevent reports (like the "ClassCertificate" report) from showing up in the list.  (ClassCertificate runs from the "Scheduled Class Entry" screen only.)

Parameter Reports:  To add a parameter report to the Reports List is a little more complex.  First of all, you have to create a form which has controls to hold the parameters and reference those controls in the Report Source query.  Next, name the form with an "rpt" prefix.  Lastly, you'll want to name the report itself with an "rpf" prefix.  This will prevent these reports from appearing in the Reports List.

Example:  The form "rptClasses By Instructor" is the form which will run the "rpfClasses by Instuctor" report.  The Record Source for the report (a saved query called: Scheduled Classes Query by Instructor) references the combo box value on the form to restrict the values in the report.

Maintain Information

The Maintain Information screen allows you to edit your reference tables.  In it, you can add or edit Applications, Buildings, Classrooms, Courses, and so forth.  These options will then be available to the drop down lists in the data entry screens.

If you add new reference tables, you'll also want to create a simple edit form for the table.  If you name the form with a prefix of "Maintain ", it will automatically appear in the Table Mainenance list.

The Getting Started screen is a little different from the rest as it allows you to turn the Getting Started screen on and off.

You can find this sample here: http://www.rogersaccesslibrary.com/forum/topic232.html.

How do I calculate a Median in Access? Part 2

The DMedian() Function:

In my last post, (How do I calculate a Median in Access? Part 1) I defined Median and described in general terms how to calculate a median in Access. This time, I'm going to describe in detail the DMedian() user-defined function.

First of all, you have to create a Public Function in a General Module. Create a new module by selecting Module in the Database Window and click the New button. Immediately click the Save button and name the module "basDMedian". Next, type the following function into the module.

Public Function DMedian(FieldName As String, _
      TableName As String, _
      Optional Criteria As Variant) As Variant

' Created by Roger J. Carlson
' http://www.rogersaccesslibrary.com
' Terms of use: You may use this function in any application, but
' it must include this notice.

'Returns the median of a given field in a given table.
'Returns -1 if no recordset is created

' You use this function much like the built-in Domain functions
' (DLookUp, DMax, and so on). That is, you must provide the
' 1) field name, 2) table name, and 3) a 'Where' Criteria.
' When used in an aggregate query, you MUST add each field
' in the GROUP BY clause into the into the Where Criteria
' of this function.

' See Help for more on Domain Aggregate functions.

On Error GoTo Err_Median

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim RowCount As Long
    Dim LowMedian As Double, HighMedian As Double

    'Open a recordset on the table.
    Set db = CurrentDb
    strSQL = "SELECT " & FieldName & " FROM " & TableName
    If Not IsMissing(Criteria) Then
        strSQL = strSQL & " WHERE " & Criteria & " ORDER BY " & FieldName
    Else
        strSQL = strSQL & " ORDER BY " & FieldName
    End If
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    'Find the number of rows in the table.
    rs.MoveLast
    RowCount = rs.RecordCount
    rs.MoveFirst
   
    'Determine Even or Odd
    If RowCount Mod 2 = 0 Then
        'There is an even number of records. Determine the low and high
        'values in the middle and average them.
        rs.Move Int(RowCount / 2) - 1
        LowMedian = rs(FieldName)
        rs.Move 1
        HighMedian = rs(FieldName)
        'Return Median
        DMedian = (LowMedian + HighMedian) / 2
    Else
        'There is an odd number of records. Return the value exactly in
        'the middle.
        rs.Move Int(RowCount / 2)
        'Return Median
        DMedian = rs(FieldName)
    End If

Exit_Median:
    'close recordset
    rs.Close
    Exit Function

Err_Median:
    If Err.Number = 3075 Then
        DMedian = 0
        Resume Exit_Median
    ElseIf Err.Number = 3021 Then
        'EOF or BOF ie no recordset created
        DMedian = -1
        Resume Exit_Median
    Else
        MsgBox Err.Description
        Resume Exit_Median
    End If
End Function

To use this function in a query, simply use the query above that calculates the average with the DAvg, and replace DAvg with DMedian, like this:

SELECT Hospital, DMedian("TimeToProc", "tblTimeToProcedure",
    "[Hospital] = '" & Hospital &"'") AS MedianTimeToProc
FROM tblTimeToProcedure
GROUP BY Hospital

Which will return this:

Hospital	MedianTimeToProc
A	2.60
B	3.5

Caveats:

You have to be careful, however, because it is very easy to produce data that looks correct, but is not. If you're not careful to include every field contained in the Where clause and Group By clause of your main query in the Criteria of the DMedian function, your data will not be correct.

Let's look at a slightly more complex example. Suppose we want to aggregate our TimeToProc data at the Hospital and Unit level. We might have a table that looks like this:

tblTimeToProcedure

PatientID	Hospital	Unit	TimeToProc
1	A	C1	1.5
2	A	C2	2
3	A	C1	2.2
4	A	C2	3
5	A	C1	2
6	A	C1	4
7	A	C2	5
8	A	C2	100
9	B	A1	2.5
10	B	A1	2
11	B	A1	4.2
12	B	A2	3
13	B	A2	2.2
14	B	A2	4
15	B	A1	5
16	B	A1	6

Suppose I create a totals query in Access that aggregates the data on Hospital and Unit, but only provide the Hospital to the DMedian function's criteria. Like this:

SELECT Hospital, Unit, DMedian("TimeToProc","tblTimeToProcedure",
     "[Hospital] = '" & [Hospital] & "'") AS MedianTimeToProc
FROM tblTimeToProcedure
GROUP BY Hospital, Unit;

I'll get a result that looks like this:

Hospital	Unit	MedianTimeToProc
A	C1	2.60
A	C2	2.60
B	A1	3.5
B	A2	3.5

This is obviously wrong. The DMedian function is aggregating ONLY at the Hospital level, while the main query is aggregating at both Hospital and Unit.

In order to produce the correct median values, we have to supply both the Hospital and the Unit to the DMedian function.

DMedian("TimeToProc", "tblTimeToProcedure", "[Hospital] = '" & Hospital &"' AND [Unit] = '" & Unit & "'")

The finished query will look like this:

SELECT Hospital, Unit, DMedian("TimeToProc", "tblTimeToProcedure",
     "[Hospital] = '" & Hospital &"'AND [Unit] = '" & Unit & "'") AS MedianTimeToProc
FROM tblTimeToProcedure
GROUP BY Hospital, Unit;

This will produce the correct results:

Hospital	Unit	MedianTimeToProc
A	C1	2.10
A	C2	4
B	A1	4.2
B	A2	3

So the rule is that every field that participates in the Where clause or Group By clause of the main aggregate query must also be represented in the Criteria argument of the domain aggregate function.

Making Sure Your Query is Correct

One way to make sure you are aggregating the data correctly in both the main query and the domain aggregate function is to create an aggregate query using the SQL AVG function, and then create the same query using the DAvg domain aggregate function. If the two queries produce the same values, you know you have created it correctly. Then just replace DAvg with your DMedian function.

For instance, first create an AVERAGE aggregate query using the SQL AVG function. The SQL statement would look like this:

SELECT Hospital, Unit, AVG(TimeToProc) AS AvgerageTimeToProc
FROM tblTimeToProcedure
GROUP BY Hospital, Unit

Hospital	Unit	AverageTimeToProc
A	C1	2.43
A	C2	27.50
B	A1	3.94
B	A2	3.07

Now create the same query, but this time use the DAvg function.

SELECT Hospital, Unit, DAvg("TimeToProc", "tblTimeToProcedure",
    "[Hospital] = '" & Hospital &"' AND [Unit] = '" & Unit & "'") AS
    AverageTimeToProc
FROM tblTimeToProcedure
GROUP BY Hospital, Unit;

Hospital	Unit	AverageTimeToProc
A	C1	2.43
A	C2	27.50
B	A1	3.94
B	A2	3.07

Since the two queries produce identical results, we know that we have set up the domain function correctly. Now just replace DAvg with your DMedian function:

SELECT Hospital, Unit, DMedian("TimeToProc", "tblTimeToProcedure",
     "[Hospital] = '" & Hospital &"' AND [Unit] = '" & Unit & "'") AS
     MedianTimeToProc
FROM tblTimeToProcedure
GROUP BY Hospital, Unit;

Hospital	Unit	MedianTimeToProc
A	C1	2.10
A	C2	4
B	A1	4.2
B	A2	3

On-line Database Sample:

On my website, (http://www.rogersaccesslibrary.com), there is a small sample database called Median.mdb. This sample has the above example and another, more complex, example in an Access database, so you can see how the DMedian function works.

There are two versions of the DMedian function. DMedian is in the basMedianADO module, and DMedian is in the basMedianDAO. You must use the DMedian97 in an Access 97 database.

Conclusion:

Because Microsoft Access^® does not have a Median aggregate function, finding the median value of a group of data can be a challenge. But with a little programming expertise, you can create your own DMedian domain-aggregate function that you can use to find the Median value for an aggregate query.

How do I calculate a Median in Access?

Users of Microsoft Excel^® are familiar with the MEDIAN function. Select a range of cells, push the function button, select MEDIAN under statistical functions, and you have your median value. However, no such function exists in Microsoft Access^®. So if you need to figure a median in Access, what do you do? One solution is to create your own Median domain aggregate function or DMedian().

But to understand how to program a median function, you must first understand how a median is calculated.

Calculating a Median:

Almost everyone knows what an average is. Add up all the values in your list and divide that by the number of values. In general, it looks like this: (a₁+a₂+...+a_n)/n. In most cases, this gives a pretty good aggregate picture of your data. However, if you have values that fall way out of the general range of the others, it can give you a skewed picture.

This is where the median becomes important. A median works differently. To find the median, you sort your list (ascending or descending, makes no difference), count the number of values in your list, and then divide that number by two. If the list has an odd number of values, the result will be a fraction (n.5). Take the integer portion of the resultant (n) and go to the (n+1)th value in your sorted list. That number becomes the median. If the list has an even number of values, the result will be a whole number (n). To figure a median with an even number of values, you take the integer portion of the resultant, (n) and average the nth and (n+1)th values.

Example - Odd number of values:

1.5

2

2.2

3

2

4

5

Average: (1.5+2+2.2+3+2+4+5)/7 = 2.814286.

Median: First, order the list (1.5, 2, 2, 2.2, 3, 4, 5). Counting the number of values returns 7. Divide 7 by 2 and you get 3.5. Taking the integer portion of 3.5 yields 3. Go to the (3+1)th (or 4th) value in the ordered list and you have your median of 2.2.

Example - Even number of values:

1.5
2
2.2
3
2
4
5
1

Average: (1.5+2+2.2+3+2+4+5+1)/8 = 2.5875.

Median: Again, order the list (1, 1.5, 2, 2, 2.2, 3, 4, 5). There are 8 values in the list. Dividing 8 by 2 equals 4. Select the 4th and (4+1)th (that is 5th) value in the ordered list results in 2 and 2.2. Averaging these numbers results in your median value of 2.1.

As you can see, in both cases, the values of the average and median are fairly close. In the Odd case: 2.814286 vs. 2.2. In the Even case: 2.5875 vs. 2.1. But what if we had an outlier; say 100 (in place of 5), in the list?

1.5
2
2.2
3
2
4
100
1

The median stays at 2.1, but the average jumps to 14.4625. Not very representative of your data. So there are times when it is statistically more accurate to use the median rather than the average.

Calculating the Median in Access

But the question remains: how do you create a median in Microsoft Access? One answer is to create your own domain aggregate function: DMedian().

So what are domain aggregate functions? Access has a number of built-in domain aggregate functions that work similarly to the SQL aggregate functions: MAX, MIN, SUM, COUNT, AVG (and others). They are DMax(), DMin, DSum, DCount, and DAvg. These functions act like mini totals (aggregate) queries that return a single value. You can use these functions as the source for a control on a form or report, or you can use them in a query. For more information on Domain functions, see my blog series: Domain Functions Demystified.

There are three arguments you must provide to a domain function. Two are required: Fieldname, Tablename, and the third is optional: a Criteria (essentially a Where clause without the word WHERE). The domain function will then calculate the desired function (min, max, etc.) for that Field, against that Table, filtering it on the Criteria. In general: Dfunction("fieldname", "tablename", "criteria").

Before we look at creating our own DMedian() function, let's look at how the DAvg() function works.

Suppose we have the following table:

tblTimeToProcedure

PatientID	Hospital	TimeToProc
1	A	1.5
2	A	2
3	A	2.2
4	A	3
5	A	2
6	A	4
7	A	5
8	A	100
9	B	2.5
10	B	2
11	B	4.2
12	B	3
13	B	2.2
14	B	4
15	B	5
16	B	6

(TimeToProc is the number of hours between patient admission and the time a give procedure was administered.)

To calculate the average TimeToProc for the entire table with the DAvg function, you would provide the Field and Table without a Criteria. Like this:

DAvg("TimeToProc", "tblTimeToProcedure")

In this case, no Criteria required because we don't want to filter the data. However, suppose we wanted just the average of Hospital A? In that case, we would do this:

DAvg("TimeToProc", "tblTimeToProcedure", "[Hospital]='A'")

(Because Hospital is text data, you have to provide quotes (or single quotes) around it. Numeric data does not require delimiters. For more information on this, look in the Access Help at the DMin, DMax aggregate functions. Type DMin in the keywords.)

You can also use the DAvg function in a Totals Query in Access to return the average for each hospital. Like this:

SELECT Hospital, DAvg("TimeToProc", "tblTimeToProcedure",
"[Hospital] = '" & Hospital &"'") AS AverageTimeToProc
FROM tblTimeToProcedure
GROUP BY Hospital

Which will return this:

Hospital	AverageTimeToProc
A	14.9625
B	3.6125

The obvious question, of course, is why not just use the SQL aggregate function (AVG) to do the same thing:

SELECT Hospital, AVG(Hospital) AS AverageTimeToProc
FROM tblTimeToProcedure
GROUP BY Hospital

That's a good question, and in fact, it would be better to us the SQL function. But what if Access didn't have an AVG function? This is the case with the MEDIAN. No such function exists, so we have to create our own DMedian function to replace it.

Next time, I'll look at the details of the DMedian() function.

New Sample: Form_ContinuousSimulated

By A. D. Tejpal

This sample db demonstrates a simulated continuous form having unbound controls, facilitating row-wise display of unlimited colors.


It displays 12 records at a time. Full coverage of data is provided via suitable navigation buttons. Apart from being able to edit the records, the user can add new records or delete the current record by clicking appropriate command buttons. In addition, two alternative modes for search have been provided: (a) By record number or (b) By ID number. All these actions are feasible directly on the form. Current record remains identified by special highlight in first column.

First two columns (locked) display the record number and TrainID. Next two columns, holding TrainCode and ColorValue, are editable. Last column serves as ColorStrip, each row displaying the color represented by ColorValue in the previous column.

There is two way synchronization between ColorValue and ColorStrip. Any value entered in the former gets reflected as corresponding color in the latter. On the other hand, double click on ColorStrip invokes the color dialog box, where the user has unlimited choice and the color finally selected gets displayed in the color strip. Simultaneously, appropriate value gets assigned in ColorValue column.

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/topic574_post592.html

What To Do When You Take Over A Database Application

Taking over an existing database project is much like designing one from scratch, except some of the work is done for you. Unfortunately, some of that work may be wrong, so you can't necessarily rely on it.

First of all, are there relationships set up in the Relationship Window? Hopefully there are.  This will give you a map to see how the tables are related.

Write all the relationships in pairs of sentences: See Entity Relationship Diagramming for more information.

1:M
Each Customer can have one or more Orders.
Each Order can be for one and only one Customer.

M:M
Each Student can be in one or more Classes
Each Class can have one or more Students

Ask yourself (or your customer) if each and every one of these sentences is true. I will often go through this process with the customer. As they learn the database structure in this non-technical way, they will begin to spot relationship errors on their own.

Don't be constrained by the existing product. It's quite possible that the original database design is wrong. Don't assume the previous developer knew what he or she was doing. Even if he did, chances are good that if it's been in production for more than a couple of years, the business rules have changed. This is where the customer having a grasp of the database design process can be very useful.

If there are no relationships, you will have to infer these relationships based on the queries.  As you discover table relationships, you should create them in the Relationships Window.  If they're not there already, it is doubtful that you will be able to turn Referential Integrity ON, but at the very least, you will begin documenting the relationships.

Next, I would start with the reports.  Open a report and check the RecordSource property see which query it uses.  Then open that query, see what it is based on and so forth.  When I have done this, I've used a separate piece of paper for each report and query tree.  Then I will actually draw out the tree structure.  I'll also create a list of all the queries and underneath each, list the queries or objects (form or reports) that it directly applies to.  This can help if a single query is a base for many others.  You may be able to find a tool to do this for you, but the discovery process is very educational.

Do the same with the forms.

Next, look through the Code Modules (including the code behind forms and reports).  Often code is used to create queries or uses saved queries to create recordsets. 

If DAO is used to CREATE the query, it might look something like this:   

strSQL = "SELECT * FROM MyTable"
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

or

Set qdf = db.CreateQueryDef("qryMyQuery", "SELECT * FROM MyTable")

The first method is preferable and it is worthwhile to convert any using the second method into the first. The reason for this is that you will also want to know what queries are being created in code. If you create the SQL as a separate string, you can use the Debug.Print line to have the code evaluate the SQL string and display the SQL code in the Immediate Window. Like this:

strSQL = "SELECT * FROM MyTable"
Debug.Print strSQL
Set qdf = db.CreateQueryDef("qryMyQuery", strSQL)

Complex SQL statements are often difficult to read in VBA code, so this is a useful debugging technique as well.

If a query is just being USED in code (not created in code) it might look like this:

Set qdf = db.QueryDefs("qryMyQuery")

or

Set rs= db.OpenRecordset("qryMyQuery")

or in ADO

rs.Open "qryMyQuery', cnn, adOpenKeyset, adLockOptimistic

There are a lot of variations on this.  However, the important thing is that the query (or table for that matter) is being used by the code.  Make a note of that as well.  You can use the Find and Replace feature to find each query name.  You can set it to search through all of the code modules.

Also, some Forms and Reports have SQL statements in their RecordSource properties, rather than saved queries.  You should also make a note of the elements that make up these.

Once you've done all the reports, forms, and code, see which queries are not accounted for.  Chances are they are not used anywhere.  Many mature database projects have unused objects lying around.  DO NOT delete these. Instead, I favor renaming them with an XXX prefix, which tells me this is OK to delete at some future time.  Sometimes I will also add the date to the name so I know how long it's been since I "deleted" it.  If you run into a problem, you can always name it back.

Lastly, I would try to make some kind of method out of all of this madness. Try to develop some sort of rational naming structure for your queries. There is no single correct way to do this.  Often if a series of queries is used ONLY by one report, I will name them after the report and indicate which level they are in.  However, for queries that are used as a base for multiple other queries, this doesn't work so well.  I've never come up with anything that worked in all cases.  And it doesn't matter.  Just try the best you can to do a rational job of renaming them.  CAUTION:  DO THIS ON PAPER FIRST!

Once you have renamed your queries on paper in some rational manner, get your hands on a renaming tool.  Rick Fisher has a good shareware add-in called "Find and Replace".  You can find it here: http://www.rickworld.com/download.html.  (It is worth registering for the extra features, though).  This product (and others like Speed Ferret) allow you to rename an object throughout the entire database including forms, queries, reports, macros, and code.

By the time you get done with all this, you will know this database like the back of your hand.

Report_SubRepSetMaxRowsPerPg

By AD Tejpal

This sample db demonstrates custom setting of maximum number of rows per page for one or more subreports.


Setting of forced page breaks in subreport's detail section, though effective, suffers from the drawback that the subreport control on parent report gets forced to full page height. As a result blank space on main report goes waste. Though not recommended, this method has been demonstrated as the last option, just for academic interest.

Note:

While using forced page break in subreport, it has to be ensured that CanGrow property of subreport control on parent report is set to Yes. Otherwise the subreport does not get displayed beyond first page. In fact what happens is that as a result of forced page break, the subreport control with CanGrow as Yes, expands suitably so as to span multiple pages as needed.

Following styles are demonstrated in the sample db:

1 - A1: Single SubReport Style 1:

The desired effect is achieved by conditional cancellation of detail format of subreport for records not falling in the target block determined by parent report's current record number. It is like obtaining a filtered output matching target block of sequential numbers. The number of pages on parent report is restricted to the minimum required for the subreport.

2 - A2: Single SubReport Style 2:

Similar to A1. However, the number of pages on parent report is not restricted. It can exceed the minimum required for the subreport.

3 - B1: Two SubReports Style 1:

Similar to A1 above, but with two subreports, each with its own setting for max rows per page.

4 - B2: Two SubReports Style 2:

Similar to A2 above, but with two subreports, each with its own setting for max rows per page.

5 - C: Single SubReport With Forced Page Break:

Not recommended. Included for academic interest only.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/report-subrepsetmaxrowsperpg_topic573.html

New Sample: Form_LastViewedAndCurRecToggle

By A.D. Tejpal

    This sample db demonstrates toggling between last viewed record and the current record on subform in datasheet view, via a command button on parent form.

    Navigation through subform records is conducted through a combo box on the parent form. Alternatively, the user can click on the desired record directly on the subform. The current record gets highlighted in light green while the last viewed record is highlighted in light grey.

    The user can also flag one or more records by double clicking any of the columns on desired record. Last column of records flagged in this manner gets highlighted in pink color. These flags remain in force for the current session of access, even if the form is closed and then re-opened. Repeat double click on a flagged record will remove the flag. To remove all flags, command button captioned "Clear All Flags" can be clicked.

Version: Access 2000 file format

You can find the sample here:
www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=572&PID=590#590

New Sample: Data Definition Language: SQL vs DAO

This sample (with full documentation) illustrates how to do a variety of DDL (Data Definition Language) operations using both SQL and DAO.

DDL operations are those that modify the database structure, ie. tables, fields, indexes, and relationships.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic570_post587.html

Data Definition Language (DDL): DAO

Data Definition Language (DDL)

Data Definition Language (DDL) is a programmatic way to create and modify the database structure, that is, objects like tables, indexes, and relationships.

Before Access came along, DDL statements were the only way to modify the database structure in most relational database management systems. Access introduced the graphical user interface (GUI) to do most DDL functions. Because the Access GUI is so easy to use, most Access users never have reason to use DDL statements. However, there are circumstances under which it is advantageous to use DDL statements.

I use them a lot for automating data import processes. I can create a temporary table, import data to the temp table, change data types, remove indexes from the permanent table, append data from the temp to the permanent table, then rebuild the indexes -- all automatically, all in SQL code.

But that's not the only use. You can also use DDL to set the seed and interval for an autonumber field, to remotely change the structure of a back-end database (useful for multi-user databases), or make any other change to a production database. In a web environment, it can be used to add databases to an active website.

In this three-part series, I'll be addressing both SQL and DAO.

• SQL vs. DAO
• DDL Using SQL
• DDL Using DAO (this post)
• You can download this whole series in a single document with sample database, here: http://www.rogersaccesslibrary.com/forum/topic570_post587.html

 

DDL Using DAO

DAO (Data Access Objects) works differently than SQL. DAO is an object model specific to Access (or rather for the MDB or ACCDB file types). One reason for using DAO over SQL is that you can set Access specific properties (like Default Value and Validation Rules) with it. Also, certain datatypes (like Hyperlink) can only be created with DAO.

In general, with DAO, you:

1. Declare object variables (e.g., Table, Field, Property, etc.)
2. Instantiate the object (that is, create the object)
3. Append it to the appropriate Collections (that is, a table to the Tables Collection)

You can't create a Table without creating at least one Field, so you have to create and append the fields for the table before you append the table. If you set Properties for the field, you must do so before you append the field to the fields collection. So creating a table goes something like this:

1. Declare variables (Table, Field)
2. Instantiate Table
   • Instantiate Field 1
   • Instantiate Property 1
   • Append Property 1
   • Instantiate Property 2
   • Append Property 2
   • Append Field1
   • Instantiate Field 2
     • Instantiate Property 1
     • Append Property 1
   • Append Field 2
3. Append Table

So, specific examples might go something like these:

 

Create Table

Sub exaCreateTableDAO()
'DAO DDL example demonstrates creating a table, fields, properties

' Declare object variables
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb

' Create the table (BooksDAO)
Set tbl = db.CreateTableDef("BooksDAO")

' Create a field (ISBN)
Set fld = tblNew.CreateField("ISBN", dbText, 13)

' Set field properties
fld.Required = True

' Append field (ISBN) to Fields collection
tbl.Fields.Append fld

' Create a field (Title)
Set fld = tblNew.CreateField("ISBN", dbText, 100)

' Set field properties
fld.Required = True
fld.AllowZeroLength = False
fld.DefaultValue = "Unknown"

' Append field (Title) to Fields collection
tbl.Fields.Append fld

' Append table (BooksDAO) to TableDef collection
db.TableDefs.Append tbl

End Sub

 

Modify Table

Modifying a table (that is, adding a new field or adding new properties to an existing field) is similar, except instead of instantiating a new table, you instantiate an existing table. When modifying an existing table or field, you do not need to append it to its collection.

Sub exaModifyTable()
'DAO DDL example'demonstrates modifying a table by adding a field
' and modifying an existing field's property

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = CurrentDb

' Instantiate existing table (BooksDAO)
Set tdf = db.TableDefs("BooksDAO")

' Create a field (Price)
Set fld = tdf.CreateField("Price", dbCurrency)

' Append field to Fields collection
tdf.Fields.Append fld

' Modify existing field (Title) with properties (validation rule
' and validation text)
Set fld = tdf.Fields("Title")
fld.ValidationRule = "Like 'A*' or Like 'Unknown'"
fld.ValidationText = "Known value must begin with A"

End Sub

 

Modify Field Names

Sub exaModifyFieldNames()
'DAO DDL example demonstrates modifying field names

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("MyTable")
For Each fld In tdf.Fields
    fld.Name = fld.Name & "new"
Next

End Sub

Create Database

Sub exaCreateDB()
'DAO DDL example demonstrates creating a database programmatically

Dim dbNew As DAO.Database
Set dbNew = CreateDatabase _
("C:\classes\cis253\winter98\MoreBks", dbLangGeneral)

End Sub

Delete Table

Sub DeleteTable()
'DAO DDL example demonstrates deleting a table

Dim db As DAO.Database
Set db = CurrentDb
db.TableDefs.Delete "tstBooks"

End Sub

Create Index

Sub exaCreateIndex()
'DAO DDL example demonstrates creating an index

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs!Books

' Create index
Set idx = tdf.CreateIndex("PriceTitle")

' Append fields to index
Set fld = idx.CreateField("Price")
idx.Fields.Append fld
Set fld = idx.CreateField("Title")
idx.Fields.Append fld

' Append index to table
tdf.Indexes.Append idx

End Sub

 

Create Primary Key

Sub CreatePrimaryKey()
'DAO DDL example demonstrates creating an index

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs!Books

' Create index
Set idx = tdf.CreateIndex("PriceTitle")

' Append fields to index
Set fld = idx.CreateField("Price")
idx.Fields.Append fld
Set fld = idx.CreateField("Title")
idx.Fields.Append fld

' Make Index primary
idx.Primary = True

' Append index to table
tdf.Indexes.Append idx

End Sub

 

Delete Index

Sub DeleteIndex()
'DAO DDL example demonstrates creating a composite primary key

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Set db = CurrentDb
Set tdf = db.TableDefs!Books
tdf.Indexes.Delete "PriceTitle"

End Sub

 

Create Relationship

Sub exaRelations()

'DAO DDL example creating a Relationship

Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb

' Create relation
Set rel = db.CreateRelation("PublisherRegions", _
"PUBLISHERDAO", "SALESREGIONS")

' Set referential integrity w/ cascade updates
rel.Attributes = dbRelationUpdateCascade

' Specify key field in KeyTable (Publishers)
Set fld = rel.CreateField("PubID")

' Specify foreign key in ForeignTable (SalesRegions)
fld.ForeignName = "PubIDFK"

' Append field to Relation
rel.Fields.Append fld

' Append relation to Relations collection
db.Relations.Append rel

End Sub

 

Drop Relationship

Sub exaDeleteRelation()

'DAO DDL example Deleting a Relationship
Dim db As Database
Set db = CurrentDb
db.Relations.Delete "PublisherRegions"

End Sub

 

Create Table With AutoNumber PrimaryKey And Hyperlink

Sub exaCreateTableWithAutoNumberAndHyperlink()

'DAO DDL example demonstrates creating a table, fields, properties
Dim db As DAO.Database
Dim tblNew As DAO.TableDef
Dim fld As DAO.Field

' Create the table and a field
Set db = CurrentDb()
Set tblNew = db.CreateTableDef("NewTable")
Set fld = tblNew.CreateField("AutoField", dbLong)

' Set field properties
fld.Required = True
fld.Attributes = dbAutoIncrField

' Append field to Fields collection
tblNew.Fields.Append fld

' Create Primary Key
Set idx = tblNew.CreateIndex("PrimaryKey")

' Append fields to index
Set fld = idx.CreateField("AutoField")
idx.Fields.Append fld

' Make Index primary
idx.Primary = True

' Append index to table
tblNew.Indexes.Append idx

' Create hyperlink field
Set fld = tblNew.CreateField("HyperField", dbMemo)

' Set field properties
fld.Attributes = dbHyperlinkField

' Append field to Fields collection
tblNew.Fields.Append fld

' Append table to TableDef collection
db.TableDefs.Append tblNew

End Sub

Data Definition Language: SQL

Data Definition Language (DDL)

Data Definition Language (DDL) is a programmatic way to create and modify the database structure, that is, objects like tables, indexes, and relationships.

Before Access came along, DDL statements were the only way to modify the database structure in most relational database management systems. Access introduced the graphical user interface (GUI) to do most DDL functions. Because the Access GUI is so easy to use, most Access users never have reason to use DDL statements. However, there are circumstances under which it is advantageous to use DDL statements.

I use them a lot for automating data import processes. I can create a temporary table, import data to the temp table, change data types, remove indexes from the permanent table, append data from the temp to the permanent table, then rebuild the indexes -- all automatically, all in SQL code.

But that's not the only use. You can also use DDL to set the seed and interval for an autonumber field, to remotely change the structure of a back-end database (useful for multi-user databases), or make any other change to a production database. In a web environment, it can be used to add databases to an active website.

In this three-part series, I'll be addressing both SQL and DAO.

• SQL vs. DAO
• DDL Using SQL (this post)
• DDL Using DAO
• You can download this whole series in a single document with sample database, here: http://www.rogersaccesslibrary.com/forum/topic570_post587.html

 

DDL Using SQL

The SQL DDL statements can be executed in a query window, but the most useful way to do it is in VBA code. To do that, I need to build a string holding the SQL statement and run it using the Execute method of the database object. There are two ways to do this. The first (and simplest) is to run it directly against the built-in CurrentDb object:

Dim strSQL As String
strSQL = "<The SQL Statement here>"
CurrentDb.Execute strSQL

The other is a little more complicated, but in my opinion better. Create a database object and set it to the CurrentDb:

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "<The SQL Statement here>"
db.Execute strSQL

This method is particularly useful when executing multiple SQL statements as I'll show later.

Drop Table

CurrentDb.Execute "DROP TABLE BOOKSQL;"

Create Table

'create a table with 4 fields and Primary key

Dim strSQL As String
strSQL = "CREATE TABLE BOOKSQL "
strSQL = strSQL & "(ISBN TEXT(13) CONSTRAINT PKey PRIMARY KEY, "
strSQL = strSQL & "Title TEXT(100), "
strSQL = strSQL & "Price CURRENCY, "
strSQL = strSQL & "PubID TEXT(10)); "
CurrentDb.Execute.Execute strSQL

Datatype key words:

• Autonumber -- AUTOINCREMENT
• Text -- TEXT(<length>)
• Memo and Hyperlink - MEMO
• Byte -- BYTE
• Integer -- SHORT
• Long integer -- LONG
• Single -- SINGLE
• Double -- DOUBLE
• ReplicationID -- GUID
• Date/Time -- DATETIME
• Currency -- CURRENCY
• Yes/No -- LOGICAL
• OleObject -- OLEOBJECT

Alter Table

CurrentDb.Execute "ALTER TABLE PublisherSQL ADD PubAddress TEXT (50);"

CurrentDb.Execute "ALTER TABLE BookSQL ADD Cost CURRENCY;"

CurrentDb.Execute "ALTER TABLE BookSQL ADD PubDate DATETIME;"

Drop Index

CurrentDb.Execute "DROP INDEX idxTitle ON BookSQL;"

Create Index

CurrentDb.Execute "CREATE UNIQUE INDEX idxTitle ON BookSQL (Title);"

Create Primary Key

CurrentDb.Execute "CREATE UNIQUE INDEX PrimaryKey ON Books (BookID) WITH PRIMARY;"

Drop Relationship

CurrentDb.Execute "ALTER TABLE BookSQL DROP CONSTRAINT PubBook;"

Create Relationship

Dim strSQL As String
strSQL = "ALTER TABLE [BookSQL] "
strSQL = strSQL & " ADD CONSTRAINT [PubBook] FOREIGN KEY (PubID) "
strSQL = strSQL & " REFERENCES PublisherSQL (PubID);"
CurrentDb.Execute strSQL

Examples:

Sub DropTables()
'deletes a Relationship and two tables
Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "ALTER TABLE BookSQL DROP CONSTRAINT PubBook;"
    db.Execute "DROP TABLE PublisherSQL;"
    db.Execute "DROP TABLE BookSQL;"
Set db = Nothing
End Sub

'---------------------------------

Sub BuildTables()
' creates two tables and a Relationship between them
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
'create PublisherSQL table
    strSQL = "CREATE TABLE PublisherSQL "
    strSQL = strSQL & "(PubID TEXT(10) CONSTRAINT "
    strSQL = strSQL & "PrimaryKey PRIMARY KEY, "
    strSQL = strSQL & "PubName TEXT(100), "
    strSQL = strSQL & "PubPhone TEXT(20));"
    db.Execute strSQL
'create BookSQL table
    strSQL = "CREATE TABLE BookSQL "
    strSQL = strSQL & "(ISBN TEXT(13) CONSTRAINT PKey PRIMARY KEY, "
    strSQL = strSQL & "Title TEXT(100), "
    strSQL = strSQL & "Price MONEY, "
    strSQL = strSQL & "PubID TEXT(10)); "
    db.Execute strSQL
'create One-To-Many relationship between BookSQL and table
    strSQL = "ALTER TABLE [BookSQL] "
    strSQL = strSQL & " ADD CONSTRAINT [PubBook] FOREIGN KEY (PubID) "
    strSQL = strSQL & " REFERENCES PublisherSQL (PubID);"
   db.Execute strSQL
Set db = Nothing
End Sub

'---------------------------------

Sub DropIndex_Import_CreateIndex()
' this sample demonstrates removing an index, importing data, and
' re-creating the index.
Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "DROP INDEX idxTitle ON BookSQL;"
    DoCmd.TransferText acImportDelim, "", "Books", _
         CurrentProject.Path & "\Books.txt", True, ""
    db.Execute "create unique index idxTitle on BookSQL(Title);"
Set db = Nothing
End Sub

'---------------------------------

Sub ModifyField()
'you can't directly modify a field in a table you have to:
'1) create a new field with the new properties
'2) copy the data from the old field to the new field
'3) delete the old field
'Note: if the field to be deleted is part of an index, that
'index must be dropped and then re-established on the new field
Dim db As DAO.Database
    Set db = CurrentDb
    db.Execute "alter table [Books Copy] add Title2 text(100);"
    db.Execute "UPDATE [Books Copy] SET Title2 = Title;"
    db.Execute "alter table [Books Copy] drop column Title;"
Set db = Nothing
End Sub

Next time, I'll take a closer look at specific Data Definition Language using DAO.

Data Definition Language (DDL) DAO vs. SQL

Data Definition Language (DDL)

Data Definition Language (DDL) is a programmatic way to create and modify the database structure, that is, objects like tables, indexes, and relationships.

Before Access came along, DDL statements were the only way to modify the database structure in most relational database management systems. Access introduced the graphical user interface (GUI) to do most DDL functions. Because the Access GUI is so easy to use, most Access users never have reason to use DDL statements. However, there are circumstances under which it is advantageous to use DDL statements.

I use them a lot for automating data import processes. I can create a temporary table, import data to the temp table, change data types, remove indexes from the permanent table, append data from the temp to the permanent table, then rebuild the indexes -- all automatically, all in SQL code.

But that's not the only use. You can also use DDL to set the seed and interval for an autonumber field, to remotely change the structure of a back-end database (useful for multi-user databases), or make any other change to a production database. In a web environment, it can be used to add databases to an active website.

In this three-part series, I'll be addressing both SQL and DAO.

• SQL vs. DAO (this post)
• DDL Using SQL
• DDL Using DAO
• You can download this whole series in a single document with sample database, here: http://www.rogersaccesslibrary.com/forum/topic570_post587.html

DAO vs. SQL

In Access, the two major methods are DAO methods and SQL Statements. DAO (Data Access Objects) is the object model that Access uses to programmatically manipulate the database and its data. (There are other object models you can use like ADO and ADO.net, but DAO is recommended for use with Access.) However, in many cases, you can also manipulate your database with in SQL statements. In general, SQL is more efficient than other methods, so if you can, it's recommended.

SQL DDL statements have a number of advantages over other methods of modifying the database structure. For one thing, is independent of the object model you're using (ADO, DAO, ADO.Net) and can be executed from different platforms like VBA, C++, C#, and so forth. And while there are minor differences in implementation, DDL is fairly standard to most database platforms like Access, SQL Server, Oracle, and Sybase. It is also easier to read and understand.

For instance, in SQL, I can create a simple table like so:

Sub CreateTableSQL()
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb
    strSQL = "CREATE TABLE NewTable2 " & _
        "(NewField1 TEXT(100), " & _
        "NewField2 SINGLE);"
    db.Execute strSQL
End Sub

By comparison, in DAO, I need to do the following:

Sub CreateTableDAO()
Dim db As DAO.Database
Dim tblNew As DAO.TableDef
Dim fld As DAO.Field
    Set tblNew = db.CreateTableDef("NewTable")
    Set fld = tblNew.CreateField("NewField1", dbText, 100)
    tblNew.Fields.Append fld
    Set fld = tblNew.CreateField("NewField2", dbSingle)
    tblNew.Fields.Append fld
    db.TableDefs.Append tblNew
End Sub

SQL DDL statements have a number of disadvantages as well. Most importantly, you cannot set a number of Access specific table properties, like Validation Rules, Validation Text, Default values, and so forth. To do that, you have to use an object model like DAO.

Next time, I'll take a closer look at specific SQL Data Definition Language statements.

New Sample: ListFoldersAndFiles

By AD Tejpal

    This sample db demonstrates listing of folders and files contained within the selected top directory, as per desired file specifications. Based upon user's choice, subfolders can be included or ignored. If desired, more than one types of files can be included in a single file spec in the form of a comma separated string - e.g. :  "*.htm,*.pdf,*.txt" etc.

    Process Mode For Listing Folders and files:

    Two alternative methods for listing of folders and files, under a given top folder, are covered as follows:
    (a) Non-Recursive mode - using Dir() function. Apart from being faster than (b), it has the advantage that there is no extra strain on memory resources (otherwise associated with recursive approach), thus avoiding the risk of potential hang up in case of very large and deep directory tree.
    (b) Recursive mode - using FileSystemObject

    Display Of Listed Folders And Files:

    Each run for listing of folders and files is logged in table T_ProcessLog. On the viewing form, for the selected ProcessID, path and other details of topmost folder are displayed at top of the form. Similar details for the current subfolder are displayed just below the information for top-most folder.

    Subfolders and their files are displayed in adjacent subforms. For the current file, its details (like file type, size, attributes, DtCreated / DtLastModified / DtLastAccessed) are also displayed, apart from a hyperlink to the file itself. The hyperlink label becomes active only for permitted file types. The user can edit the contents of table T_AllowHyperLink for setting such  permissions. Three alternative styles of display are provided as follows:

    Style A - View Folders and Files In Hierarchical Chain:

    The user can drill down the directory tree by expanding any of the subfolders which then assumes the role of current main folder, resulting in display of subfolders and files held by the erstwhile subfolder. This can be done indefinitely, till the last subfolder at deepest nesting level is reached. Similarly, by pressing a command button, the user can move up the directory tree. The process can be repeated till the current main folder becomes identical to the top folder (i.e. the original top most folder for which the listing was generated).

    Style B - View All Folders At A Glance - And Their Files:

    For convenient viewing, the folders are sorted as per nesting level and path. The top-most folder (nesting level: zero) is highlighted in distinct color. For other folders, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other.

    Style C - View All Files At A Glance:

    For convenient viewing, the files are sorted as per nesting level and path. Files in top-most folder (nesting level: zero) are  highlighted in distinct color. For other files, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other.

    General module named basCommDlg, an adaptation from Access Developer's Handbook, has been kindly provided by Bill Mosca.

    Note:
    (a) While using file system object, all types of folders and files get covered (including System, Volume, Hidden, ReadOnly etc). On the other hand, while using Dir() function, such types don't get covered unless relevant arguments are explicitly supplied.

    (b) Using Dir command via DOS command prompt, listing of folders and files can be saved to a text file, which can then be imported into access table. Such a listing is quite fast and one might be tempted to try this route. However, there is a pitfall associated with this approach. If any special characters are present in the folder or file name (say in internet files), the same might not come through faithfully. For example ® is found to come across as r - resulting in corrupted path. 

Version: Access 2000 file format.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/topic568_post584.html

More samples by AD Tejpal: http://www.rogersaccesslibrary.com/forum/tejpal-ad_forum45.html

Searching for a Wildcard in a “LIKE” Criteria

In a query, the LIKE operator allows the user to query for a character or group of characters anywhere within a text field.  It does this by matching the field to a string which mixes the character(s) you want to match with wildcard characters like * and ?. Some examples:

• FirstName LIKE "C*"  will return any name that starts with a C (i.e. Carlton, Clark)
• LastName LIKE "*-*"  will return any last name that has a hyphen anywhere in the field (i.e. Flickema-Carlson, Smith-Jones)
• PONum LIKE "C????"   will return and PO number that starts with a C and has exactly 5 characters. (i.e. CSIDF, C24DG)
• PONum LIKE "C##"  returns all values that start with a C, is exactly 3 characters long, and characters 2 and 3 MUST be numbers. (i.e. C45, C16)

So how can I search for a wildcard character itself?  I can simply enclose the wildcard character with brackets [].

For instance, if I wanted to find any value that has an asterisk (*) anywhere in it, I could do this:

• PO LIKE "*[*]*"

If I wanted to find any value that starts with a hash mark(#), I could do this:

• Check LIKE "[#]*"

If I wanted to find any value ending with a question mark (?), I could do this:

• Comment LIKE "*[?]"
  

Showing Query Parameters in a Report

A parameter query is one which asks the user for input.  For instance, suppose I have a query that pulls records for a date range. Suppose further, I want the query to ask me for a Start Date and End Date for the range.  I can create a query like this:

SELECT * FROM MyTable WHERE TheDate BETWEEN [Enter Start Date] AND [Enter End Date]

Running this query will bring up two dialog boxes:

 

Entering the values in the boxes will return the records in that range.

But if I create a Report based on this query, how do I show the selected date range?  After all, reports show information from records in the query’s record source, and Parameter values aren’t included.

There are a couple of possibilities.

Method 1: Read Parameters Directly

The first is to read the parameter directly.  To do that, I just put a text box on my report for each query parameter. In the control source for the text boxes, I put the query parameter preceded by an equal sign.

For instance, in the case of the above query, the control sources for my two text boxes will be:
=[Enter Start Date] and =[Enter End Date]

Alternately, I could have a single text box with your dates concatenated:
=[Enter Start Date] & " - " & [Enter End Date]

I can also fancy it up a bit like this:
=”Date Range: “ & [Enter Start Date] & " to " & [Enter End Date]

Method 2: Read Values Returned

The second option is to read the actual minimum and maximum values from the records in the record source.  I can use the Min and Max functions of the report to do that.  Again, in the Control Source property of a text box:

=Min([Enter Start Date]) & " - " & Max([Enter End Date])

I can, of course, put them in separate text boxes as well.

Comparing The Methods

So what’s the difference between the two methods?

The first method returns the date range requested.  The second method returns the date range actually returned.

Isn’t that the same thing?  Not necessarily.  Suppose I request data from 1/1/2010 to 7/1/2011, but my table only has data starting with 2/1/2011.  The first method will return 1/1/2010 to 7/1/2011 (what I requested).  But the second method will return 2/1/2011 to 7/1/2011 (what’s actually in the record source of the report).

Microsoft Releases Office 2010 SP1

Office 2010 Service Pack 1 has been released by Microsoft.  See MS Knowledgebase article: http://support.microsoft.com/kb/2460049.  The service pack can be downloaded from the article as well.

The main improvements to Access appear to be in the area of bug-fixes and stability.  There are only three improvements listed in the KB article, but in the downloadable fix list there’s a much longer list of fixes.

Everybody has different priorities, but to my mind, here are some of the more important problems that were fixed:

• Access does not activate or return the user to the correct Ribbon tab for a previously opened database object when the user returns to that object.
• Access Wizards are not loaded correctly when "Disable all controls without notification" is selected in Trust Center.
• The program crashes when you apply a sort to a query that is based on a multi-value field.
• "Reserved error -5500" occurs when you try to run a cross-tab query that would generate null values in the column names of the query.
• "Object invalid or no longer set" error occurs when you try to use an ALTER TABLE query to change a field type or size.
• You cannot relink tables in Access databases that have linked tables to other MDBs/ACCDBs that cannot be found
• The file format that is displayed in the title bar for Access 2010 databases is "(Access 2007)."
• Incorrect data is displayed when a user's query has a list that includes a combination of GroupBy and either OrderBy or Where
• "Invalid precision for decimal data type" or results are truncated when the user runs a crosstab query.

There are also a lot of “crash fixes”, all of which are important.

New Sample: NormalizingRepeatingColumnsVBA.mdb

By Roger Carlson

This sample demonstrates how to normalize a table that has repeated columns with VBA.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet with code rather than SQL statements.

Full Article Included

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumnsvbamdb_topic567.html

Normalizing Repeated Columns: VBA

In my earlier blog series (The Problem of Repeated Columns), I defined repeated columns and talked about the data integrity problems associated with them. I also showed several different examples of repeated columns and the how difficult it is to query repeated columns as compared to the normalized equivalent. If you haven't read these yet, it would be worthwhile to read first. Similarly, if you are not familiar with the concept of Normalization, you should read my blog series What is Normalization?

One of the comments suggested I should discuss how to convert a table with repeated columns into a normalized table structure. I thought that was a really good idea, so I'm going to spend the next few posts doing so. The difficulty in discussing this issue, however, is that the exact solution differs with each table, so there is no single solution. Fortunately, in the last series, I showed 5 different tables, each with a slightly different structure, so while I cannot show a single solution that will work for every table with repeated columns, hopefully, one of the following will work for most cases.

Others in this series:

• Single Repeated Column
• Multiple Repeated Columns
• Yes/No Fields
• Using VBA (this post)

In my previous examples, I used purely SQL solutions. This time, I thought I'd illustrate how to do this with VBA. There's no real advantage to using VBA over the SQL solutions. In fact, in large databases, it will almost always be slower. Nevertheless, there may be situations with complex data that VBA would be the best solution, and besides, it represents another tool in your Access toolkit.

In my post Aggregating Across Repeated Columns: Summing, I discussed an example of table with repeated columns that looked like this:

Figure 1: Student Scores table with repeated columns

Normalized to the First Normal Form (1NF) to remove the repeated columns, the table would look like this:

Figure 2: Patient Symptom table (1NF)

Figure 2, however does not represent the final form of normalization. Because the student names are repeated, they should be removed to a separate table. So normalizing to Third Normal Form (3NF) I would have something like this:

Figure 3: Normalized to Third Normal Form (3NF) - Tables and Relationships Views

(For a further explanation of both First and Third Normal Forms, see The Normal Forms , What is Normalization, and Entity-Relationship Diagramming).

Normalizing to First Normal Form (1NF)

I'll start with the 1NF because it's less complex. Overall, it's a matter of looping through the records in the denormalized table (i.e. StudentScores_RepeatedColumns) and writing each column value into a new record in the 1NF table (i.e. StudentScores_1NF).

Public Sub Normalize_RepeatedColumns_VBA_1NF(ParamArray FieldNames())
'   This routine writes data from a table with repeated columns into a
'   table normalized to the First Normal Form (1NF)
'   The field names which are send in via the parameter list of the call
'   are written into an array called FieldNames.
'   declare variablesDim i As Integer
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
'   Open database object to current database

Set db = CurrentDb
'   Open the denormalized table to read the values

Set rsSource = db.OpenRecordset("StudentScores_RepeatedColumns")
'   Open the normalized table write the values

Set rsTarget = db.OpenRecordset("StudentScores_1NF")
'   Loop through the denormalized Source table

Do While Not rsSource.EOF
    '   Loop through the fields, i.e. the values in the parameter array    

For i = LBound(FieldNames) To UBound(FieldNames)
        '   Add a New record to the target table, write the values,
        '   and save (update) the record

        rsTarget.AddNew
            '   write the student name

            rsTarget!StudentID = rsSource!Student
            '   write the field name

            rsTarget!TestNum = FieldNames(i)
            '   write the field value

            rsTarget!Score = rsSource(FieldNames(i))
        rsTarget.Update
     Next i
     rsSource.MoveNext
Loop
End Sub
You can call the subroutine like so:
Sub test1NF()

'   To run the subroutine, place the cursor in this sub and click "run"
'   The arguments are the field names of the columns you want to
'   normalize
  Call Normalize_RepeatedColumns_VBA_1N _
       ("Test1", "Test2", "Test3", "Test4")
   
End Sub

The end result looks like Figure 2 above.


Normalizing to Third Normal Form (3NF)

The process for normalizing repeated column data into 3NF is similar to the 1NF process. It does, however, require two loops, one to add records to the "one-side" table ("Student") , and an inner loop (For...Next) to write records to the "many-side" table ("StudentScores").

It is important to note that the order in which the data is moved is vital. Data must be written into the "one-side" table first, and then data can be moved into the "many-side" table. Overall, the process is to loop through the records in the denormalized table, write common values to the a record in the one-side table, store the primary key value from the new record, and then for each field in the parameter array, create a new record in the many-side table.

Public Sub Normalize_RepeatedColumns_VBA_3NF(ParamArray FieldNames())
'   This routine writes data from a table with repeated columns into two
'   normalized tables: Students and StudentScores
'   The field names which are send in via the parameter list of the call
'   are written into an array called FieldNames.
'   declare variables

Dim i As Integer
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTargetOneSide As DAO.Recordset
Dim rsTargetManySide As DAO.Recordset
Dim StudentIDtemp As Long
'   Open database object to current database

Set db = CurrentDb
'   Open the denormalized table to read the values

Set rsSource = db.OpenRecordset("StudentScores_RepeatedColumns")
'   Open the "one-side" table to write the values

Set rsTargetOneSide = db.OpenRecordset("Student")
'   Open the "Many-side" table to write the values

Set rsTargetManySide = db.OpenRecordset("StudentScores")
'   Loop through the denormalized Source table

Do While Not rsSource.EOF
    '   Add a New record to the "one-side" target table, save
    '   the primary key value (autonumber) for use later,
    '   and save (update) the record

    rsTargetOneSide.AddNew
        rsTargetOneSide!Student = rsSource!Student
        '   save the StudentID created by the autonumber field

        StudentIDtemp = CLng(rsSource("StudentID"))
    rsTargetOneSide.Update
   
    '   Loop through the fields in the Parameter Array    

For i = LBound(FieldNames) To UBound(FieldNames)
   
        '   Add a New record to the "many-side" target table,
        '   write the values, and save (update) the record       

             rsTargetManySide.AddNew

            '   write the saved student id\

            rsTargetManySide!StudentID = StudentIDtemp
            '   write the field name

            rsTargetManySide!TestNum = FieldNames(i)
            '   write the field value

            rsTargetManySide!Score = rsSource(FieldNames(i))
        rsTargetManySide.Update
       
    Next i
    rsSource.MoveNext
Loop
End Sub
You can call the subroutine like so:
Sub test3NF()

'   To run the subroutine, place the cursor in this sub and click "run"
'   The arguments are the field names of the columns you want to
'   normalize  
  Call Normalize_RepeatedColumns_VBA_3NF _
      ("Test1", "Test2", "Test3", "Test4")
   
End Sub
The end result looks like Figure 3 above.

You can find a sample which illustrates the above here:
http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumnsvbamdb_topic567.html