Memunculkan Field Gambar(OLE Object) Di Database Acces

Sedikiit berbagi lagi.. Mungkin Teman2 ada kesulitan mengenai memunculkan file Image Database Acces ke VB. Saya menggunakan dua Cara untuk mewujudkan hal tersebut:

1. Me-Load Gambar dalam Kontrol Image dari String Alamat Gambar yang telah ada di Field Database, jadi Field dalam tabel bertype Text. Keuntungannya Database berukuran lebih Kecil dibanding cara kedua.
2. Meload Gambar Dari Field yang bertype OLE Object kedalam kontrol Image. Keuntungannya semua gambar dalam satu database sehingga dapat sebagai pustaka gambar.

Kali ini kita akan menggunakan cara yang kedua, Buat Database Acces bernama "BioData.mdb" terdiri atas satu Tabel yaitu "TblPhotoSaja" dan di dalamnya buat field Type Ole Object beri nama "Image",serta satu field lagi Bertype AutoNumber berinama "Id", Langsung saja ^_^ ini saourcenya..

Copy Paste Code Dibawah Ini Dalam form

Option Explicit
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Declare Function lOpen Lib "kernel32" Alias "_lopen" (ByVal lpPathName As String, ByVal iReadWrite As Long) As Long
Private Declare Function lclose Lib "kernel32" Alias "_lclose" (ByVal hFile As Long) As Long
Private Declare Function GetFileSize Lib "kernel32" (ByVal hFile As Long, lpFileSizeHigh As Long) As Long
Private lpFSHigh As Long
Private strfilepath As String
Private Buffer As String
Private Const OF_READ = &H0&
Private db As ADODB.Connection
Private WithEvents adoPrimaryRSImageName As Recordset

Private Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    Flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Private Function GetFile(ByRef frm As Form) As String
    Dim OFName As OPENFILENAME
    OFName.lStructSize = Len(OFName)
    'Set the parent window
    OFName.hwndOwner = frm.hWnd
    'Set the application's instance
    OFName.hInstance = App.hInstance
    'Select a filter
    OFName.lpstrFilter = "Bitmap (*.bmp)" + Chr$(0) + _
      "*.bmp" + Chr$(0) + _
      "Jpg (*.jpg)" + Chr$(0) + _
      "*.jpg" + Chr$(0) + _
      "Icons (*.ico)" + Chr$(0) + _
      "*.ico" + Chr$(0) + _
      "Windows Metafiles (*.wmf)" + Chr$(0) + _
      "*.wmf" + Chr$(0) + _
      "Jpeg (*.jpeg)" + Chr$(0) + _
      "*.jpeg" + Chr$(0) + _
      "Gif (*.gif)" + Chr$(0) + _
      "*.gif" + Chr$(0) + _
      "All Files (*.*)" + Chr$(0) + _
      "*.*" + Chr$(0)
    'create a buffer for the file
    OFName.lpstrFile = Space$(254)
    'set the maximum length of a returned file
    OFName.nMaxFile = 255
    'Create a buffer for the file title
    OFName.lpstrFileTitle = Space$(254)
    'Set the maximum length of a returned file title
    OFName.nMaxFileTitle = 255
    'Set the initial directory
    'OFName.lpstrInitialDir = "C:\" 'Commented so that the box opens on the last directory browsed
    'Set the title
    OFName.lpstrTitle = "Open Dialog Box"
    'No flags
    OFName.Flags = 0
    'Show the 'Open File'-dialog
    If GetOpenFileName(OFName) Then
      GetFile = Trim$(OFName.lpstrFile)
    Else
      GetFile = ""
    End If
End Function

Private Sub SaveBitmap(ByRef adoRS As ADODB.Recordset, ByVal strField As String, ByVal SourceFile As String)
    'This sub copies the actual file into a byte array.
    'This byte array is then used as the value for
    'the field having an image data type
    Dim Arr() As Byte
    Dim Pointer As Long
    Dim SizeOfThefile As Long
    Pointer = lOpen(SourceFile, OF_READ)
    'size of the file
    SizeOfThefile = GetFileSize(Pointer, lpFSHigh)
    lclose Pointer
    'Resize the array, then fill it with
    'the entire contents of the field
    ReDim Arr(SizeOfThefile)
    Open SourceFile For Binary Access Read As #1
    Get #1, , Arr
    Close #1
    adoRS(strField).Value = Arr
    Exit Sub
End Sub

Private Sub cmdFirst_Click()
If adoPrimaryRSImageName.BOF And adoPrimaryRSImageName.RecordCount = 0 Then
   MsgBox "Anda Tidak Memiliki Data Record, Klik Tombol " & """" & "Tambah Record" & """" & " untuk Membuat Record."
