This may interest some of you!! Storing any File type in a database!

[Beacon]

Wanna play music from the db!

This is a simple demo.
Add the media player control(mp1), a text box(txtfile) and another 2 command buttons(cmdGet and cmdPlay) to the above code etc.

IMPORTANT: you gotta add a field called filename and a text box called txtfilename as well. The make changes to cmdadd and fillfields subs.

I done it for ya coz i'm so noice!

Public Sub FillFields()
    If Not (rs.BOF And rs.EOF) Then
        txtDescription.Text = rs.Fields("Description")
        txtfilename.Text = rs.Fields("filename")
    End If
End Sub

ADD NEW CODE:

Private Sub cmdAdd_Click()
    Dim bytData() As Byte
    Dim strDescription As String
    On Error GoTo err
    
    With dlgadd
        
        .Filter = "All Files (*.*|*.*"
        .DialogTitle = "Select File"
        .ShowOpen
 
        
        Open .FileName For Binary As #1
        ReDim bytData(FileLen(.FileName))
        If FileLen(.FileName) > "1000000" Then
        MsgBox "This is a large file it could take some time! Please be patient!!!", vbInformation, "Large File"
        End If
    End With
    
    Get #1, , bytData
    Close #1
    
    Dim fn As String
    fn = dlgadd.FileName
    strDescription = InputBox("Enter description.", "New Picture")
    
    With rs
    DoEvents
    Me.MousePointer = 11
        .AddNew
        .Fields("filename") = fn
        .Fields("Description") = strDescription
        .Fields("File").AppendChunk bytData
        .Update
    DoEvents
    End With
    Me.MousePointer = 0
    FillFields
    Exit Sub
err:
    If err.Number = 32755 Then
        Me.MousePointer = 0
    Else
    Me.MousePointer = 0
        MsgBox err.Description
        err.Clear
    End If
End Sub

Now on cmdGet

Private Sub cmdGet_Click()
 
            'check for temporary file if it's found delete it
            temp = App.Path & "\" & txtfilename.Text
            If Len(Dir(temp)) > 0 Then
                Kill temp
            End If
 
            'Open temp file
            file = FreeFile
            Open temp For Binary As #file
 
            'Read temp file as binary into chunk
            'this is the engine room
            ImgeSze = rs("FILE").ActualSize
            Do While offset < ImgeSze
               chunk() = rs("FILE").GetChunk(ChunkSize)
               Put #file, , chunk()
               offset = offset + ChunkSize
            Loop
 
            Close #file
txtfile.Text = temp
'this is to load it's path name into the text box
End Sub

Then on cmdPlay

Private Sub cmdPlay_Click()
'get filename
mp1.FileName = txtfile.Text
mp1.Play 'play it
'Please modify the below line so when it's finished playing it, it 'deletes the file it made! At the moment it'll work but you'll get an error
Kill txtfile.Text 'kill it
End Sub

It just uses the temp file we make when retrieving the file out of the database and plays it then deletes it when finished playing.

[Beacon]

If you get E_fail or E_Size errors then:

1) Get mdac 2.6 not 2.5 for e_fails

and
2) Make sure all fields are memo except autonumber and ole fields!

[Sastraxi]

There are different kinds of databases that allow unlimited length in the memo field.

[timekiller]

Beacon,

I got it to work by changing my datatype to my field from Binary to Image. Very nice!

I also found that this will work as well using Stream (ADO 2.5+, SQL 7.0):

Dim mstream As ADODB.Stream
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open

mstream.LoadFromFile sFile
rs.Fields("yourfieldhere").Value = mstream.Read

rs.Update

'--------------------------------------------------

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open

'read from db recordset
mstream.Write rs.Fields("yourfieldhere").Value

'write to file
mstream.SaveToFile "yourpathfilenamehere", adSaveCreateOverWrite


No need to chunk

[a2427]

Any one know what the max size of an ole object field is in Access. My program (beacons) program works until I try to read a large file into th database, then I get an "E_Fail" error from the provide. I dont even know what an E_Fail error is?

[a2427]

