Storing BLOBs in linked table using DAO

[harmankardon]

Hello all,

I am using the following code to store BLOBs in a linked table (SQL Server 2000):
http://support.microsoft.com/kb/210486

This code works great for files up to 40MB or so (haven't been able to determine the exact limit). When I try to use it for files around 60-70MB I get the following error: Run-time Error '3155' ODBC--insert on a linked table failed

This error occurs on the T.Update line in the above code.

Again, it works great for files smaller than 40MB, but craps out on me on anything larger.

Any ideas?

 

[harmankardon]

Just thought I'd bump this in-case someone else has seen this problem since I originally posted.

 

[mdlueck]

What sort of control are you using to ship that much data to the SQL BE DB?

I banged into an issue where Access text field controls start crashing Access when a string gets "significantly" large enough. Certainly not 40MB worth!

So I was thinking, if not via DAO LinkedTables, perhaps it could work via ADO.Command objects, unbound form, and the like.

 

[harmankardon]

I simply have a button on my file attachments form that says "Add Attachment" which when clicked runs a VBA module that pops-up a Select File dialog window and then pushes the selected file as a BLOB into a field in my SQL Server linked table.

(Access sees the data type of field as "OLE Object" and SQL Server sees the data type of the field as "Image".)

What I have yet to do is to write a small VB6 or VB.NET app with just my BLOB code to see if the size limit problem I am having is somehow related to Access and the Jet/ACE engine. I've pretty much tried everything else I can think of.

Maybe I'll try ADO and see if that is any better.

 

[ChrisO]

The Microsoft code you linked to seems very old.

It appears to be pre-Access95 in that it requires 16 bit load blocks. With Access95, and later, the load block can be handled with 32 bit size. That removes the necessity to chunk the size of blocks to 16 bits and the need to get the remainder.

In 32 bit systems (Access95 and later) it can be reduced to:-

Private Sub FileToField (ByVal strFileName As String, _
                         ByRef objFieldName As Object, _
                         ByRef objFileSize As Object)
                              
    Dim bytBuffer() As Byte
    Dim intFileNum  As Integer
    
    intFileNum = FreeFile
    
    Open strFileName For Binary As intFileNum
    
    ReDim bytBuffer(LOF(intFileNum))
    objFileSize = LOF(intFileNum)               [color=green]' Only required to write the size to the Table.[/color]
    
    Get intFileNum, , bytBuffer
    objFieldName.AppendChunk bytBuffer
    
    Close intFileNum
        
End Sub

(Surprisingly, on my computer, grab time is about 2.4 seconds for a 120 Meg file.)

Start small and build up to the failure.
A test demo is attached in A2003 format.

Edit:
Size of FileName in table tblUserFiles was too small; expanded to 255.

Chris.

 

[harmankardon]

Hmm, interesting, I'm definitely going to have a look at this to see if it solves my problem.

Do you appropriate GetChunk code that would go with this code?

 

[ChrisO]

I do not understand the question.

That demo puts a file into the table. If you also want the code to that puts the BLOB back into a directory then that can be done.

I think first you see if it works as is.
Then split the database and test again.
Then split it across a network.
Then test it with SQL server.

If that all works we can look at taking the code one step further.

But one step at a time.

Chris.

 

[harmankardon]

I do not understand the question.

If you also want the code to that puts the BLOB back into a directory then that can be done.

Chris.

Yes this is what I meant, my apologies for the confusion.

I'll let you know how my testing goes and we can take it from there.