Extracting Audio from Access Database

  • Thread starter Thread starter sontaranleader
  • Start date Start date
S

sontaranleader

Hi

I wonder if any of you guys can help. I have been given the task at
work which Im struggling with...

We have a database which is getting large due to records with a field
of audio files (held as an ole object). I have been asked to extract
all the audio files from the database and store them on a seperate
place on the server.

I was wondering is there was a quick and easy method of extracting the
audio files from the table. There are about 80,000 records.

Can anyone help??
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Probably just run a query that gets the ole object, probably a BLOB, and
writes it to a file using VBA's WRITE # command.  A problem is you have
to know the file'saudiotype: mp3, aud, ogg, etc.  Also, media files
usually have header bytes that identify things about the file that are
required for the file to run.  Do you know if your OLE objects have
those bytes?  Probably the only way to find out is save one & try to
play it.

You'll need a query to get the OLE object (let's name it GetAudio):

SELECT file_name, ole_object
FROM table_name
WHERE file_name IS NOT NULL

Be sure to change the table & column names to match your table's &
columns' names.

Then some VBA routines to iterate through the query's recordset and
write the data to the files (that's why I included the file_name in the
query).

NOTE: These routines are untested!

Sub SaveAudioFiles()

   ' The PATH is the path to the folder where the files will
   ' be saved.  Change to suit your needs.
   Const PATH = "C:\myAudioFiles\"

   ' This is the query we created & saved, above
   Const QRY = "GetAudio"

   ' This is the file extension - change to whatever file type
   ' you're saving the file.
   Const EXT = ".mp3"

   On Error GoTo err_

   Dim db As DAO.Database, qd As DAO.QueryDef, rs As DAO.Recordset

   ' Get the recordset
   Set db = CurrentDb
   Set qd = db.QueryDefs(QRY)
   Set rs = qd.OpenRecordset()

   Do While NOT rs.EOF
     SaveAudio PATH & rs!file_name & EXT, rs!ole_object

' For debug purposes uncomment the following line
' Just one file will be saved.

'   Exit Do

     rs.MoveNext
   Loop

   ' If got here, then no errors
   MsgBox "Completed", vbInformation, "SaveAudioFiles"

exit_:
   ' clean up & exit
   On Error Resume Next
   Set rs = Nothing
   Set qd = Nothing
   Set db = Nothing
   Exit Sub

err_:
   ' You'll probably want a better Error Handler than this....
   MsgBox "An Error Occurred While SavingAudioFiles" & vbCrLf & _
     "Error: " & Err.Description, vbExclamation
   Resume exit_

End Sub

Sub SaveAudio(ByVal strFileName As String, obj As Object)
' Save oneaudiofile

' If error, it is returned to the calling routine

   Dim fd As Integer

   ' Get the next available file buffer
   fd = FreeFile

   ' Open a new file & save the data
   Open strFileName For BinaryAccessWrite As fd

   Write #fd, obj

   Close fd

End Sub

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSd4sZIechKqOuFEgEQKm9wCdEArXBKwpbw+xYpY0nC6hQraSbAoAoJoF
YQzyezm8ZW4F02NoFwBk9ZvM
=fzQk
-----END PGP SIGNATURE-----
Click to expand...

Thanks for you help. I tried this rotine but an error message 'bad
file mode' is displayed at the pont of writing the file, so THIS line
basically..

Open strFileName For Binary Access Write As fd

Any ideas??
 
Thanks for you help. I tried this rotine but an error message 'bad
file mode' is displayed at the pont of writing the file, so THIS line
basically..

Open strFileName For BinaryAccessWrite As fd

Any ideas??- Hide quoted text -

- Show quoted text -
Click to expand...


Additonal information for you, the files are definately .wav (i have
amended your code to denote this)
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hmmm... for some reason the words got smooshed together.  It should be:

   Open strFileName For BinaryAccessWrite As fd

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSed4MIechKqOuFEgEQKjrACfShn+HLrgMuXWNugEQN+GVkf5OgkAnife
MLet9bDtW2oaK2xUMUOVLhQb
=VEMy
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -
Click to expand...