I found the problem, or at least a simple solution. I tried the exact same code, but instead used DAO as the database driver and guess what it worked fine. So it seems that the problem is in the ADO driver, not the size of the field (although there is a1 GB limit as you mention). Also I found a clever way with the help of your code to not only store any file the database, but also view any file in the OLE Container field once it is stored as long binary data in the database (I am doing this so any file can dropped into my OLE box can be stored then viewed later.

The problem I found with storing long binary data is that unless you know the type, you just have binary data, i.e. you dont know what kind of file it is. This poses a problem when trying to view ANY file that is stored as long binary data.

The method is simple, use the ole box to do the dirty work. When some one drops an object into the ole box, have the ole box 'SavetoFile'. By doing this, eveything is stored in the binary data, including the object header (now we will always know the type of file stored.) Once the ole box savetofile method has been completed, read the saved file into the database as long binary data using the appendchunk method as you showed. To view any object, Simple write the data from the long binary field to a file named .OLE, and use the ole box's readfromfile method. This method allows the ole box to do all the hardwork and the programmer does not have to worry about storing a file extension or object type with the long binary data. Hope this helps and if any needs the code, I will provide it.

[a2427]

Here it is. It needs cleaned up but it works. Let me know if you have questions.

Dim RS As ADODB.Recordset
Dim db As Database
Dim rsdao As Recordset


Private Sub Form_Load()

ConnecttoDB 'connection function for ado stuff

Set RS = New ADODB.Recordset 'Ado rs
RS.Open "Select * from tblTemp", NRGConnection, adOpenDynamic, adLockOptimistic, adCmdText


Set db = OpenDatabase(App.Path & Nuregdbname)

   
Set rsdao = _
      db.OpenRecordset("Select * from tbltemp", _
      dbOpenDynaset)
   
      'tblTemp contains an ole field named "Longdata" 

End Sub

Private Sub Save_Object_to_Recordset()
'Save object in the ole box to the rs

Dim bytData() As Byte
Dim strDescription As String
Dim FileNum As Integer


On Error GoTo err

Dim t As String
t = "c:\t.ole"
If Len(Dir(t)) > 0 Then
Kill t
End If

   
' Get file number.
FileNum = FreeFile
' Open file to be saved.
Open t For Binary As #FileNum
' Save the file.
OLE1.SaveToFile FileNum 'Save ole object to file
' Close the file.
Close #FileNum
   





Open t For Binary As #1 'Open saved file


'Get data from saved file
ReDim bytData(FileLen(t))
Get #1, , bytData 'Get data
Close #1




'Put in rs at current row
With rsdao
    .Edit
    .Fields("LongData").AppendChunk bytData
    .Update 'ADO fails here for large files.
End With

Exit Sub

err:

MsgBox err.Description & "  " & err.Number
err.Clear


End Sub

Public Sub Display_Ole_Object_From_Recordset()

Dim imgsze As Long
Dim offset As Long
Dim chunk() As Byte
Dim file As Integer
Const ChunkSize = 32768
Dim FileNum As Integer
Dim t As String
'Make sure the temporary file does not already exist

t = "c:\t.ole"
If Len(Dir(t)) > 0 Then
Kill t
End If

'Open temp file and write the db contents
file = FreeFile
Open t For Binary As #file
imgsze = RS("longdata").ActualSize 'Get field size from rs
Do While offset < imgsze
chunk() = RS("longdata").GetChunk(ChunkSize) 'Extract the data
Put #file, , chunk()
offset = offset + ChunkSize
Loop
Close #file

   
' Get file number.
FileNum = FreeFile
' Open file to be read by the ole
Open t For Binary As #FileNum
' Save the file.
OLE1.ReadFromFile FileNum 'Insert file into ole box
' Close the file.
Close #FileNum

MsgBox "Object inserted."

End Sub

You can ignore the ADO stuff.

[Beacon]

If anyone has the same problem as A2427. I.e

"Any one know what the max size of an ole object field is in Access. My program (beacons) program works until I try to read a large file into th database, then I get an "E_Fail" error from the provide. I dont even know what an E_Fail error is?"

The answer is you need to go get ADO 2.7 Library from microsofts website!

[ZaSz]

can u shoe me hoe to put something like 500 .jpg files in ONE file?

[JPRoy392]

I can't shoe you a thing!!

[Motxopro]

yeah it interests me

[Jethro]

Originally posted by Beacon
Nah got me mate megatron to do that i just said delete all jethro's posts and he said ok!

So that's why all my threads have disappeared, including the one detailing how to export data at the speed of light....damn hope l keeped some notes on that one

[ZUWARI]

this example is great. but y i need to store it in db?

[Beacon]

Ok i'll answer this with a question.

Why do you store text and number etc. in a database?
Same reason. Alls this code does is stores and retrieves numbers(binary data) in a database.

later
b

[Jethro]

Originally posted by ZUWARI
this example is great. but y i need to store it in db?

Er...to access it later....Security codes etc.

[squirrelly1]

I just got an unspecified error!!!! on the following code:

Why???

Dim db As Connection
Dim rs As Recordset

Private Sub LoadDriverBase()
Dim db As Connection
    Set db = New Connection
        db.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Drivers\DriverBase\DrBase.mdb;Persist Security Info=False"
    
    Set rs = New Recordset
        rs.Open "Select * From Zipped", db.ConnectionString, adOpenStatic, adLockOptimistic
        
End Sub

Public Sub FillFields()
    If Not (rs.BOF And rs.EOF) Then
        txtDescription.Text = rs.Fields("Description")
        txtfilename.Text = rs.Fields("filename")
    End If
End Sub

Private Sub cmdAdd_Click()
    Dim bytData() As Byte
    Dim strDescription As String
    'On Error GoTo err
    
    With dlgadd
        
        .Filter = "All Files (*.*|*.*"
        .DialogTitle = "Select File"
        .ShowOpen
 
        
        Open .FileName For Binary As #1
        ReDim bytData(FileLen(.FileName))
        If FileLen(.FileName) > "1000000" Then
        MsgBox "This is a large file it could take some time! Please be patient!!!", vbInformation, "Large File"
        End If
    End With
    
    Get #1, , bytData
    Close #1
    
    Dim fn As String
    fn = dlgadd.FileName
    strDescription = InputBox("Enter description.", "New Zipped File")
    
    With rs
    DoEvents
    Me.MousePointer = 11
        .AddNew
        .Fields("FileName") = fn
        .Fields("Description") = strDescription
        .Fields("Zipped").AppendChunk bytData
        .Update
    DoEvents
    End With
    Me.MousePointer = 0
    'FillFields
    Exit Sub
err:
    If err.Number = 32755 Then
        Me.MousePointer = 0
    Else
    Me.MousePointer = 0
        MsgBox err.Description
        err.Clear
    End If
End Sub

Private Sub Form_Load()
LoadDriverBase
End Sub

THanx for any help!!!

~Squirrelly1

[Beacon]

Where is the error?

What line?

Oh also note that code is for ADODB? Not dao not that it should matter but there maybe some functions in there that dao wont like!
It was an extension of my image_library one!

later
b

[squirrelly1]

Beacon,

First of all, thank you for replying...

The error was in the line..

.Fields("Zipped").AppendChunk bytData

It says that it is an "Unspecified Error"...

The Field name is correct, and I am using ADODB...

The library I am using is the Microsoft ActiveX Data Objects 2.1 Library. Could you attach the latest library to your next post... Maybe that's the problem.

Also, if you have time...
Could you put together a 'very simple' (I don't want to waste your time) sample program that will put .exe's into a dbase...

As you can see, I've tried to do this with the code posted here, but I've been quite unsuccessful as of yet.


Thank you for your time,

Squirrelly1

[squirrelly1]

I downloaded the 2.7 library, and still no change.... I'm going to try and start over again and see if I just forgot something last time...


Please help, beacon...


thanx,
Squirrelly1

[Beacon]

Ok i'll whack a crappy example together for you! Sorry it was a public holiday over here!

But till then i note you arent using ADODB code more dao type code:
Note: This was an extension to my image library tutorial.

In your declarations it should be:

Private cn As ADODB.Connection
Private rs As ADODB.Recordset

This is how you set the connection:

Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & App.Path & "\files.mdb"
    cn.Open
    Set rs = New ADODB.Recordset
    rs.Open "table1", cn, adOpenKeyset, adLockPessimistic, adCmdTable

Alter you code accordingly!!!
later
b

[Beacon]

An example for ya!

This example was from when i was working out how to do it so it's real crap and basic. It works however but it's slow, looks crap and even the database is real simple.

I dont like posting stuff that aint up to standard but i dont have time to write comments and stuff for a more complex one!

Anyway hope it makes sense and helps!
later
b

[Ecniv]

Beacon - nice coding. Anyway - what is the definition of MP1 in the play music from DB ?

Regards

Vince

[Beacon]

Ok sorry bit lost what do you want to do??
You want to identify that it's an MP1 file yes/no?


later
b

[FusedOut]

Beacon,

Does it mean that with your codes to save any files to database that I can do the following:

First of all, the user selects a file from a server and can insert the selected file into an attribute in the database. And that after insterting the file into the database, the user can in the future work on the file that is in the database? When the file is open for editing, it is not longer reflected/updated to the file in the server? Which means, the file in the database is no longer asociated/linked to the file in the server.

Can the codes that you've written support that? Hope you're able to understand what i'm talking about =)

Thanks for your advice!!!

[Ecniv]

Originally posted by Beacon
Ok sorry bit lost what do you want to do??
You want to identify that it's an MP1 file yes/no?


later
b

I was looking at the second chunk of coding at the top - said mp1 in the code and I couldn't see where it was defined. Just did a search though for mp1 and you've stated its a Media Player control sooooooooooo Sorry

Vince

[Beacon]

Vince:
hehe sorry crappy names but it was a test!


FusedOut:
Yes. Why??

[FusedOut]

so Beacon,

do i save all the information by using Appendchunk method? how do i get back my image after its saved to the database? can you show me the codes to do it?

Thanks :-)

[Beacon]

Nope!

Read the top thread there's a tutorial i wrote for saving images into a database and getting em back out.
Download it and read it and you'll know how.

later
b

[squirrelly1]

Beacon,

First of all, that is some awesome code!!! It's going to help me a lot... and thnx for the example, I checked it out and it's cool (I'll examine what I did wrong in a little bit)

Thanks a million,

Squirrelly1

[Beacon]

no probs let me know!
later
b

[squirrelly1]

Beacon,
I was wrong, the example didn't exactly work for me.... I created a test .txt file and put some string into it... I think it was "This is a test" or something like that.... I had no trouble getting it into the database, but when I tried to save it to another name from the database, it gave me an error...

Either BOF or EOF is true.... la di da di da

the error was in the savefile sub on this line...

imgsze = rs("file").ActualSize

I checked out the created file anyway, and it was empty instead of containing my string...

thanks beacon...

Squirrelly1

[squirrelly1]

Beacon,

have you tried to do this with streaming????

I wonder if that would be faster????

Squirrelly1

[Beacon]

Hey squirrelly1

Nope i havent tried streaming it! I wondered that myself but havent had the time to test it.
Over the next few days i'll see what i can do if i have time!

Ok it has no checks in it for eof or nothing i dont think.
The codes doesnt work if you loop the savefile sub but i dont think you doing that.
Though you may be at the EOF so you'll need to make sure the recordset is at the record you want.

Are you using the code in that attachment??
If so gimme 45mins and i'll be able to download it and test it.

[squirrelly1]

Yep, I'm using the example you gave... it is the one w/ the error

I'm probably wrong, but i think the error is in that you don't seem to have any code that tells the program where to look in the db...

for example...

you tell it to look in rs("file)... but how does it know which one of the files you want to save if you have multiple files in the database?

[Beacon]

yeah whoops i kinda through that one together sorry.

Basically you gotta find out what record you want.
This isnt the most efficient way or the fastest but it works:

Also i altered the progress bar code so it's better!!

Public Sub savefile()
pb1.Value = pb1.Min
cd1.Filter = "All Files (*.*) |*.*" 'only allow gifs
cd1.DialogTitle = "Save File"
cd1.FileName = lstfilename.Text
cd1.ShowSave 'show save dialog box
 
            Dim imgsze As Long
            Dim offset As Long
            Dim chunk() As Byte
            Dim file As Integer
            Dim temp As String
            Const ChunkSize = 100
            Dim X As Integer
            
'what record are we at
        rs.MoveFirst
        X = 0 'or 1 if using list view or treeview!
        Do Until X = lstfilename.ListIndex
        rs.MoveNext
        X = X + 1
        Loop
            
            'Make sure the temporary file does not already exist
            temp = cd1.FileName
If Len(Dir(temp)) > 0 Then
                Kill temp
            End If
            'Open temp file
            file = FreeFile
            Open temp For Binary As #file
 
            'Read temp file as binary into chunk
            'this is the engine room
            imgsze = rs("file").ActualSize
            pb1.Max = imgsze
            Do While offset < imgsze
               chunk() = rs("file").GetChunk(ChunkSize)
               Put #file, , chunk()
               offset = offset + ChunkSize
            If imgsze < offset Then
            pb1.Value = pb1.Max
            Else
            pb1.Value = offset
            End If
            Loop
            Close #file
 
MsgBox "Save Complete", vbInformation, "Save:"
End Sub

[squirrelly1]

This is really awesome Beacon... You certainly are a master at your craft...

But I did find one little thing...

when you put in the record finding bit... you set X to equal 1, but it needs to be 0.... Just a little thing, but it makes a big difference!!!

Thanks again,
Squirrelly1

[Beacon]

yeah whoops i was testing with a treeview!
hehe
I'll go change it thanks!

b

[Dimension]

what do i have to reference to make this work?

[Beacon]

Microsoft Active Data Objects 2.x

If your confused about ado read my tutorial on it!!

later
b

[FusedOut]

erm Beacon....

How do I update the field that is of OLE Object datatype?
Do I have to delete the content in there first and then insert it in?

I'm doing the sql code wrongly...and it is deleting the record instead of just the content in the field.

Here's my sql code for that:
sql = "DELETE FileInfo.Comments FROM FileInfo WHERE FileInfo.FileName = '" & selectedFile & "'"

Or can I do it this way? Its wrongly done too...
sql = "UPDATE FileInfo SET Comments = " & "rst.Fields(Comments).AppendChunk strdata " & "WHERE FileInfo.FileName =" & selectedFile


Can you please help me with this? Thank you very much!