Uploading xls to OLE Object in table

[cbh35711]

Hello fine folks,



I am trying to upload an xls file as an OLE object. I would need to create a new record for each upload. I'm using the code i found here http://stackoverflow.com/questions/6...file-ms-access

After enabling the correct reference(MS ActiveX Data Objects 2.5 Library), it compiles fine. When stepping thorugh the code, it causes Access to crash on this line

Set cn = CurrentProject.Connection

Any idea what i am doing wrong?

Private Sub Import_Click()


On Error GoTo ErrHandler


    sFilePathAndName = "C:\CashFlow.xls"
    'Test to see if the file exists. Exit if it does not.
    If Dir(sFilePathAndName) = "" Then Exit Sub


    'LoadPicIntoDatabase = True


    'Create a connection object
    Dim cn As ADODB.Connection
    Set cn = CurrentProject.Connection


    'Create our other variables
    Dim rs As ADODB.Recordset
    Dim mstream As ADODB.Stream
    Set rs = New ADODB.Recordset


    'Configure our recordset variable and open only 1 record (if one exists)
    With rs
        .LockType = adLockOptimistic
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .Open "SELECT TOP 1 * FROM CashFlowFile", cn
    End With


    'Open our Binary Stream object and load our file into it
    Set mstream = New ADODB.Stream
    mstream.Open
    mstream.Type = adTypeBinary
    mstream.LoadFromFile sFilePathAndName


    'add a new record and read our binary file into the OLE Field
    rs.AddNew
    rs.Fields("ole_object") = mstream.Read
    rs.Update


    'Edit: Removed some cleanup code I had inadvertently left here.




Cleanup:
    On Error Resume Next
    rs.Close
    mstream.Close
    Set mstream = Nothing
    Set rs = Nothing
    Set cn = Nothing


    Exit Sub


ErrHandler:
    MsgBox "Error: " & Err.Number & " " & Err.Description
    'LoadPicIntoDatabase = False
    Resume Cleanup


End Sub

Thanks!!

Chris

[June7]

Don't know if will help but I use 'MS ActiveX Data Objects 2.8 Library' in Access 2007.

I would open recordset like (never used Cursor Location parameter):

rs.Open "SELECT TOP 1 * FROM CashFlowFile;", cn, adOpenDynamic, adLockOptimistic

I tested code with these edits and it ran without error.

[cbh35711]

Thanks June!

2.8 clearly seems better than 2.5, so i enabled that instead.

I commented out

Set cn = CurrentProject.Connection

because it wasn't used anywhere else. When getting to your updated line of code, it throws this error "Error 3001 Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another." I thought this might be due to commenting of the Connection line. After putting it back in, it crashes again. Looking in the locals window, I do not see

Me.Connection

as a possibility. Maybe it's not called Connection?

Does anyone have another way they've tried before to suggest?

All your help is much appreciated.

Chris

[June7]

The Set cn = Connection line is needed because you use the cn variable in opening the recordset. Without variable, can do:

rs.Open "SELECT TOP 1 * FROM CashFlowFile;", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Connection is correct and works for me. I cannot replicate your issue. Do you want to provide project for analysis? Follow instructions at bottom of my post.

[cbh35711]

Not sure how i missed that one. Before uploading the project, let me put one more thing out there.

Here's a sub i created from the part i understood and something online

Private Sub Command20_Click()    
   
    'different way to open the record
    Set db = CurrentDb
    Set rs = db.OpenRecordset("CashFlowFile", dbOpenDynaset)
    

    sFilePathAndName = "C:\CashFlow.xls"    
    
    Set mstream = New ADODB.Stream
    mstream.Open
    mstream.Type = adTypeBinary
    mstream.LoadFromFile sFilePathAndName
    
    rs.AddNew
    rs.Fields("ole_object") = mstream.Read
    rs.Update
End Sub

This creates a new record in the table. Unfortunately instead of saying MS Excel, it says Long Binary Data. It doesn't seem to contain the same functionality it did before. I think it has something to do with mstream.Type = adTypeBinary.

Thanks June,

Chris