Else
  adoPrimaryRSImageName.MoveFirst
End If
End Sub

Private Sub cmdLast_Click()
If adoPrimaryRSImageName.EOF And adoPrimaryRSImageName.RecordCount = 0 Then
   MsgBox "Anda Tidak Memiliki Data Record."
Else
    adoPrimaryRSImageName.MoveLast
End If
End Sub

Private Sub cmdNext_Click()
If adoPrimaryRSImageName.EOF And adoPrimaryRSImageName.RecordCount = 0 Then
   MsgBox "Anda Tidak Memiliki Data Record."
End If
If Not adoPrimaryRSImageName.EOF Then adoPrimaryRSImageName.MoveNext
If adoPrimaryRSImageName.EOF And Not adoPrimaryRSImageName.RecordCount = 0 Then
    Beep
    adoPrimaryRSImageName.MoveLast
End If
End Sub

Private Sub cmdPrevious_Click()
If adoPrimaryRSImageName.BOF And adoPrimaryRSImageName.RecordCount = 0 Then
   MsgBox "Anda Tidak Memiliki Data Record"
End If
If Not adoPrimaryRSImageName.BOF Then adoPrimaryRSImageName.MovePrevious
If adoPrimaryRSImageName.BOF And Not adoPrimaryRSImageName.RecordCount = 0 Then
    Beep
    adoPrimaryRSImageName.MoveFirst
End If
End Sub

Private Sub CariSimpanImage_Click()
    strfilepath = GetFile(Me)
    If strfilepath <> "" Then
        Image1.Picture = LoadPicture(strfilepath)
        SaveBitmap adoPrimaryRSImageName, "Image", strfilepath
    End If
End Sub

Sub Koneksi()
    Set db = New ADODB.Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & App.Path & "\BioData.mdb"
    Set adoPrimaryRSImageName = New ADODB.Recordset
    adoPrimaryRSImageName.Open "TblPhotoSaja", db, adOpenDynamic, adLockOptimistic
    Set Image1.DataSource = adoPrimaryRSImageName
End Sub

Private Sub Form_Load()
    Koneksi
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set db = Nothing
    Set adoPrimaryRSImageName = Nothing
    Unload Me
End Sub

Semoga bermanfaat.. ^_^

Serpihan Kode Database Access

Pertama yang perlu disapkan adalah :

• Nama Database : DBPembelajaran.mdb format Microsoft Office Access 2000

• Nama Tabel : SiswaLogin

• Nama Field dalam Tabel SiswaLogin : Nama Field Nama_Siswa TypeField Text dan field kedua   Nama Field NIS TypeField Text

• Klik Menu Project Pilih References.. : Microsoft ActiveX Data Object 2.0 Library atau versi yang lebih tinggi.

Dibawah ini serpihan kode yang mungkin bermanfaat, silahkan...
1. a. Koneksi Dengan Database Yang Tidak Berpassword

Option Explicit
Dim db As ADODB.Connection
Dim adoPrimaryRSLoginSiswa As ADODB.Recordset

Private Sub Form_Load()
On Error GoTo err
        Set db = New ADODB.Connection
        db.CursorLocation = adUseClient
        db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & App.Path & "\DBPembelajaran.mdb;"
err:
        If db.State = 1 Then
            MsgBox "Terkoneksi dengan database"
        ElseIf db.State = 0 Then
            MsgBox "Tidak Terkoneksi dengan database.", vbInformation, "Error"
        End If
End Sub
 

1. b. Koneksi Dengan Database Berpassword

Private Sub Form_Load()
On Error GoTo ERR
        Dim DBBerPassword
        Set DBBerPassword = New ADODB.Connection
        DBBerPassword.CursorLocation = adUseClient
        DBBerPassword.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\DBPembelajaran - Copy.mdb" & ";Persist Security Info=False;Mode=12;Jet OLEDB:Database Password=TulisPasswordnya"
ERR:
        If DBBerPassword.State = 1 Then
            MsgBox "Terkoneksi dengan database"
        ElseIf DBBerPassword.State = 0 Then
            MsgBox "Tidak Terkoneksi dengan database.", vbInformation, "Error"
        End If
End Sub

2. Buka Record

Private Sub Command1_Click()
On Error GoTo err
        Set adoPrimaryRSLoginSiswa = New ADODB.Recordset
        adoPrimaryRSLoginSiswa.Open "TblSiswaLogin", db, adOpenStatic, adLockOptimistic
