Hi there,
Just to start I should clarify that when I refer to "characters" in this thread, I'm referring to a particular "part" played by actor ^_^
In my job I work with a lot of different audio files recorded by many different actors as many different characters over various languages and I decided to create a DB which would allow us to track and efficiently use the many thousands of pieces of individual casting info that pass through my teams hands.
It was a bit of a quick and easy DB and I allowed the attachment of a single OLE object for each casting.
The reporting is basically that by looking for a particular actor, type of role or project, we have various tracked audio samples with actor and character information associated.
The problem is that the DB is getting to be too large, so I want to add a button to the DB's home page in the style of "auto archive" where:
1) All the records will an ole object attached are stored in a recordset
2)This recordset is looped through one by one taking the following actions:
i)A predictable path for the OLE object to saved is generated based on filename, project, date etc
ii) The path is saved in a hyperlink field
iii)The OLE object is saved at the designated path as an working audio file (The audio files are usually .wav format but sometimes also .mp3 or .aif.)
iv)The original embedded ole object is deleted from the DB
...
THE STEPS I NEED YOUR EXPERTISE ON ARE 2iii & 2iv -> How can I take an ole object and save it on a server?
Thanks you very much for any help you can provide and here's my code so far:
Private Sub Command58_Click()
' Setup connection
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim db As Database
Set db = CurrentDb()
'Create a fixed date and time value for any changes during this process
Dim DateTimeOfChange As Date
DateTimeOfChange = Now()
DateTimeOfChange = DateTimeOfChange
DoCmd.SetWarnings False
Application.SetOption "Auto compact", True
'FTA = Files To Archive i.e all files where the "audio ref" field includes an OLE audio object
Dim FTA As New ADODB.Recordset
FTA.ActiveConnection = cnn1
FTA.CursorType = adOpenKeyset
FTA.LockType = adLockOptimistic
FTA.Open "SELECT TBLcasting.ID, TBLcasting.AudioRef FROM TBLcasting WHERE (((TBLcasting.AudioRef) Is Not Null))"
If Not (FTA.BOF And FTA.EOF) Then
FTA.MoveFirst
Do While FTA.EOF = False
'Here is where the OLE object stored in TBLcasting.AudioRef (a wav file which can be opened from within the DB currently)
'needs to be saved in a particular/predictable location on a server as a wav file or an aif file (depending on teh source file)
'After the file is saved on teh server, a text link will be updated with teh correcsponding audio fiel we just saved on teh server
'and the original ole object will be deleted.
'The DB automatically compresses when closed so the currently massive db should be reduced a massive amount allowing for continued use.
FTA.MoveNext
Loop
End If
End Sub
Just to start I should clarify that when I refer to "characters" in this thread, I'm referring to a particular "part" played by actor ^_^
In my job I work with a lot of different audio files recorded by many different actors as many different characters over various languages and I decided to create a DB which would allow us to track and efficiently use the many thousands of pieces of individual casting info that pass through my teams hands.
It was a bit of a quick and easy DB and I allowed the attachment of a single OLE object for each casting.
The reporting is basically that by looking for a particular actor, type of role or project, we have various tracked audio samples with actor and character information associated.
The problem is that the DB is getting to be too large, so I want to add a button to the DB's home page in the style of "auto archive" where:
1) All the records will an ole object attached are stored in a recordset
2)This recordset is looped through one by one taking the following actions:
i)A predictable path for the OLE object to saved is generated based on filename, project, date etc
ii) The path is saved in a hyperlink field
iii)The OLE object is saved at the designated path as an working audio file (The audio files are usually .wav format but sometimes also .mp3 or .aif.)
iv)The original embedded ole object is deleted from the DB
...
THE STEPS I NEED YOUR EXPERTISE ON ARE 2iii & 2iv -> How can I take an ole object and save it on a server?
Thanks you very much for any help you can provide and here's my code so far:
Private Sub Command58_Click()
' Setup connection
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim db As Database
Set db = CurrentDb()
'Create a fixed date and time value for any changes during this process
Dim DateTimeOfChange As Date
DateTimeOfChange = Now()
DateTimeOfChange = DateTimeOfChange
DoCmd.SetWarnings False
Application.SetOption "Auto compact", True
'FTA = Files To Archive i.e all files where the "audio ref" field includes an OLE audio object
Dim FTA As New ADODB.Recordset
FTA.ActiveConnection = cnn1
FTA.CursorType = adOpenKeyset
FTA.LockType = adLockOptimistic
FTA.Open "SELECT TBLcasting.ID, TBLcasting.AudioRef FROM TBLcasting WHERE (((TBLcasting.AudioRef) Is Not Null))"
If Not (FTA.BOF And FTA.EOF) Then
FTA.MoveFirst
Do While FTA.EOF = False
'Here is where the OLE object stored in TBLcasting.AudioRef (a wav file which can be opened from within the DB currently)
'needs to be saved in a particular/predictable location on a server as a wav file or an aif file (depending on teh source file)
'After the file is saved on teh server, a text link will be updated with teh correcsponding audio fiel we just saved on teh server
'and the original ole object will be deleted.
'The DB automatically compresses when closed so the currently massive db should be reduced a massive amount allowing for continued use.
FTA.MoveNext
Loop
End If
End Sub
