MS-Access / Getting Started

---

Handling Application Dependencies

Configurations can vary from one machine to another, making application dependencies one of the more difficult aspects of development and deployment. Let's take a look at some ways you can deal with one dependency issue in particular - references.

The following examples tie into one another so begin by creating a new database for the code that follows.

Installing Files from an Attachment Field

Certain dependencies can also be stored and subsequently installed from an attachment field. This is particularly useful if you are distributing library databases with your application. Using an attachment field allows you to create setup routines that are built in to the database.

To use an attachment field to contain the dependent files, begin by creating a table with the following schema. Save the table as USysTblDependentFiles when you're done.

As the MDB file format does not support the Attachment data type, this will work only in the new ACCDB file format.

Field Name	Field Type (Size)	Description
ID	Attachment	Primary Key
DependentFiles	Attachment	Contains the dependent files
Component		Description of the attachment contents. Used in the WHERE clause of a recordset that retrieves setup files and allows you to define multiple files that make up a given component.

In our example, we will store a single ACCDB file in an attachment field. This file will be used as a reference in our application.

Create a new class module called Installer and add the following code:

' installs a file from tblAttachments
Public Function InstallFromAttachment(stComponent As String, _
    stDestPath As String) As Long
    On Error GoTo err_handler:

Therefore, we'll need two Recordset objects.

    Dim rs1 As DAO.Recordset2
    Dim rs2 As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim stSQL As String
    Dim stDestFile As String

Next, we want to open the dependent files table for the selected component and get the recordset of attachments. This will be stored in the variable named rs2.

    ' open the attachment table and look for the specified attachment
    stSQL = "SELECT * FROM USysTblDependentFiles " & _
    "WHERE Component = '" & stComponent & "'"
    Set rs1 = CurrentDb().OpenRecordset(stSQL)
    Set rs2 = rs1.Fields("DependentFiles").Value

Because the attachment field may store multiple attachments, we should walk through the entire recordset of files. We'll get the FileData field for each attachment and save it to the hard drive using the SaveToFile method. The FileData field in the attachment recordset contains the actual file.

    If (Not (rs2.EOF)) Then
	' save the attachment data - may be multiple attachments
	While (Not rs2.EOF)
	    Set fld = rs2("FileData")
	    stDestFile = stDestPath & "\" & rs2("FileName")
	    fld.SaveToFile stDestFile
	    ' next attachment
	    rs2.MoveNext
	Wend
    Else
	Err.Raise vbObjectError + 513, TypeName(Me), _
	  "Cannot find files for component: " & stComponent
    End If

We're raising an error here instead of displaying a message box because this is a class module. The last thing we should do is clean up our objects and then exit the routine:

cleanup:
    DoEvents
    rs2.Close: Set rs2 = Nothing
    rs1.Close: Set rs1 = Nothing
    Set fld = Nothing
    Exit Function
err_handler:
    If (Err = 3839) Then
	' file already exists - delete it
	VBA.Kill stDestFile
	Resume
    ElseIf (Err = 91) Then
	Err.Clear
	Exit Function
    ElseIf (Err <> 0) Then
	MsgBox "Unexpected error: " & Err.Description, vbCritical
	Resume cleanup
    End If
End Function

The 3839 error code shown in the error handler is raised when the file being saved already exists. In our case, we're deleting the file and updating it from the one in the attachment field.

To test this routine, insert multiple attachments into the USysTblDependentFiles table. Then, add the following test code.

Public Sub InstallAttachmentsTest()
    Dim objInstaller As New Installer
    objInstaller.InstallFromAttachment "<YourComponentName>", CurrentProject.Path
    Set objInstaller = Nothing
End Sub

This code will install files from the dependent files table to the directory where the database resides. Be sure to change the text YourComponentName to match that in your table.