ODBC example for Visual Basic

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

Note: The LEADTOOLS ActiveX requires ODBC drivers that can properly handle long binary fields. examples have been tested and will work with version 3.0 of the Microsoft ODBC Desktop Driver Pack. (This driver pack ships with Access 95 and works on 32-bit systems.) You can download drivers from the LEAD BBS, the CompuServe forum, or the Worldwide Web home page. The CompuServe forum is at GO LEADTECH. The web page is ftp://ftp.leadtools.com/pub/utils/ODBC32.zip. The file to download is ODBC32.ZIP.

1. Make a copy of the project that you created in Loading and Displaying an Image; then load the copied project.

2. In the Add-Ins menu, select the Data Manager...

3. In the Data Manager Window's File menu, select New, and create an Access database named c:\lead\lead.mdb.

4. Click the New button, and create a table with one field as follows:

Name:  ltvbpic
Field Name:  photo
Data Type:  long binary

5. Exit the Data Manager.

6. Open the ODBC administrator and click the Add button.

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

8. Enter the name LEADACCESS.

9. Click the Select directory button and select the LEAD directory.

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

11. In Visual Basic, replace the code in the main form's init procedure as follows:

Private Sub Form_Load()

  'Position and size the main form so that it takes up most of the screen.
  Width = Screen.Width * 0.9
  Height = Screen.Height * 0.8
  Left = (Screen.Width - Width) / 2
  Top = (Screen.Height - Height) / 2
  ' 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 = ScaleHeight * 3 / 4
  WidthAllowed = ScaleWidth * 3 / 4

  Lead1.Left = (ScaleWidth - WidthAllowed) / 2
  Lead1.Top = (ScaleHeight - 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, "ltvbpic", "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")
    Next.Enabled = False
    Prev.Enabled = False
  End If

End Sub

12. Add a common dialog control to your main form and name it Commdlg.

13. At the top of your main form, add seven command buttons and name them as follows: Add, Update, Delete, First, Next, Prev, and Last.

14. For the Add button, add the following code to the click procedure:

If Lead1.dbCanAppend = False Then
    Msg = "You cannot add to this database."
    answer = MsgBox(Msg, 0, "Error")
Else
    Lead1.dbAddNew
    Commdlg.Filter = "All Files|*.*"
    Commdlg.Flags = cdlOFNFileMustExist
    Commdlg.DialogTitle = "Open File"
    Commdlg.CancelError = True
    Commdlg.ShowOpen
    filename = Commdlg.filename
    Lead1.Load filename, 0, 0, 1
    nRet = Lead1.dbUpdate(FILE_CMP, 0, QFACTOR_QMS)
        If nRet <> 0 Then
            Msg = "You cannot update this database."
            answer = MsgBox(Msg, 0, "Error")
        Else
            Lead1.dbMoveLast
            Lead1.ForceRepaint
            Msg = "Image added to the database."
            answer = MsgBox(Msg, 0, "LEAD")
            Next.Enabled = True
            Prev.Enabled = True
            If Lead1.dbIsBOF = True Then
              Prev.Enabled = False
            Else
              Prev.Enabled = True
            End If
            If Lead1.dbIsEOF = True Then
              Next.Enabled = False
            Else
              Next.Enabled = True
            End If
    End If
End If

15. For the Update button, add the following code to the click procedure:

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

16. For the Delete button, add the following code to the click procedure:

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
      Next.Enabled = False
      Prev.Enabled = False
    End If
    Lead1.ForceRepaint
End If

17. For the First button, add the following code to the click procedure:

If Lead1.dbIsBOF = True And Lead1.dbIsEOF = True Then
    Msg = "The database is empty."
    answer = MsgBox(Msg, 0, "Notice")
    Next.Enabled = False
    Prev.Enabled = False
Else
    Lead1.dbMoveFirst
    Prev.Enabled = False
    Next.Enabled = True
    Lead1.ForceRepaint
End If

18. For the Next button, add the following code to the click procedure:

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

19. For the Prev button, add the following code to the click procedure:

If Lead1.dbIsBOF = True And Lead1.dbIsEOF = True Then
    Prev.Enabled = False
    Next.Enabled = False
    Msg = "The database is empty."
    answer = MsgBox(Msg, 0, "Notice")
Else
      Lead1.dbMovePrev
      If Lead1.dbIsBOF = True Then
         Prev.Enabled = False
         Lead1.dbMoveNext
         Msg = "Already at first record."
         answer = MsgBox(Msg, 0, "Notice")
      Else
         Next.Enabled = True
      End If
      Lead1.ForceRepaint
End If
End Sub

20. For the Last button, add the following code to the click procedure:

If Lead1.dbIsBOF = True And Lead1.dbIsEOF = True Then
    Msg = "The database is empty."
    answer = MsgBox(Msg, 0, "Notice")
    Next.Enabled = False
    Prev.Enabled = False
Else
    Lead1.dbMoveLast
    Next.Enabled = False
    Prev.Enabled = True
    Lead1.ForceRepaint
End If

21. Update the main form's unload procedure as follows:

Private Sub Form_Unload(Cancel As Integer)
    Lead1.dbClose
End Sub

22. Run your program to test it.