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.