ODBC Example for Access 95 and 97

Take the following steps to create and use a simple database, containing only images. (For a more sophisticated approach, refer to Using ODBC to Access Image Data.)

1. Start Microsoft Access 95 or 97.

2. Select Blank Database, and click OK.

3. Specify c:\lead.mdb as the database name and click Create.

4. In the Database window, select the Tables tab, and click the New button.

5. In the New Table window, select Design View and click OK.

6. In the table designer, specify one field as follows:

Field Name: photo
Data Type: OLE Object

7. Close the table designer, saving the table with the following name:

Table Name: ltacpic

8. In the Windows Control Panel, start the 32bit ODBC administrator and click the Add button.

9. Select Microsoft Access driver and click the OK button.

10. Enter the name LEADACCESS.

11. Click the Select directory button and select the c:\ root directory.

12. Click the OK button; click the next OK button; then click the Close button.

13. Return to Access.

14. In the Database window, select the Forms tab, and click the New button.

15. In the New Form window, select Design View and click OK.

16. Click and drag the form's detail area to make it 5 1/2 inches deep (large enough to fill the screen when you maximize it.)

17. On the Insert pull-down menu, use the Custom Control option to select the Lead Control.

18. Click the OK button. The lead control appears on the form.

19. Drag the Lead control halfway down the form so that you will have room for command buttons later. (You do not need to resize the control, because you will add code to size and position it at runtime.)

20. In the properties box, change the name of the control to Lead1.

21. At the top of your main form, add seven command buttons and name them as follows:

Name	Caption
RFirst	\|<
RNext	>
RPrev	<
RLast	>\|
RUpdate	Flip
RAdd	Add

22. Also at the top of the form, add a Combo Box, and set its properties as follows:

Name: SelectedFile

Row Source Type: Value List

Row Source: c:\lead\images\image1.cmp;c:\lead\images\image2.cmp

Default Value: "nothing"

23. Click the Code Window icon on the toolbar.

$image\btncode.gif$ For Access 95.

$image\btncode2.gif$ For Access 97.

24. Select the Activate procedure for Form1, and add the following code. In online help, you can select the block of code with the mouse, then press Ctrl-C to copy it.

' Declare local variables
Dim HeightAllowed, WidthAllowed, DString, msg, answer

' Set up the application window
DoCmd.Maximize
Me.ScrollBars = False
Me.Painting = True

' Set the position and size of the LEAD control.
' Allow for a border of 1/8 of the form size.

'The units of measure do not matter, since we are calculating proportions.
HeightAllowed = Me.WindowHeight * 3 / 4
WidthAllowed = Me.WindowWidth * 3 / 4

Lead1.Left = (Me.WindowWidth - WidthAllowed) / 2
Lead1.Top = (Me.WindowHeight - HeightAllowed) / 2
Lead1.Width = WidthAllowed
Lead1.Height = HeightAllowed

'Turn off automatic repainting and turn on scroll bars.
Lead1.AutoRepaint = False
Lead1.AutoScroll = True

'Set the image display size to match the LEAD control
Lead1.SetDstRect 0, 0, Lead1.ScaleWidth, Lead1.ScaleHeight
Lead1.SetDstClipRect 0, 0, Lead1.ScaleWidth, Lead1.ScaleHeight

' Display the LEAD control.
Lead1.BorderStyle = 1

' Open the database.
DString = "ODBC;DSN=LEADACCESS"
Lead1.dbOpen DString, "ltacpic", "photo", DB_OPENOPTIONS_NONE
Lead1.dbLoadBits = 24
Lead1.dbLockingMode = DB_LOCKINGMODE_OPTIMISTIC
Lead1.ForceRepaint
If Lead1.dbIsBOF = True And Lead1.dbIsEOF = True Then
  msg = "The database is empty."
  answer = MsgBox(msg, 0, "Notice")
  RNext.Enabled = False
  RPrev.Enabled = False
End If

25. For the RAdd button, add the following code to the click procedure:

Dim msg, answer, filename, nRet, ErrorStatus
ErrorStatus = Lead1.EnableMethodErrors
Lead1.EnableMethodErrors = False
If Lead1.dbCanAppend = False Then
    msg = "You cannot add to this database."
    answer = MsgBox(msg, 0, "Error")
    GoTo endofadd
End If

filename = Dir(SelectedFile.Value)
If filename = "" Then
    msg = "Specified file does not exist."
    answer = MsgBox(msg, 0, "Error")
    GoTo endofadd
End If

Lead1.dbAddNew

nRet = Lead1.Load(SelectedFile.Value, 0, 0, 1)
If nRet <> 0 Then
    msg = "Invalid file format."
    answer = MsgBox(msg, 0, "Error")
    GoTo endofadd
End If

nRet = Lead1.dbUpdate(FILE_CMP, 24, QFACTOR_QMS)
If nRet <> 0 Then
    msg = "You cannot update this database."
    answer = MsgBox(msg, 0, "Error")
    GoTo endofadd
End If

Lead1.dbMoveLast
Lead1.ForceRepaint
msg = "Image added to the database."
answer = MsgBox(msg, 0, "LEAD")
RNext.Enabled = True
RPrev.Enabled = True
If Lead1.dbIsBOF = True Then
RPrev.Enabled = False
Else
RPrev.Enabled = True
End If

If Lead1.dbIsEOF = True Then
RNext.Enabled = False
Else
RNext.Enabled = True
End If

endofadd:
Lead1.EnableMethodErrors = ErrorStatus

26. For the RUpdate button, add the following code to the click procedure:

Dim msg, answer
If Lead1.dbCanUpdate = False Then
    msg = "You cannot update this database."
    answer = MsgBox(msg, 0, "Error")
ElseIf Lead1.dbIsBOF = True Or Lead1.dbIsEOF = True Then
    msg = "There is no current record."
    answer = MsgBox(msg, 0, "Error")
Else
    If Lead1.dbEditMode = DB_EDITMODE_NONE Then
        Lead1.dbEdit
        Lead1.Flip
        Lead1.ForceRepaint
    End If
    Lead1.dbUpdate FILE_CMP, 0, QFACTOR_QMS
End If

27. For the RDelete button, add the following code to the click procedure:

Dim msg, answer
If Lead1.dbCanUpdate = False Then
    msg = "You cannot update this database."
    answer = MsgBox(msg, 0, "Error")
ElseIf Lead1.dbIsBOF = True Or Lead1.dbIsEOF = True Then
    msg = "There is no current record."
    answer = MsgBox(msg, 0, "Error")
Else
    Lead1.dbDelete
    Lead1.dbRequery
    Lead1.dbMoveFirst
    If Lead1.dbIsBOF = True And Lead1.dbIsEOF = True Then
      RNext.Enabled = False
      RPrev.Enabled = False
    End If
    Lead1.ForceRepaint
End If

28. For the RFirst button, add the following code to the click procedure:

Dim msg, answer
If Lead1.dbIsBOF = True And Lead1.dbIsEOF = True Then
    msg = "The database is empty."
    answer = MsgBox(msg, 0, "Notice")
    RNext.Enabled = False
    RPrev.Enabled = False
Else
    Lead1.dbMoveFirst
    RPrev.Enabled = False
    RNext.Enabled = True
    Lead1.ForceRepaint
End If

29. For the RNext button, add the following code to the click procedure:

Dim msg, answer
If Lead1.dbIsBOF = True And Lead1.dbIsEOF = True Then
    Lead1.SetFocus
    RPrev.Enabled = False
    RNext.Enabled = False
    msg = "The database is empty."
    answer = MsgBox(msg, 0, "Notice")
Else
      Lead1.dbMoveNext
      If Lead1.dbIsEOF = True Then
        Lead1.SetFocus
        RNext.Enabled = False
        Lead1.dbMovePrev
        msg = "Already at last record."
        answer = MsgBox(msg, 0, "Notice")
      Else
        RPrev.Enabled = True
      End If
      Lead1.ForceRepaint
End If

30. For the RPrev button, add the following code to the click procedure:

Dim msg, answer
If Lead1.dbIsBOF = True And Lead1.dbIsEOF = True Then
    Lead1.SetFocus
    RPrev.Enabled = False
    RNext.Enabled = False
    msg = "The database is empty."
    answer = MsgBox(msg, 0, "Notice")
Else
      Lead1.dbMovePrev
      If Lead1.dbIsBOF = True Then
         Lead1.SetFocus
         RPrev.Enabled = False
         Lead1.dbMoveNext
         msg = "Already at first record."
         answer = MsgBox(msg, 0, "Notice")
      Else
         RNext.Enabled = True
      End If
      Lead1.ForceRepaint
End If

31. For the RLast button, add the following code to the click procedure:

Dim msg, answer
If Lead1.dbIsBOF = True And Lead1.dbIsEOF = True Then
    msg = "The database is empty."
    answer = MsgBox(msg, 0, "Notice")
    RNext.Enabled = False
    RPrev.Enabled = False
Else
    Lead1.dbMoveLast
    RNext.Enabled = False
    RPrev.Enabled = True
    Lead1.ForceRepaint
End If

32. Add the following code to the main form's unload procedure:

Lead1.dbClose

33. $image\btncmpl.gif$ Click the compile button on the toolbar; then close the code window.

34. Close the form designer, saving the changes.

35. With Form1 selected in the Database window, click the Open button to test the form.

To add a file to the database, specify its path name in the Combo Box.