Using ODBC to Access Image Data (Access 95 and 97)

This lesson describes how to use the LEADTOOLS ODBC features to maintain images in a table with other data. In this lesson, you accomplish the following:

Create a database with one table containing images and names for the images.
Create an ODBC data source that references the table.
Use the Access form's default behavior to manage the table and to access the names.
Use the LEADTOOLS methods to access the images through the ODBC data source.
Use SQL SELECT statements to maintain a sorted view of the records.
Synchronize the record pointers so that the two views of the same table are always correct.

1. Start Microsoft Access 95 or 97.

2. Select Blank Database, and click OK.

3. Specify leadpic.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 two fields as follows:

a. Field Name: who. Data Type: text. Size: 255.

b. Field name: photo. Data Type: OLE Object.

7. Use the Edit pulldown menu to specify who as the primary key.

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

Table Name: people

9. In Access 97 only, close the database, and start your project with another name. (Otherwise, an ODBC error will occur when trying to open the table.)

10. In the Database window, select the Queries tab, and click the New button.

11. Create Query1 as follows. (You can select View SQL to enter the query directly.)

SELECT who FROM people ORDER BY who

12. In Access 97 only, specify the leadpic.mdb as the data source in the query's property table.

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

14. In the New Form window, select Design View, specify Query1 as the data source, and click OK.

15. On the Insert pull-down menu, use the Custom Control option (in Access 97, the ActiveX Control option) to select the Lead Control (12).

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

17. Size and position the control as you want it to appear in your application.

18. In the Properties box for the LEAD control, make the following changes:

a. Set the Border Style property to solid.

b. Set the Name property to Lead1.

19. Add a TextBox control to your form and change its properties as follows:

a. Set the Control Source property to who.

b. Set the Name property to Cwho.

20. Add a CommonDialog OLE control to your form and change its name to CommonDialog1. (If your system does not have a CommonDialog control, you can use any valid way of getting a file name.)

21. $image\btncmd.gif$ Add three command buttons to your form and name them as follows. (Cancel the Command Button Wizard when it appears.)

Name	Caption
AddPhoto	Add Photo
DeleteRecord	Delete Record
FlipPhoto	Flip Photo

22. Close Access, and from the Windows Control Panel, create the ODBC data source as follows:

a. Open the ODBC32 administrator and click the Add button.

b. Select Microsoft Access driver and click the OK (or Finish) button.

c. Enter the name LEADAC32.

d. Select the path to the database that you created.

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

23. Restart Access, open your project, and add the following code to your form's general declarations.

' The Windows Sleep function lets us synchronize requeries on adds and deletes
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

' These are variables for implementing the dbMove method
Public OldPosition, NewPosition
Public NormalMove, OnLastRecord

24. Add the following code to the form's Load procedure.

On Error GoTo ERRORHANDLER

' Declare a local variable
Dim ODBCString, SQLString

  ' Turn off automatic scrolling and repainting
  Lead1.AutoScroll = False
  Lead1.AutoRepaint = False

  ' Open the ODBC database.
  ' Use the same SELECT statement that is used for the data control.
  ' Note that the image field must be listed first in the SELECT statement.
  ODBCString = "ODBC;DSN=LEADAC32"
  SQLString = "SELECT photo FROM people ORDER BY who"
  Lead1.dbOpen ODBCString, SQLString, "photo", DB_OPENOPTIONS_NONE

  ' Set the LEAD control's database properties
  Lead1.dbLoadBits = 0
  Lead1.dbLockingMode = DB_LOCKINGMODE_OPTIMISTIC

  ' Enable use of the dbMove method in the form's Current event
  NewPosition = 1
  If (Lead1.dbIsBOF And Lead1.dbIsEOF) = False Then
      NormalMove = True
  End If
Exit Sub
ERRORHANDLER:
MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description

25. Add the following code to the form's Unload procedure.

Lead1.dbClose

26. Add the following code to the AddPhoto button's Click event:

On Error GoTo Err_AddPhoto_Click

' Declare a local variable
Dim Myfile

' Add a record
DoCmd.GoToRecord , , acNewRec

  ' Get an image file
  CommonDialog1.Filter = "Grapics|*.cmp; *.jpg; *.jff; *.jtf; *.bmp; *.tif; *.tga; *.pcx; *.cal; *.mac; *.mac; *.img; *.msp; *.wpg; *.wpg; *.ras; *.pct; *.pcd; *.eps; *.wmf"
  CommonDialog1.Flags = cdlOFNFileMustExist
  CommonDialog1.DialogTitle = "Open File"
  CommonDialog1.CancelError = True
  CommonDialog1.ShowOpen
  Myfile = CommonDialog1.FileName

' Suspend use of the dbMove method in the form's Current event
NormalMove = False

'Set the pointer to an hourglass
DoCmd.Hourglass True

' Hide the LEAD control to avoid unnecessary repaints
Lead1.Visible = False

  ' Update the record and do a requery without inserting the image
  Cwho = Myfile
  Me.Requery
  NewPosition = 1