Thanks for the prompt reply. Still 'bad file mode' :( . Isnt the
correction the same as the original line?

regards
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

No, for some reason the spaces between the words are being removed by
your newsreader or something else, 'cuz, looking at my post on the
Google archive, the words are spaced correctly.  I'll put the word
"{space}" between the words and you substitute a real space when you get
this post.  The following should be all on one line.

   Open{space}strFileName{space}For{space}Binary{space}
     Access{space}Write{space}As{space}fd

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSehgXoechKqOuFEgEQKY6QCgiKaSI9L+sVFQKLuL69/nF0NKrToAoMaT
AgYM1/+dM4iWrsLwQsk6am1C
=Nq4s
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -
Click to expand...

Yeah thats what I'm seeing and implementing but no, still 'bad file
mode'.

Should we be looking at a 'get' 'put' methodology for extracting
binary into a file? Jus thoughts

Anyway, lots of help so far and thanks for that but so far so not
good :(
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Like I said "Untested code" ;-).  You're correct about the Get & Put.
Let's try this (I got most of this info from an Images.mdb example I
found on the Internet years ago. Unfortunately, I can't remember who
wrote it.  I believe it may have been Lyle Fairfield.).  Replace the sub
routine with the following:

Sub SaveAudio(ByVal strFileName As String, obj As DAO.Field)
  ' Save one audio file
  ' Errors are returned to the calling routine

     On Error GoTo err_

     Const BLK_SZ = 32768

     Dim lngFileLen as Long, lngLeftOver as Long, intBlocks As Integer
     Dim fd As Integer, varFileData As Variant, i As Integer

     ' Show user we're working
     DoCmd.Hourglass True

     ' Get the size of output
     lngFileLen = obj.FieldSize

     ' Calculate number of blocks to write and leftover bytes.
     intBlocks = lngFileLen\BLK_SZ
     lngLeftOver = lngFileLen Mod BLK_SZ

     ' Get the next available file buffer
     fd = FreeFile

     ' Open a new file & save the data
     Open strFileName For Binary As fd
     varFileData = obj.GetChunk(0, lngLeftOver)
     Put fd, , varFileData

     If lngLeftOver > 0 Then
       ' Write the remaining blocks of data to the output file.
       For i = 1 To intBlocks
           ' Reads a chunk and writes it to output file.
           varFileData = obj.GetChunk((i - 1) * BLK_SZ _
              + lngLeftOver, BLK_SZ)
           Put fd, , varFileData
       Next i
     End If

     Close fd

exit_:
     DoCmd.Hourglass False
     Exit Sub

err_:
     DoCmd.Hourglass False
     ' clean up & return error to calling routine
     Dim lngErr as Long: strErr As String
     lngErr = Err.Number: strErr = Err.Description
     On Error Resume next
     close fd
     On Error GoTo 0
     Err.Raise lngErr,,strErr

End Sub

Again, I didn't test this, so it would be a good idea to step through
the code a few times to see if it works OK.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup.  I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSekJSYechKqOuFEgEQJgKACg3kM9KlEvVCDr+eDYVwdctcpfVaIAoLoM
59tsuEISuzA/iKvvPjktbzYv
=4cX0
-----END PGP SIGNATURE------ Hide quoted text -

- Show quoted text -
Click to expand...

thanks again for the prompt reply. will give this a go when i get back
to the office on Monday. excited to see if it will work! will keep you
posted
 
thanks again for the prompt reply. will give this a go when i get back
to the office on Monday. excited to see if it will work! will keep you
posted- Hide quoted text -

- Show quoted text -
Click to expand...


So close! File was created and had content as denoted by its size
being 17kb. Got a bit excited but then when I tried to play the file
(a wav file) it run Windows Media Player with the message

"Windows Media Player cannot play the file. The Player might not
support the file type or might not support the codec that was used to
compress the file."

...which kinda sudjests the content is not correct

:( shame
 
Yeah, that's what I was thinking when I said there may be header bytes
that identify theaudiotype that are not stored in the record, but
would be required by the file.

Regards,
Click to expand...

is there any way of adding the header?
 
Cheers. I will try and dump the contents like this and have a look. I
cant believe its so difficult to extract an audio file from an Access
database. Someone should write and app, they would make a fortune!
(maybe me) :)
 
Back
Top