Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    carcement is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    15

    Access Error 3027 when using VBA to append table to a linked table

    I have two tables, submit and imgdest. Submit is edited by front-end users to load pictures for back-end users who then delete the images when they're done with them. Submit is edited by a form, in the form I've placed a button (Command37) that has code:
    Code:
    Private Sub Command37_Click()
      
      Call InsertData
      
      
      MsgBox "Completed", "0", "Completed Backup"
      
      Exit Sub
    End Sub
    Private Sub InsertData()
      Dim dbs As dao.Database, rst As Recordset, rstInsert As Recordset
      Set dbs = CurrentDb
      Set rstInsert = dbs.OpenRecordset("SELECT [route], [account], [Date], [Door 1].filedata,[Door 1].filename, [Door 1].filetype, [Door 15].filetype, [comments] FROM imgdest")
      Set rst = dbs.OpenRecordset("SELECT [route], [account], [Date], [Door 1].filedata, [Door 1].filename, [Door 1].filetype, [comments]  " _
      & "FROM submit WHERE route Not IN(SELECT route FROM imgdest)")
      If Not rst.EOF Then
        Do
            If Not IsNull(rst![Door 1.Filedata]) Then
            rstInsert.AddNew
            rstInsert![route] = rst![route]
            rstInsert![account] = rst![account]
            rstInsert![Date] = rst![Date]
            rstInsert![Door 1.Filedata] = rst![Door 1.Filedata]
            rstInsert![Door 1.filename] = rst![Door 1.filename]
            rstInsert![Door 2.Filedata] = rst![Door 2.Filedata]
            rstInsert![Door 2.filename] = rst![Door 2.filename]
            rstInsert![Door 3.Filedata] = rst![Door 3.Filedata]
            rstInsert![Door 3.filename] = rst![Door 3.filename]
            rstInsert![Door 4.Filedata] = rst![Door 4.Filedata]
            rstInsert![Door 4.filename] = rst![Door 4.filename]
            rstInsert![Door 5.Filedata] = rst![Door 5.Filedata]
            rstInsert![Door 5.filename] = rst![Door 5.filename]
            rstInsert![Door 6.Filedata] = rst![Door 6.Filedata]
            rstInsert![Door 6.filename] = rst![Door 6.filename]
            rstInsert![Door 7.Filedata] = rst![Door 7.Filedata]
            rstInsert![Door 7.filename] = rst![Door 7.filename]
            rstInsert![Door 8.Filedata] = rst![Door 8.Filedata]
            rstInsert![Door 8.filename] = rst![Door 8.filename]
            rstInsert![Door 9.Filedata] = rst![Door 9.Filedata]
            rstInsert![Door 9.filename] = rst![Door 9.filename]
            rstInsert![Door 10.Filedata] = rst![Door 10.Filedata]
            rstInsert![Door 10.filename] = rst![Door 10.filename]
            rstInsert![Door 11.Filedata] = rst![Door 11.Filedata]
            rstInsert![Door 11.filename] = rst![Door 11.filename]
            rstInsert![Door 12.Filedata] = rst![Door 12.Filedata]
            rstInsert![Door 12.filename] = rst![Door 12.filename]
            rstInsert![Door 13.Filedata] = rst![Door 13.Filedata]
            rstInsert![Door 13.filename] = rst![Door 13.filename]
            rstInsert![Door 14.Filedata] = rst![Door 14.Filedata]
            rstInsert![Door 14.filename] = rst![Door 14.filename]
            rstInsert![Door 15.Filedata] = rst![Door 15.Filedata]
            rstInsert![Door 15.filename] = rst![Door 15.filename]
            rstInsert![comments] = rst![comments]
            rstInsert.Update
            End If
          rst.MoveNext
        Loop Until rst.EOF
      End If
    End Sub
    This code was working for a short while, now anytime it's run I get error 3027 - Cannot update. Database or object is read-only. However, I can open the linked table and manually change information in it with no problems.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This looks like it's more complex than you need it to be. It looks like you're maintaining the same set of data on two different tables and basically just updating one set of data with the contents from the second set (copying data from SUBMIT to IMGDEST). If ROUTE is the PK of the SUBMIT table and you're just trying to add any record that does not exist in your IMGDEST table that could be run with a simple append query with the appropriate linking. Any chance you could submit a sample database (easier than trying to create something that mimics your naming convention).

  3. #3
    carcement is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    15
    I cannot use an append query as attachments are a multi-valued field.

    https://www.mediafire.com/?q3l4j5ieaabbgbv

    This is the database, imgdest is intended to be a linked table but I included it without the link for sharing. I apologize for the mediafire link, the attachment uploader doesn't seem to be working.

    Basically, it's intended to be a workaround for some seriously computer inept salesmen who can take their pictures, attach them on the road and leave them in the file "submit" and then press a button to send the data to a back-end database when they're in the office connected to our network.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    why oh why did MS ever put in the multi value and calculated fields. They cause so much grief....

    I'm curious here. Is your command37 button supposed to only copy a specific record or ALL records from submit to imgdest where there's no current value in IMGDEST

    Secondly... is there a reason you're actually storing the image instead of a location on your server? Having the images within your database is going to make it bloat very, very quickly and making the compact/repair feature less useful. If you just stored the path of the file and brought up the image as needed it might save you this aggravation with trying to update a multi value field. I have never/will never use multi value fields so I'm guessing here that FileData is the image, filename is the actual name of the file and filetype is .jpg, .gif, .bmp etc. None of this would be necessary if storing a path/filename.

    I'll look at the code more closely but mutli value fields and calculated fields are baaaaaaad news.

  5. #5
    carcement is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    15
    Alternatively, since MediaFire is irritating this is a link to the database in my dropbox:
    https://www.dropbox.com/s/1xd3sofufp...bmission.accdb

    The reasoning for storing the images is due to the sales force needing to be able to submit them directly from their tablets which travel with them outside the office. The images are used then deleted rather quickly, so I hope to not run into any issues with the size cap.

    The command37 button is intended to copy all records from submit to imgdest. I'll then create a new button to run a delete query on submit once they're appended to imgdest.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok here's what I've gotten when poking around with this.

    The code runs perfectly if and only if you are pulling ONE image for ONE record at a time

    In other words when you are pulling [Door 1].FileName it works fine, if you are pulling [door 1].filename and [door 2].filename in the same query, the base query becomes NON UPDATABLE

    so you are going to have to use something more like this:

    Code:
    Dim dbs As dao.Database, rst As Recordset, rstInsert As Recordset
    Dim sSQL As String
    Dim sSQLSource As String
    Dim i
    
        Set dbs = CurrentDb
        
        sSQLSource = "SELECT Route, Account, [Date], "
        sSQLSource = sSQLSource & "[Door 1].FileData, [Door 1].FileName, [Door 1].FileType, "
        sSQLSource = sSQLSource & "[Door 2].FileData, [Door 2].FileName, [Door 2].FileType, "
        sSQLSource = sSQLSource & "[Door 3].FileData, [Door 3].FileName, [Door 3].FileType, "
        sSQLSource = sSQLSource & "[Door 4].FileData, [Door 4].FileName, [Door 4].FileType, "
        sSQLSource = sSQLSource & "[Door 5].FileData, [Door 5].FileName, [Door 5].FileType, "
        sSQLSource = sSQLSource & "[Door 6].FileData, [Door 6].FileName, [Door 6].FileType, "
        sSQLSource = sSQLSource & "[Door 7].FileData, [Door 7].FileName, [Door 7].FileType, "
        sSQLSource = sSQLSource & "[Door 8].FileData, [Door 8].FileName, [Door 8].FileType, "
        sSQLSource = sSQLSource & "[Door 9].FileData, [Door 9].FileName, [Door 9].FileType, "
        sSQLSource = sSQLSource & "[Door 10].FileData, [Door 10].FileName, [Door 10].FileType, "
        sSQLSource = sSQLSource & "[Door 11].FileData, [Door 11].FileName, [Door 11].FileType, "
        sSQLSource = sSQLSource & "[Door 12].FileData, [Door 12].FileName, [Door 12].FileType, "
        sSQLSource = sSQLSource & "[Door 13].FileData, [Door 13].FileName, [Door 13].FileType, "
        sSQLSource = sSQLSource & "[Door 14].FileData, [Door 14].FileName, [Door 14].FileType, "
        sSQLSource = sSQLSource & "[Door 15].FileData, [Door 15].FileName, [Door 15].FileType, "
        sSQLSource = sSQLSource & "Comments "
        sSQLSource = sSQLSource & "FROM Submit"
        
        Set rst = dbs.OpenRecordset(sSQLSource)
      
        If rst.RecordCount <> 0 Then
            Do While rst.EOF <> True
                Debug.Print rst!route
                If DCount("*", "imgdest", "[Route] = " & rst!route) = 0 Then
                    Debug.Print "    NO EXISTING RECORD"
                    Debug.Print "    " & rst![door 1.filename]
                    If Not IsNull(rst![door 1.filename]) Then
                        Debug.Print "    Image in 1"
                        Set rstInsert = dbs.OpenRecordset("SELECT Route, Account, [Date], [Door 1].FileData, [Door 1].FileName, [Door 1].FileType, Comments FROM imgDest")
                        rstInsert.AddNew
                        rstInsert![route] = rst![route]
                        rstInsert![account] = rst![account]
                        rstInsert![Date] = rst![Date]
                        rstInsert![Door 1.Filedata] = rst![Door 1.Filedata]
                        rstInsert![door 1.filename] = rst![door 1.filename]
                        rstInsert![comments] = rst![comments]
                        rstInsert.Update
                        rstInsert.Close
                    End If
                    'REPEAT THE CODE BELOW FOR EACH OF YOUR POSSIBLE IMAGE FIELDS
                    If Not IsNull(rst![door 2.filename]) Then
                        Debug.Print "    Image in 2"
                        Set rstInsert = dbs.OpenRecordset("SELECT Route, [Door 2].FileData, [Door 2].FileName, [Door 2].FileType FROM imgDest WHERE [route] = " & rst!route)
                        rstInsert.Edit
                        rstInsert![Door 2.Filedata] = rst![Door 2.Filedata]
                        rstInsert![door 2.filename] = rst![door 2.filename]
                        rstInsert.Update
                        rstInsert.Close
                    End If
                  
                    Next i
                End If
                rst.MoveNext
            Loop
        Else
            MsgBox "NO records to process"
        End If
    rst.Close
    Set dbs = Nothing
    NOTE: I did not check it for places beyond the second image because you only had 1 record with multiple images in your database but it should work correctly even if you have images, say, in image 1 and image 15 with nothing in between.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    one more thing, you can probably build a loop to go through image slot 2 through 15 but it might be a little clunkier in terms of visually being able to see each section of code but it should still be able to be done if it's an issue for you to make the code a little more clean.

  8. #8
    carcement is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    15
    Hm, I get a "Next without For" error using your code.

  9. #9
    carcement is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    15
    After removing the "Next i" line, the code works perfectly for Doors 1 and 2, I assume I'll need the Next i line to continue with the following doors however I get a "Next without For" error while it's there and I'm quite new to VB.

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I started messing around with setting up a loop for items 2 through 15 but didn't have the time just take out the NEXT I portion of code and it should run just fine (the hint is in the error and it should have highlighted the section of code it was choking on)

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Yes you'll have to run this section of code:

    Code:
    'REPEAT THE CODE BELOW FOR EACH OF YOUR POSSIBLE IMAGE FIELDS                 
    If Not IsNull(rst![door 2.filename]) Then                     
        Debug.Print "    Image in 2"                     
        Set rstInsert = dbs.OpenRecordset("SELECT Route, [Door 2].FileData, [Door 2].FileName, [Door 2].FileType FROM imgDest WHERE [route] = " & rst!route)                     
        rstInsert.Edit                     
        rstInsert![Door 2.Filedata] = rst![Door 2.Filedata]                     
        rstInsert![door 2.filename] = rst![door 2.filename]                     
        rstInsert.Update                     
        rstInsert.Close                
    End If
    for each of your items labeled 2 through 15, alternately I believe if you could rename the subfields of your mutli value fields from filename filetype filedata to filename1, filetype1, filedata1, filename2, filetype2, filedata2 etc your original code would work. I am fairly certain that the duplicate field names within a mutli value field are what's causing your dataset to be non updatable.

  12. #12
    carcement is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    15
    Even with that section of code repeated with the rest of the door numbers it only handles the first two attachments slots "Door 1" and "Door 2". It doesn't give an error, just says completed and ignores the remaining doors.

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    you gave me an example set of data where there was only a picture in slot 1 and 2, the code will not do anything if there is no value in [Door x].[FileName]. If you actually have a record that has images in doors 3 through 15 and it's not copying them that's a different matter. Can you paste your modified code.

  14. #14
    carcement is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    15
    Code:
    Public Sub InsertData()
    Dim dbs As dao.Database, rst As Recordset, rstInsert As Recordset
    Dim sSQL As String
    Dim sSQLSource As String
    Dim i
    
    
        Set dbs = CurrentDb
        
        sSQLSource = "SELECT Route, Account, [Date], "
        sSQLSource = sSQLSource & "[Door 1].FileData, [Door 1].FileName, [Door 1].FileType, "
        sSQLSource = sSQLSource & "[Door 2].FileData, [Door 2].FileName, [Door 2].FileType, "
        sSQLSource = sSQLSource & "[Door 3].FileData, [Door 3].FileName, [Door 3].FileType, "
        sSQLSource = sSQLSource & "[Door 4].FileData, [Door 4].FileName, [Door 4].FileType, "
        sSQLSource = sSQLSource & "[Door 5].FileData, [Door 5].FileName, [Door 5].FileType, "
        sSQLSource = sSQLSource & "[Door 6].FileData, [Door 6].FileName, [Door 6].FileType, "
        sSQLSource = sSQLSource & "[Door 7].FileData, [Door 7].FileName, [Door 7].FileType, "
        sSQLSource = sSQLSource & "[Door 8].FileData, [Door 8].FileName, [Door 8].FileType, "
        sSQLSource = sSQLSource & "[Door 9].FileData, [Door 9].FileName, [Door 9].FileType, "
        sSQLSource = sSQLSource & "[Door 10].FileData, [Door 10].FileName, [Door 10].FileType, "
        sSQLSource = sSQLSource & "[Door 11].FileData, [Door 11].FileName, [Door 11].FileType, "
        sSQLSource = sSQLSource & "[Door 12].FileData, [Door 12].FileName, [Door 12].FileType, "
        sSQLSource = sSQLSource & "[Door 13].FileData, [Door 13].FileName, [Door 13].FileType, "
        sSQLSource = sSQLSource & "[Door 14].FileData, [Door 14].FileName, [Door 14].FileType, "
        sSQLSource = sSQLSource & "[Door 15].FileData, [Door 15].FileName, [Door 15].FileType, "
        sSQLSource = sSQLSource & "Comments "
        sSQLSource = sSQLSource & "FROM Submit"
        
        Set rst = dbs.OpenRecordset(sSQLSource)
      
        If rst.RecordCount <> 0 Then
            Do While rst.EOF <> True
                Debug.Print rst!route
                If DCount("*", "imgdest", "[Route] = " & rst!route) = 0 Then
                    Debug.Print "    NO EXISTING RECORD"
                    Debug.Print "    " & rst![door 1.filename]
                    If Not IsNull(rst![door 1.filename]) Then
                        Debug.Print "    Image in 1"
                        Set rstInsert = dbs.OpenRecordset("SELECT Route, Account, [Date], [Door 1].FileData, [Door 1].FileName, [Door 1].FileType, Comments FROM imgDest")
                        rstInsert.AddNew
                        rstInsert![route] = rst![route]
                        rstInsert![account] = rst![account]
                        rstInsert![Date] = rst![Date]
                        rstInsert![Door 1.Filedata] = rst![Door 1.Filedata]
                        rstInsert![door 1.filename] = rst![door 1.filename]
                        rstInsert![comments] = rst![comments]
                        rstInsert.Update
                        rstInsert.Close
                    End If
                    'REPEAT THE CODE BELOW FOR EACH OF YOUR POSSIBLE IMAGE FIELDS
                    If Not IsNull(rst![door 2.filename]) Then
                        Debug.Print "    Image in 2"
                        Set rstInsert = dbs.OpenRecordset("SELECT Route, [Door 2].FileData, [Door 2].FileName, [Door 2].FileType FROM imgDest WHERE [route] = " & rst!route)
                        rstInsert.Edit
                        rstInsert![Door 2.Filedata] = rst![Door 2.Filedata]
                        rstInsert![door 2.filename] = rst![door 2.filename]
                        rstInsert.Update
                        rstInsert.Close
                    End If
                    If Not IsNull(rst![door 3.filename]) Then
                        Debug.Print "    Image in 3"
                        Set rstInsert = dbs.OpenRecordset("SELECT Route, [Door 3].FileData, [Door 3].FileName, [Door 3].FileType FROM imgDest WHERE [route] = " & rst!route)
                        rstInsert.Edit
                        rstInsert![Door 3.Filedata] = rst![Door 3.Filedata]
                        rstInsert![door 3.filename] = rst![door 3.filename]
                        rstInsert.Update
                        rstInsert.Close
                    End If
                End If
                rst.MoveNext
            Loop
        Else
            MsgBox "NO records to process"
        End If
    rst.Close
    Set dbs = Nothing
    End Sub
    I just added door 3 to check if it would work, I added an image to door three in my first test entry in submit and it ignores it and just handles doors 1 and 2 for all entries.

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Have you got another sample, I don't know how you're getting those images into your database. If you could upload a sample with at least 1 record with an image in every slot that would help tremendously.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 11-15-2013, 07:01 AM
  2. import table to append to a table in Access 2010
    By Stephanie53 in forum Forms
    Replies: 2
    Last Post: 05-23-2013, 03:40 PM
  3. Linked Table Append Query Problem.
    By dlab85 in forum Queries
    Replies: 4
    Last Post: 03-13-2013, 07:30 AM
  4. Replies: 0
    Last Post: 11-30-2010, 12:23 PM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums