Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2007
    Posts
    74

    Question Unanswered: Insert an attachment into my db using a form

    All,

    I have a form that allows users to insert a row into a table in my db. I need to allow the users to insert an attachment into an OLE field in the database from that form as well. I have not been able to identify any way to do this.

    Any Suggestions?
    Regards

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Are you using a hyperlink field or what? If you're looking for directions on how to use the Common Dialog API, you can look here.
    Me.Geek = True

  3. #3
    Join Date
    Jun 2007
    Posts
    74

    Some Clarification on my confusion

    OK,
    Since I posted this I have found some more information..

    I am using the code from http://www-.mvps.org/access/api/api0001.htm to prompt the user for a file.


    Once the user selects the file I am placing the file location in a textbox.
    That was the easy part.

    Now for the part I am stumped on.
    My form has many Listbox and ComboBox controls and a few TextBoxes. When the user clicks a buton titled "Submit Notification" All of the data is written into the table with the following code:

    Code:
     strSql = "Insert into Notifications Values ('" & Title & "','" & Category & "','" & Language & "','" & EffDate & "','" _
                & ExpDate & "','" & LOB & "','" & Channels & "','" & Roles & "','" & Regions & "','" & States & "','" _
                & Offices & "','" & Comment & "','" & AID & "','" & PID & "','" & Status & "');"
        Set db = DBEngine(0)(0)
        db.Execute strSql, dbFailOnError
        'MsgBox db.RecordsAffected & " record(s) where unpicked."
        If db.RecordsAffected = 1 Then
            MsgBox "Notification Submitted Successfully"
        End If
        Set db = Nothing
    So after this statement executes I want to insert the attachment in the file location that is in the textbox to the row I just inserted. I can use the Title of the notification to identify the row since I just wrote it and it is still stored in a variable. I am just not sure how to write the code to insert the file into the table.

    Hopefully that makes sense!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    T-SQL INSERT syntax
    Code:
    INSERT
    INTO MyTable(Field1, Field2, ... , FieldN)
    VALUES(Value1, Value2, ... , ValueN)
    HTH
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2007
    Posts
    74
    The problem is that inserting the value using T-SQL, Execute, or DoCmd.RunSQL results in the field value being set to "Long Binary" in the table. So when I try to open the object from the table I get an error and the file willl not open.

    It seems that the OLE Container is not created when the value is inserted in this way. I have found an example of how to insert a Bitmap at http://support.microsoft.com/default...B;EN-US;205635

    This seems to be for VB and not for VBA. I do not see a control I can use that is a Data Control where I can set the "Database Name" and "Record Source" in the MS Access toolbox or additional tools"

    It seems that this should be an easy task and I am making it too difficult. Please allow me to restate and simplify in case I muddied the water earlier.

    Here is my situation:
    I have the path of a file that I want to insert as an OLE object (either a Word Doc or Excel File) into a table that has a field set with a datatype of OLE.

    The file to be inserted into the table is selected by the user (from my form) and then the path information is stored in a textbox on the form. When the user clicks submit (Command Button), I want to insert the file stored at the path displayed on my form into a table in my db

    Does that make it more clear or more confusing?
    Last edited by Brent Blevins; 06-12-07 at 12:50.

  6. #6
    Join Date
    Feb 2004
    Posts
    137
    The following code should do what you want:
    Code:
    Dim b() As Byte
    Dim ff As Integer
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ff = FreeFile
    ' Substitute the actual textbox control name on the next line
    Open Me.Text0 For Binary Access Read Shared As ff
        ReDim b(LOF(ff) - 1)
        Get #ff, , b
    Close #ff
    
    ' Substitute the actual database name on the next line
    Set db = DAO.OpenDatabase("C:\TEMP\db1.mdb")
    ' Substitute the actual table name on the next line
    Set rs = db.OpenRecordset("MyOLETest", dbOpenTable)
    
    With rs
        .AddNew
        ' Substitute the actual field name on the next line
        .Fields("Picture").AppendChunk b
        .Update
        .Close
    End With
    
    Set rs = Nothing
    
    db.Close
    Set db = Nothing
    See if this solution works for you.

  7. #7
    Join Date
    Jun 2007
    Posts
    74

    Same problem

    I modified your code, adding the name of my Textbox, set db by using CurrentDb and added the tablename, and indicate the field in the table as shown below.

    Code:
    Dim b() As Byte
    Dim ff As Integer
    'Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ff = FreeFile
    ' Substitute the actual textbox control name on the next line
        Open Form_Notification_Generator.tbAttachment For Binary Access Read Shared As ff
        ReDim b(LOF(ff) - 1)
        Get #ff, , b
    Close #ff
    
    ' Substitute the actual database name on the next line
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Attachment", dbOpenTable)
    
    With rs
        .AddNew
        ' Substitute the actual field name on the next line
        .Fields("object").AppendChunk b
        .Update
        .Close
    End With
    
    Set rs = Nothing
    
    db.Close
    Set db = Nothing

    When I run the code it does perform an insert (in this instance I am trying to insert a text file), but when I open the table the field says "Long Binary Data". Then if I double click it to open the file, I get the following error.

    "A problem occured while Vibe Content Publisher was communication with the OLE server or ActiveX control
    Close the OLE server and restart it outside of vibe content publisher. Then try the original operation again in Vibe Content Publisher"

    I have read all of the helpfiles and see no apparent reason why this solution would not work but it doesn't.

    Any other ideas?

  8. #8
    Join Date
    Jun 2007
    Posts
    74

    Red face Fixed, Well kind of...

    OK, I figured out how to get the file embedded in a table. The first step I take is to insert all of the data from the various controls into the Notifications table using the first part of the code below. The second part of the code embeds the OLE File into a table named OLETMP

    Code:
    '***Insert the row into the table***
        Dim db As DAO.Database
        
        strSQL = "Insert into Notifications  Values ('" & Title & "','" & Category & "','" & Language & "','" & EffDate & "','" _
                & ExpDate & "','" & LOB & "','" & Channels & "','" & Roles & "','" & Regions & "','" & States & "','" _
                & Offices & "','" & Comment & "','""','" & AID & "','" & Status & "');"
        'Set db = DBEngine(0)(0)
        Set db = CurrentDb
        db.Execute strSQL, dbFailOnError
            
    
        If db.RecordsAffected = 1 Then
    
        '***Insert the OLE File into the OLETMP table
            Form_Notification_Generator.tbAttachment.SetFocus
            AttachmentString = Form_Notification_Generator.tbAttachment.Text
    
            If AttachmentString <> "" Then
                Form_Notification_Generator.OLEFile.Visible = True
                Form_Notification_Generator.OLEFile.Class = "Word.Document"
                Form_Notification_Generator.OLEFile.OLETypeAllowed = acOLEEmbedded
                Form_Notification_Generator.OLEFile.SourceDoc = AttachmentString
                Form_Notification_Generator.OLEFile.Action = acOLECreateEmbed
                Form_Notification_Generator.tbTitle.SetFocus
                Form_Notification_Generator.OLEFile.Visible = False
                Call InsertFile(Title, AttachmentString)
            Else
                MsgBox "Notification Submitted Successfully"
            End If
        End If
    
    
        Set db = Nothing
    
        If db.RecordsAffected <> 1 Then
            MsgBox "UH-OH, the notification was not inserted!"
        End If

    So, I bound the form to a table called OLETMP and addeed a Bound Object Frame with a Control Source of an OLE field in that table. I am inserting the OLE file into the OLETMP table which is a separate table from where the rest of the data is going. I then call a function named "InsertFile" that is supposed to be a Select Into statement that will move the OLE File from the OLETMP table into the Notifications table. My plan was to then, delete * from the OLETMP table to clean it up for the next time it is needed. I did this because I could not figure out how to insert the file into the Notifications table in the same row as the data I just inserted. I would ultimately like not use the extra table and insert it all into one table (the Notifications table).

    I know that in order to do that I need to bind the form to the Notifications table and set the Bound Object Frame's Control Source to the OLE field on the Notifications table.

    With either method, my problem seems to be inserting it into the Notifications table in the correct row.


    So, what I am asking now is...
    After my "Insert Into Notifications" statement runs.. How do I use the code to insert the file in the correct table (and the correct Row). I tried to use a DAO.Recordset but could not get the syntax right.

    OR

    Am I better off embedding the OLE File in the separate table and then moving it using SQL into the correct table. If so, I would really appreciate some help on the SQL syntax. I tried for hours and could not get it right.

    Any Suggestions or have I muddied the water too much?


    Regards,

  9. #9
    Join Date
    Jun 2007
    Posts
    74
    Just wanted to thank those who responded. I finally figured this thing out tonight. If anyone is interested in the solution please let me know and I will post the code.

  10. #10
    Join Date
    Nov 2005
    Posts
    49
    Posting the code as a reference is always a good thing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •