Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Cannot Update Field . . . Runtime error 3113

    The error says "cannot update 'StreetName'; field not updatable Runtime error 3113

    Any ideas would be greatly appreciated. Thanks . . . Rick

    Code:
    Private Sub Command1_Click()
    Dim strSQL As String
    strSQL = "UPDATE (tblSoCalMLS_Download) SET tblSoCalMLS_Download.SteetName = UCase(tblSoCalMLS_Download.StreetName), tblSoCalMLS_Download.City = UCase(tblSoCalMLS_Download.City), tblSoCalMLS_Download.OfficeList = UCase(tblSoCalMLS_Download.OfficeList), tblSoCalMLS_Download.P_OfficeName = UCase(tblSoCalMLS_Download.P_OfficeName), tblSoCalMLS_Download.OfficeSell = UCase(tblSoCalMLS_Download.OfficeSell), tblSoCalMLS_Download.P_OfficeNameSell = UCase(tblSoCalMLS_Download.P_OfficeNameSell)"
    DoCmd.RunSQL strSQL
    End Sub

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem might be that your sql is all on one line which makes it really hard for the database to understand (*)

    try it like this --
    Code:
    UPDATE tblSoCalMLS_Download 
       SET SteetName = UCase(StreetName)
         , City = UCase(City)
         , OfficeList = UCase(OfficeList)
         , P_OfficeName = UCase(P_OfficeName)
         , OfficeSell = UCase(OfficeSell)
         , P_OfficeNameSell = UCase(P_OfficeNameSell)






    (*) just kidding
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Rick, if the re-arranged SQL does not fix it, could you let us know what kind of table "tblSoCalMLS_Download" is? I'm thinking that it might be a text file that has been linked, or else an Excel spreadsheet that has been linked. I understand that in Access 2003, Excel spreadsheets that have been linked as a table in Access can not be updated.

  4. #4
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    No errors . . . but nothing happens?

    Code:
    Private Sub Command1_Click()
    Dim strSQL As String
    strSQL = "UPDATE tblSoCalMLS_Download"
    Set tblSoCalMLS_Download.SteetName = UCase(tblSoCalMLS_Download.STREETNAME)
    tblSoCalMLS_Download.City = UCase(tblSoCalMLS_Download.City)
    tblSoCalMLS_Download.OFFICELIST = UCase(tblSoCalMLS_Download.OFFICELIST)
    tblSoCalMLS_Download.P_OFFICENAME = UCase(tblSoCalMLS_Download.P_OFFICENAME)
    tblSoCalMLS_Download.OFFICESELL = UCase(tblSoCalMLS_Download.OFFICESELL)
    tblSoCalMLS_Download.P_OFFICENAMESELL = UCase(tblSoCalMLS_Download.P_OFFICENAMESELL)
    DoCmd.RunSQL strSQL
    End Sub

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Rick,
    if the re-arranged SQL does not fix it, could you let us know what kind of table "tblSoCalMLS_Download" is? I'm thinking that it might be a text file that has been linked, or else an Excel spreadsheet that has been linked. I understand that in Access 2003, Excel spreadsheets that have been linked as a table in Access can not be updated.
    What version of Access are you using? Is tblSoCalMLS_Download a text file you have linked to? Is it a linked table, or is it a local table (not a linked table). Just giving us the SQL again really does nothing to help us help you.

  6. #6
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    The table is a "Get External Data" from an ASCII delimited text file. There is no linking. The fields I'm working with are simple text fields. The table is a local table of 409,655 records.
    Most of the time the table will not have that many records, however, I don't think that has anything to do with it.

    Thanks . . .

  7. #7
    Join Date
    Mar 2004
    Location
    California
    Posts
    502
    On another note . . . I kept getting "expecting end of statement" errors and other syntax errors.

    The current syntax above does not return any errors. It doesn't work either. Ha!

    How about the command button. I deleted one button and started new from another butter and changed the Command3 to Command2. Any problems there?

    Thanks . . . Rick

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Copy that SQL string into the SQL design view of a new query and run it from there. Before running the query, be sure that the Warnings is set to true. (DoCmd.SetWarnings = True) Do you get any messages? What are they? Does it change the data to upper case?
    Could you post a sample database with this table in it (or part of the table) and this SQL code?
    Where are you getting the "expecting end of statement" errors? When running VBA code? When doing something from the Immediate window? What do the lines look like that Access is expecting end of the statement?

    Changing from Command3 to Command2 should not make a difference, but the newer versions of Access (XP, 2003, and 2007) have something in them, unless they have been shut off, that changes things within the database that refer to something like a table, and maybe even a button. So, changing from Command3 to Command2 might mess something up, but I really don't think that is this problem.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Rick Schreiber
    How about the command button. I deleted one button and started new from another butter and changed the Command3 to Command2. Any problems there?
    right click the button and then build event...
    Sometimes Access can't find what it's looking for when you change the buttons name or delete and re-create blah blah blah.
    It just refreshes it's memory

    This would explain why the button is doing nothing - because it thinks it has no code to run.
    George
    Home | Blog

  10. #10
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Update query resolved. See Example . .

    This is the SQL that I was able to get to work, suggested by Rudy if I remember correctly. Once one understands this it becomes far easier.

    I sincerely appreciate all of the assistance. I'm learning.

    Code:
    UPDATE tblSoCalMLS_Download 
    SET tblSoCalMLS_Download.STREETNAME = UCase([streetname]), 
    tblSoCalMLS_Download.CITY = UCase([city]),
    tblSoCalMLS_Download.OFFICELIST = UCase([officelist]), 
    tblSoCalMLS_Download.P_OFFICENAME = UCase([P_officename]), 
    tblSoCalMLS_Download.OFFICESELL = UCase([officesell]), 
    tblSoCalMLS_Download.P_OFFICENAMESELL = UCase([P_officenamesell]);
    Hope this helps someone. Rick

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you do remember correctly, see http://www.dbforums.com/showpost.php...42&postcount=2

    note that i did not use table prefixes all over the place
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I figured this new SQL works because the lines are much shorter.

Posting Permissions

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