Need help to ...audio files.

[Margaret]

Dear Friends,

I got a sample code to store pictures in MS Access Database. I found it in Si_the_geek's signature (http://www.vbforums.com/showthread.php?t=376767) In that sample, there is an imagebox to show the pictures. Now, I have created a database to store audio files similar to this project. But don't know how to open an audio file (here I don't know which control to put on the form instead of imagebox)?

The following is the code where I need help (the line is in BOLD)

Public Sub FillFields()
    If Not (rs.BOF And rs.EOF) Then
        txtDescription.Text = rs.Fields("Description")
        [B]Set Image1.DataSource = rs [/B] 'setting image1’s datasource
        Image1.DataField = "Picture" 'set its datafield.
    End If
End Sub

Set Image1.DataSource = rs which control to be used in place of Image1.DataSource

Need help, please.

Please also check the following code:

Private Sub cmdAdd_Click()
    Dim bytData() As Byte
    Dim strDescription As String
    
    On Error GoTo err
    
    With dlgAdd
        .Filter = "Audio Files (*.dat, *.wma)|*.dat;*.wma"
        .DialogTitle = "Select Audio File"
        .ShowOpen
           
        Open .FileName For Binary As #1
        ReDim bytData(FileLen(.FileName))
        End With
      
    Get #1, , bytData
    Close #1
    
    strDescription = InputBox("Enter description.", "New Audio File")
    
    With rs
        .AddNew
        .Fields("Description") = strDescription  'adding record to db
       [B] .Fields("Audio").AppendChunk bytData  [/B] 'adding the audio to the db
        .Update
    End With
    
    FillFields
    Exit Sub
err:
    If err.Number = 32755 Then
    Else
        MsgBox err.Description
        err.Clear
    End If
End Sub

Margaret

[si_the_geek]

I hadn't noticed before (how could I have missed it? ), but the code from the tutorial does not actually extract the file from the database - instead it binds the picture to an Image control.

This wont work for other file types (and is a 'bad' method anyway), so you need some extra code. What you need to do is save the data to a file, and then use the file in whatever way is appropriate (possibly followed by deleting the file you saved).


Here is a sub I have written which saves the data to a file:

Sub SaveFileFromDB(strFileName As String, fldPictureField As ADODB.Field)
'Save data from a OLE/BLOB field to a file
 
Dim intFileNum As Integer
Dim bytChunk() As Byte
 
          'open file for writing
  intFileNum = FreeFile
  Open strFileName For Binary As #intFileNum
          'get data from database
  bytChunk() = fldPictureField.GetChunk(fldPictureField.ActualSize)
          'write data to file
  Put #intFileNum, , bytChunk()
          'close the file
  Close #intFileNum
 
End Sub

..and to use it, put the following code (or similar) in FillFields, replacing both lines that use "Image1":

Call SaveFileFromDB(App.Path & "\temp.tmp", rs.Fields("Picture"))

One thing to note is that in this example I have used a file name that may not work for you (temp.tmp).. for example, the way you play the audio files may need the file to have the correct extension (eg: .wav , or .mp3).

What I would recommend is adding an extra field to the database table, called FileName (with a data type of: Text). When you save data in cmdAdd_Click, set the value of this field to the name of the file (with the extension, but not the path), using code like this:

.Fields("FileName") = MyFileName   'where MyFileName is a variable containing the file name

..you can then re-save the file using the original name, eg:

Call SaveFileFromDB(App.Path & "\" & rs.Fields("FileName").Value, rs.Fields("Picture"))

[si_the_geek]

Did this work for you?

(I'd like to update the FAQ as appropriate, but need to be sure the code is valid first)

[stephen100]

dont work for me. Too many bugs.

[si_the_geek]

Can you show us how you are using it?
(it would be best to upload the code files and database).

[aikidokid]

Hi si_the_geek

I have ammended the code as above shows and it seems to be saving the word document files, but...
how do I then show then when I open them?

I changed temp.tmp to temp.doc.

I have attached the project, but with the mdb the file is too big, so I have shown an image.

[si_the_geek]

Oh my, I've had a bigger delay in replying than I thought! (and possibly too late for you too)

The best method for most file types (unless you have specific code in your app to deal with the file type) is to use ShellExecute as in your thread about this.

Assuming that you used the example from the end of post 2, the code could be like this:

        ShellExecute Me.hwnd, "open", App.Path & "\" & rs.Fields("FileName").Value, vbNullString, "", SW_SHOWNORMAL

[aikidokid]

Oh my, I've had a bigger delay in replying than I thought! (and possibly too late for you too)

No problem si, I know you would get back when you could

I have replied to this in that other thread.

You said you wanted feedback so you might use this as a FAQ.
Well, it all works for me. I tried it with JPEG, GIF and word documents (.doc).

Thanks

[si_the_geek]

Excellent, thank you.