Extracting Audio from Access Database

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??
 
M

MGFoster

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??
Click to expand...

-----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's audio type: 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, "Save Audio Files"

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 Saving Audio Files" & vbCrLf & _
"Error: " & Err.Description, vbExclamation
Resume exit_

End Sub

Sub SaveAudio(ByVal strFileName As String, obj As Object)
' Save one audio file

' 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 Binary Access Write 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-----
 
S

sontaranleader

-----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??
 
S

sontaranleader

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)
 
M

MGFoster

Additonal information for you, the files are definately .wav (i have
amended your code to denote this)
Click to expand...

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

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

Open strFileName For Binary Access Write 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-----
 
S

sontaranleader

-----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
 
M

MGFoster

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

-----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-----
 
S

sontaranleader

-----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 :(
 
M

MGFoster

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 :(
Click to expand...

-----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-----
 
S

sontaranleader

-----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
 
S

sontaranleader

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
 
M

MGFoster

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
Click to expand...

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

Regards,
 
S

sontaranleader

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?
 
M

MGFoster

is there any way of adding the header?
Click to expand...

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

Well, I'm not positive that's the problem, but, you'd have to look up
that header data in some tech manual (maybe on the Net). Here's some
header bytes I took off a .wav file I have. The numbers on the left are
hexidecimal bytes and the right-hand column is an ASCI translation of
the bytes - each letter represents one byte. If the bytes are not
printable they are represented as a period. My guess is the sound data
starts after the word "data." This dump was produced by a program
called "hd" (hex-dump), an old Unix command. You might find a copy on
the web somewhere. I believe there are some Windows versions available
it might be called something different. I had a Windows version one
time & it let me add bytes to the file. I don't remember where that
program came from; probably one of those free software sites.

chimes.wav
52 49 46 46 d8 d9 00 00 57 41 56 45 66 6d 74 20 RIFF....WAVEfmt
10 00 00 00 01 00 02 00 22 56 00 00 88 58 01 00 ........"V...X..
04 00 10 00 64 61 74 61 84 d9 00 00 02 00 03 00 ....data........

You might be able to do this in the sub-routine that saves the object,
but I don't know how to add the header to the object. Don't know if it
would change the object to a string, or corrupt the object.

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/AwUBSe45iIechKqOuFEgEQIDSQCg8UzNh2lPKvEXAw332mTwTsZcXFIAoPbv
mHVfuuN8/SVpESdgqGkLPxTZ
=rcUS
-----END PGP SIGNATURE-----
 
S

sontaranleader

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) :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Extracting audio from an Access Database 1
Extracting and reinserting audio? 2
Access Lock File .LACCDB / Now shows as a notepad file 0
Template with fields and premade texts.. 0
Single file Power Point with audio 1
can't save audio file in a powerpoint 0
can't save audio in a powerpoint file 3
Dynamic Naming of Form Button 3

Top