' Wait for the update to take effect
Call Sleep(5000)

  ' Requery the LEAD control's recordset and make sure it is
  ' synchronized with the data control.
  Lead1.dbRequery
  NormalMove = True ' Do normal record synchronization
  DoCmd.GoToRecord , , acLast
  If (Lead1.dbCurrentRecord + 1) <> Me.CurrentRecord Then
    MsgBox "Synchronization error!" + Chr(13) + "Delete the record and exit"
    DoCmd.FindRecord Myfile, , , , , acCurrent
    DoCmd.Hourglass False
    Exit Sub
  End If

  ' Go to the new record, and show the LEAD control
  DoCmd.FindRecord Myfile, , , , , acCurrent
  Lead1.Visible = True

  ' Load the image file and update the recordset
  Lead1.dbEdit
  Lead1.Load Myfile, 0, 0, 1

  ' Update the recordset using the appropriate format
  If Lead1.BitmapBits = 1 Then
    Lead1.dbUpdate FILE_Lead1BIT, 1, 0
  ElseIf Lead1.BitmapBits = 4 Then
    Lead1.dbUpdate FILE_PCX, 4, 0
  ElseIf Lead1.IsGrayscale = GRAY_NO Then
    Lead1.dbUpdate FILE_CMP, 24, QFACTOR_QMS
  Else 'save as grayscale
    Lead1.dbUpdate FILE_CMP, 8, QFACTOR_QMS
  End If

  ' Set the mouse pointer back to the default
  DoCmd.Hourglass False
  Exit Sub

Err_AddPhoto_Click:
  DoCmd.Hourglass False
  ' Handle the duplicate record error
  If Err.Number = 3022 Then
    MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description
    Cwho = "Let's delete this record"
    MsgBox "Answer yes when prompted to delete this record, which you added by mistake."
    DeleteRecord_Click
  Else
    MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description
  End If

27. Add the following code to the DeleteRecord button's Click event:

On Error GoTo Err_DeleteRecord_Click

' Declare local variable
Dim RecMarker As Long

  ' Disable the normal record synchronization and hide the LEAD control.
  NormalMove = False
  Lead1.Visible = False

'Set the pointer to an hourglass
DoCmd.Hourglass True

' Save the current position
RecMarker = Me.CurrentRecord

  ' Delete the record and wait for the deletion to take effect
  DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
  Call Sleep(5000)

  ' Requery the recordsets, move to the last record,
  ' and initialize the NewPosition global variable.
  Me.Requery
  Lead1.dbRequery

  If Lead1.dbIsBOF And Lead1.dbIsEOF Then
    MsgBox "Database is empty"
    DoCmd.Hourglass False
    NormalMove = True
    Lead1.Visible = True
    Exit Sub
  End If

  DoCmd.GoToRecord , , acLast
  NewPosition = Me.CurrentRecord
  Lead1.dbMove NewPosition - 1

  ' Make sure the LEAD control is on the last record.
  Lead1.dbMoveNext
  If Lead1.dbIsEOF Then
    Lead1.dbMovePrev
  Else
    MsgBox "Synchronization error!" + Chr(13) + "Restart the application"
    Exit Sub
  End If

  ' Return to the old record position, if possible.
  NormalMove = True
  If RecMarker < NewPosition Then
    OnLastRecord = False
    ' The LEAD control is made visible in the form's Current procedure
    DoCmd.GoToRecord , , acGoTo, RecMarker
  Else
    Lead1.Visible = True
    Lead1_Change
  End If

  'Set the mouse pointer back to the default
  DoCmd.Hourglass False
  Exit Sub

Err_DeleteRecord_Click:
  DoCmd.Hourglass False
  NormalMove = True
  MsgBox Err.Description

28. Add the following code to the FlipPhoto button's Click event:

On Error GoTo ERRORHANDLER
  ' Flip the image and update the record
  Lead1.Flip
  Lead1.dbEdit

Exit Sub
ERRORHANDLER:
MsgBox Err.Source + " " + CStr(Err.Number) + Chr(13) + Err.Description

29. Add the following code to the Lead1 control's Change event:

' Declare local variables
Dim HeightAllowed, WidthAllowed, DisplayTop, DisplayWidth, DisplayHeight, DisplayLeft

' Avoid processing events that occur before the bitmap is fully loaded
If Lead1.Bitmap = 0 Or Lead1.Visible = False Then Exit Sub

' Calculate the display rectangle to fit the image inside the control
HeightAllowed = Lead1.ScaleHeight
WidthAllowed = Lead1.ScaleWidth
If (HeightAllowed * Lead1.BitmapWidth / Lead1.BitmapHeight) <= WidthAllowed Then
  DisplayTop = 0
  DisplayHeight = HeightAllowed
  DisplayWidth = DisplayHeight * Lead1.BitmapWidth / Lead1.BitmapHeight
  DisplayLeft = (Lead1.ScaleWidth - DisplayWidth) / 2
Else
  DisplayLeft = 0
  DisplayWidth = WidthAllowed
  DisplayHeight = DisplayWidth * Lead1.BitmapHeight / Lead1.BitmapWidth
  DisplayTop = (Lead1.ScaleHeight - DisplayHeight) / 2
End If

'Set the image display size and paint the image
Lead1.SetDstRect DisplayLeft, DisplayTop, DisplayWidth, DisplayHeight
Lead1.SetDstClipRect DisplayLeft, DisplayTop, DisplayWidth, DisplayHeight
Lead1.ForceRepaint

30. Add the following code to the Form's Current event:

' Use the dbMove method to move the LEAD control to the current record
If NormalMove Then
  ' The current position is the NewPosition
  ' The OldPosition is the previous position
  OldPosition = NewPosition
  NewPosition = Me.CurrentRecord
  Lead1.Visible = True
  Lead1.dbMove NewPosition - OldPosition
End If

31. Run your program to test it.

Note: For 256-color mode, you can improve the image quality as explained in Palette Usage in Microsoft Access.