Working with Relations

Relationships between tables are crucial to preserving data integrity in MS-Access or any other relational database system. In these examples, I show both how to create and delete a relationship. The "create" example creates a one-to-many relationship, enforces referential integrity, uses a single field, without cascades.

You can certainly manage relations in Access using the GUI (Tools ~ Relationships) but one of my "best practices" is to code any table changes in a test environment, and then apply them to the production database.

Note: Lines are wrapped to fit the Blogger window - you can view the formatted examples at Working with Relations.

Sub AddRel_Nov_26_2007()
    ' create one-to-many relationship 
    ' from PrimaryTable to ForeignTable
    Const PrimaryTable = 
        "tlkpScholarshipStatus"
    Const PrimaryField = "Status"
    '
    Const ForeignTable = "tblSCH_Applicant"
    Const ForeignField = "Status"
    '
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    '
    Set db = CurrentDb
    Set rel = db.CreateRelation
    With rel
    .Name = PrimaryTable & ForeignTable
    .Table = PrimaryTable
    .ForeignTable = ForeignTable
    .Fields.Append rel.CreateField(PrimaryField)
    .Fields(PrimaryField).ForeignName = ForeignField
    End With
    db.Relations.Append rel
    Set fld = Nothing
    Set rel = Nothing
    Set db = Nothing
End Sub

Sub DelRel_Nov26_2007()
    ' delete the relationship
    ' from PrimaryTable to ForeignTable
    Const PrimaryTable = "tlkpScholarshipStatus"
    Const ForeignTable = "tblSCH_Applicant"
    ' relation was named 
    ' using concatenated table names
    Const RelationName = 
        "tlkpScholarshipStatustblSCH_Applicant"
    '
    Dim db As DAO.Database
    '
    Set db = CurrentDb
    db.Relations.Delete RelationName
    Set db = Nothing
End Sub

footnote:

This best practice of scripting table changes came in quite handy when working with an out-of-state customer. Instead of having the back-end database emailed for me to apply the changes, I simply coded the changes into a module in a newfront-end file & the customer ran the table-change code. This minimized downtime on their production system.

Saving Access Forms as Reports

Forms are the heart of an Access application, but sometimes a hard-copy is still required. A well-formatted form can be printed simply by using the DoCmd.PrintOut statement, but to print a single record you'll probably need to set a bookmark of some kind, apply a filter, print the form, and then use the bookmark to return to that same record.

Access provides for a "Save As Report" feature, so you can quickly create a Report that matches the form & then use DoCmd.OpenReport using a filter or where clause. Very handy. However, if you have code-behind in your form, Access will also carry that code into the report module which can cause compilation errors.

For example, if you have a Form_Open event, that code will be copied verbatim into the report module but it won't compile due to the class mismatch. If you do not compile your modules, Access 2003 will ignore that error, but Access 2007 is much less tolerant and it will report "an error loading ... continue loading project?" but either way you respond, 2007 will not run any portion of your application until the code is fixed.

Apparently, Access 2007 opens every single module in the database, so obsolete or old versions of objects can prevent an app from running, even if they are never called from the application.

Formatted MsgBox in Access

I wanted to create a formatted message box in Access, with the first line being bold, to duplicate the built-in delete confirmation message. In older versions of Access, you could use

MsgBox "First line is bold@Second line is normal@@"

but when Access moved from 97 to 2000 this feature went away.

I Google'd this & found several versions of a workaround, and here is what worked for me in Access 2003:

EDIT: 06/19/2008 this replaces the original code:

Function MsgBoxEx(sBoldText As String, sNormalText As String, iButtons As Integer, iIcon As Integer, sTitle As String) As Integer
Dim s As String
s = sBoldText & "@"
s = s & sNormalText & "@@"
MsgBoxEx = Eval("MsgBox(""" & s & """, " & iButtons + iIcon & ", '" & sTitle & "')")
End Function

This function will accept built-in constants for buttons and icons, and will return a value based on the buttons.

SQL Server Backup / Restore

I routinely take a backup from the production SQL 2005 Server and restore it to my dev system. I had previously published my code for backup and restore, which used backup devices but I found that was not necessary.

I have a folder on the production server named E:\SQL_Bill which is shared for me alone and is the target for my backup. I then zip and copy the file to my thumb drive, then copy and unzip the file to my dev system in the default backup folder.

Here is the backup script:

BACKUP DATABASE [lampsPAR]
TO DISK = N'E:\SQL_Bill\wm_LampsPAR.bak'
WITH
NOFORMAT,
INIT,
NAME = N'lampsPAR-Full Database Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO

and here is the restore script:

RESTORE DATABASE [LampsPAR]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\wm_LampsPAR.bak'
WITH
FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO

To create these scripts, all I had to do is use SSMS to set up my backup, but then I clicked the script button to generate the script in a new window. I then cancelled the backup & saved the script as a .sql file. I did the same for the restore script. What could be easier than that.