err:
        If adoPrimaryRSLoginSiswa.State = 1 Then
            MsgBox "Terkoneksi dengan Tabel"
        ElseIf adoPrimaryRSLoginSiswa.State = 0 Then
            MsgBox "Tabel tidak ditemukan, cek kembali tabel yang ada dalam database.", vbInformation, "Error"
        End If
End Sub

3. Cek Isi Field

Private Sub Command2_Click()
    adoPrimaryRSLoginSiswa.MoveFirst
    MsgBox "NAMA FIELD : " & adoPrimaryRSLoginSiswa.Fields(0).Name & _
    vbCrLf & "ISI FIELD RECORD PERTAMA : " & adoPrimaryRSLoginSiswa.Fields(0).Value, vbInformation
End Sub

4. Menghubungkan Isi Field Ke Control

Private Sub Command3_Click()
    Set Me.Text1.DataSource = adoPrimaryRSLoginSiswa
    Set Me.Text2.DataSource = adoPrimaryRSLoginSiswa
    
    Me.Text1.DataField = "NAMA_SISWA"
    Me.Text2.DataField = "NIS"
    
End Sub

5. Mengecek Field Kosong (IsNull)

Private Sub Command4_Click()
    'DI PROPERTY Text3 MultiLine pilih True
    'DI PROPERTY Text3 ScrollBars pilih 3
    Text3.Text = "MENGECEK FIELD NIS KOSONG"
    adoPrimaryRSLoginSiswa.MoveFirst
    While Not adoPrimaryRSLoginSiswa.EOF
    If IsNull(adoPrimaryRSLoginSiswa.Fields("NIS")) = True Then
        Text3.Text = Text3.Text & vbCrLf & "NO : " & adoPrimaryRSLoginSiswa.AbsolutePosition & ". " & adoPrimaryRSLoginSiswa.Fields("NAMA_SISWA").Value & " KOSONG"
    ElseIf IsNull(adoPrimaryRSLoginSiswa.Fields("NIS")) = False Then
        Text3.Text = Text3.Text & vbCrLf & "NO : " & adoPrimaryRSLoginSiswa.AbsolutePosition & " TIDAK KOSONG "
    End If
        adoPrimaryRSLoginSiswa.MoveNext
    Wend
End Sub

6. Navigasi

Private Sub Command5_Click()
    If adoPrimaryRSLoginSiswa.AbsolutePosition = 1 Or adoPrimaryRSLoginSiswa.RecordCount = 0 Then
        Beep
    Else
        adoPrimaryRSLoginSiswa.MoveFirst 'Ke record Pertama
    End If
    Me.Label2.Caption = "NO. " & adoPrimaryRSLoginSiswa.AbsolutePosition
End Sub

Private Sub Command6_Click()
    If adoPrimaryRSLoginSiswa.AbsolutePosition = 1 Or adoPrimaryRSLoginSiswa.RecordCount = 0 Then
        Beep
    Else
        adoPrimaryRSLoginSiswa.MovePrevious "Ke record Sebelumnya    End If
    Me.Label2.Caption = "NO. " & adoPrimaryRSLoginSiswa.AbsolutePosition
End Sub

Private Sub Command7_Click()
    If adoPrimaryRSLoginSiswa.AbsolutePosition = adoPrimaryRSLoginSiswa.RecordCount Or adoPrimaryRSLoginSiswa.RecordCount = 0 Then
        Beep
    Else
        adoPrimaryRSLoginSiswa.MoveNext 'Ke record Selanjutnya    End If
    Me.Label2.Caption = "NO. " & adoPrimaryRSLoginSiswa.AbsolutePosition
End Sub

Private Sub Command8_Click()
    If adoPrimaryRSLoginSiswa.AbsolutePosition = adoPrimaryRSLoginSiswa.RecordCount Or adoPrimaryRSLoginSiswa.RecordCount = 0 Then
        Beep
    Else
        adoPrimaryRSLoginSiswa.MoveLast 'Ke record Terakhir    End If
    Me.Label2.Caption = "NO. " & adoPrimaryRSLoginSiswa.AbsolutePosition
End Sub

6. Mendapatkan Tabel Dalam database

Private Sub Command9_Click()
Dim NamaTabel As ADODB.Recordset
Set NamaTabel = db.OpenSchema(adSchemaTables)
    While Not NamaTabel.EOF
        If NamaTabel!TABLE_TYPE = "TABLE" Then Text4.Text = Text4.Text & vbCrLf & NamaTabel!TABLE_NAME
        NamaTabel.MoveNext
    Wend
End Sub

7. Mendapatkan Field Dalam Tabel

Private Sub Command10_Click()
Dim Column As ADODB.Field
If adoPrimaryRSLoginSiswa.State = adStateOpen Then
    For Each Column In adoPrimaryRSLoginSiswa.Fields
        Text5.Text = Text5.Text & vbCrLf & Column.Name
    Next
End If
End Sub

8. Membuat Tabel - Create Table

Private Sub Command11_Click()
    Dim Cmd As New ADODB.Command
    Cmd.ActiveConnection = db
    Cmd.CommandText = "create table TabelBaru (NAMA_SISWA varchar(20), KELAS varchar(5), TENTANG_SISWA LongChar, Foto LongBinary)"
    Cmd.Execute
End Sub

9. Menambahkan Field Di Tabel Yang Sudah Ada - Add Field In Exists Table

Private Sub Command12_Click()
'Tambahkan references Microsoft ADO Ext. 2.1 for DDL and Security atau versi lebih tinggi
    Dim Xconx As ADODB.Connection
    Dim Xcmd As ADODB.Command
    Dim Xrs As ADODB.Recordset
    Dim m_MDBdatabase As String
    Dim m_MDBtable As String

'Tambahkan columns di tabel yang sudah ada
    Dim ADOXcat As ADOX.Catalog
    Dim MStbl As ADOX.table
    Dim MScol As ADOX.Column
    
    m_MDBdatabase = App.Path & "\DBPembelajaran.mdb"
    m_MDBtable = "TblSiswaLogin"

'Membuat koneksi
    Set Xconx = New ADODB.Connection
    Set Xcmd = New ADODB.Command
    Set Xrs = New ADODB.Recordset
    Set Xconx = CreateObject("ADODB.Connection")
    Xconx.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Persist Security Info=False;" & _
    "Data Source=" & m_MDBdatabase
    Set Xrs = CreateObject("ADODB.Recordset")
    Xrs.CursorLocation = adUseServer

'Mengirimkan MDB dan table ke catalog
    Set ADOXcat = New ADOX.Catalog
    ADOXcat.ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & m_MDBdatabase
    Set MStbl = ADOXcat.Tables(m_MDBtable)

'Menambahkan columns/Field ke tabel yang ada
    MStbl.Columns.Append "NILAI", adDouble
    MStbl.Columns.Append "KETERANGAN", adVarWChar, 255
    MStbl.Columns.Append "TANGGAL_LAHIR", adDate
    
'Bersihkan
    ADOXcat.ActiveConnection.Close
    Set ADOXcat = Nothing
    Set MStbl = Nothing
    Set MScol = Nothing
    Set Xconx = Nothing
    Set Xcmd = Nothing
    Set Xrs = Nothing
End Sub

10. Hapus Semua Record Dalam Tabel

Private Sub Command13_Click()
    db.Execute "DELETE FROM TBLsiswalogin"
End Sub

11. Hapus Tabel

Private Sub Command14_Click()
'Tambahkan references Microsoft DAO 3.6 Object Library atau versi lebih tinggi
    Dim ConMateri As Database, AdoDao%
    Set ConMateri = OpenDatabase(App.Path & "\DBPembelajaran.MDB", False, False, "MS Access;Pwd=dbpwd")
    Dim TbDef As TableDefs
    Set TbDef = ConMateri.TableDefs
    ConMateri.TableDefs.Delete "NamaTabelYangAkanDiHapus"
End Sub

Rename File Atau Folder

Hai... Ini ada sedikit Source Code untuk Rename File Folder, Posting ini termotivasi dari Pertanyaan Anggota Grup Programer VB Indonesia.
Langsung saja ini codenya, Semoga bermanfaat.

Private Declare Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long
Private Type SHFILEOPSTRUCT
    hwnd As Long
    wFunc As Long
    pFrom As String
    pTo As String
    fFlags As Integer
    fAborted As Boolean
    hNameMaps As Long
    sProgress As String
End Type
 Const FO_DELETE = &H3
 Const FO_RENAME = &H4
 Const FO_COPY = &H2

Sub RenameFile()
Dim NewFileName As String
Dim TitleFile
Dim PathFile
'On Error Resume Next
TitleFile = "Baru"
PathFile = "D:\Lama"
NewFileName = InputBox("Are you sure want rename File " & """" & TitleFile & """?", _
        "Rename File", PathFile)
Dim SHDirOp As SHFILEOPSTRUCT
    With SHDirOp
        .wFunc = FO_RENAME
        .pFrom = "D:\BARU"
        .pTo = NewFileName
    End With
SHFileOperation SHDirOp
End Sub

Private Sub Form_Load()
    RenameFile
End Sub