none
Random Runtime Error '3197' Assign text to DAO.Recordset ODBC MySQL VARCHAR() RRS feed

  • Question

  • I'm trying to assign text from a textbox to a dao.recordset record field that is attached to a MySQL VARCHAR() through ODBC. It's failing with an access conflict Runtime Error '3197'. At first i thought it was because i was converting a multiline textbox down to a single line replacing vbNewLine and vbCrLf with \n. I tried using a number of different replacement characters. At first i thought it was letting me pass in a straight string like "abc", but then it started failing to do that as well. i've tried all sorts of quotation gimmicks like """" &  str_data & """" or "'" & str_data & "'".  i started by passing variables directly, and then i broke it down. i'm really at a loss. there hasn't been a place in my 50000 lines of code that has had this problem. any suggestions would be appreciated. I'm still in dev phase, so no one else is using the program.

    Public Function Account_Notes(transaction_index As Integer)
    '************************************************************************************
    '************************************************************************************
    '************************************************************************************
    '************************************************************************************
    '************************************************************************************
        
        Dim strSQL As String, str_slot As String, str_data As String
                
        Dim str_client_id_code39 As String
            
        Dim str_month As String, str_year As String, str_date As String
       
        Dim rst_ctl As DAO.Recordset
                        
    
    '************************************************************************************
    '************************************************************************************
    '************************************************************************************
    '************************************************************************************
    '************************************************************************************
            
        Dim input_account_notes(7) As String
        input_account_notes(1) = "input_account_monthly_notes"
        input_account_notes(2) = "input_account_ancilliary_notes"
        input_account_notes(3) = "input_account_awards_notes"
        input_account_notes(4) = "input_account_sale_notes"
        input_account_notes(5) = "input_account_supplies_notes"
        input_account_notes(6) = "input_account_other_notes"
        input_account_notes(7) = "input_account_balance_forward_notes"
    
    
        Dim fld_account_notes(7) As String
        fld_account_notes(1) = "account_monthly_notes"
        fld_account_notes(2) = "account_ancilliary_notes"
        fld_account_notes(3) = "account_awards_notes"
        fld_account_notes(4) = "account_sale_notes"
        fld_account_notes(5) = "account_supplies_notes"
        fld_account_notes(6) = "account_other_notes"
        fld_account_notes(7) = "account_balance_forward_notes"
    
            
        str_client_id_code39 = Forms!frm_client!frm_client_information("input_client_id_code39")
    
        strSQL = "SELECT *" & _
                " FROM [Client Account]" & _
                " WHERE [account_client_id_code39] = " & "'" & str_client_id_code39 & "'" & _
                " ORDER BY [account_id] ASC"
    
        Set rst_ctl = dbs_mac.OpenRecordset(strSQL, Type:=dbOpenDynaset)
        
        If rst_ctl.BOF Eqv True Then
        
            [Procedures Core].EmergencyExitFunction ("unable to load account record$.")
            
            GoTo Line1
            
        End If
        
        rst_ctl.MoveLast
        
        
        str_month = [Procedures Utility].MonthToNumericStr(Forms!frm_client!frm_client_account("input_account_month"))
        
        str_year = Forms!frm_client!frm_client_account("input_account_year")
        
        
        str_date = str_year & "-" & str_month & "-01"
        
        rst_ctl.FindFirst "[account_tracking] = " & "'" & str_date & "'"
        
        If rst_ctl.NoMatch Eqv True Then
        
            [Procedures Core].EmergencyExitFunction ("could not find date in account tracking.")
            
            GoTo Line1
                    
        End If
        
        
        str_data = Forms!frm_client!frm_client_account(input_account_notes(transaction_index))
        
        str_slot = fld_account_notes(transaction_index)
        
        
        rst_ctl.Edit
        rst_ctl.Fields(str_slot) = """" & str_data & """"
        rst_ctl.Update
            
        
    Line1:
        
        If Not (rst_ctl Is Nothing) Then
        
            rst_ctl.Close
            
            Set rst_ctl = Nothing
        
        End If
          
    
    End Function
    

    Sunday, February 25, 2018 6:57 AM

Answers

  • the answer to my question is a simple one. MySQL requires that all tables to be updated contain a timestamp field with default=CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. the requirement is poorly documented and is selective in nature. if you are having troubles with editing tables as well, this could very well fix that. it has for me.

    the following documentation expresses this explicitly, but doesn't really go into any detail

    https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html

    using workbench edit a table. at the bottom of the list add a new field. declare the field timestamp--DELETE THE BRACKETS. add default=CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. move the field to your desired place in the list. always click off of the entry before you run apply. this will fill your field with the creation date/time.

    and there you have it. simple. 7days simple.
      
    • Marked as answer by Xittenn Friday, March 16, 2018 2:08 AM
    • Edited by Xittenn Friday, March 16, 2018 2:08 AM
    Friday, March 16, 2018 2:07 AM

All replies

  • two side notes i) naturally i did rebuild everything. ii) it does write the information to the table. but it fails after it writes it.

    Sunday, February 25, 2018 7:22 AM
  • Hi Xittenn,

    If you set str_data as “abc”, and make a test with code below, will you receive any error?

    To check whether it is related with this specific field, could you try to update other fields?

    If you update the field from MYSQL database with the same value for str_data, will you receive any error?

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 26, 2018 6:47 AM
  • >> no one else is using the program.

    The 'Conflict' errors typically mean that a record is being edited in multiple places, without intermediate saves. That can be caused by a conflict between users and code, or by a conflict between code in multiple places editing the same record, or parent and child records in related tables.

    How is the Account_Notes procedure being called, and what is happening in the database when it gets called?  If the code and/or a user is making changes to other fields in the same record in the Client Account table, you need to coordinate the data saves between the calling procedure and the Account_Notes function.  There are a number of ways to go about this.  A couple that come to mind are:

    1.  Save the record in the calling procedure, immediately before calling Account_Notes

    2.  Rather than saving the data in Account_Notes, use the function simply to return the value of strData to the calling procedure, and save it with the rest of the data in the calling procedure.


    Miriam Bizup Access MVP


    • Edited by mbizup MVP Monday, February 26, 2018 11:19 AM
    Monday, February 26, 2018 11:15 AM
  • i) onclick--execute function pop-up. pass textbox byref as object. object is stored in a global(for review) 

    ii) text from table is taken in string form and the character "|" is replaced with vbNewLine and is stored in a large textbox with enter key set to new line

    iii) user edits text. when user is done, user clicks accept.

    iv) accept call sub sets the stored textbox as such gctl_client_note.Text = str_output 

    v) onUpdate calls Account_Notes where vbNewLine is replaced with the character "|" or w/e char and the text is saved to the table.

    the text always saves to the table. if i on error resume next, i am absolutely none the wiser. i have tried every logical lockingedit and option available to the recordset. there are no open recordsets, unless they are somehow being kept open in the background. i have tried inserting dumby strings at all points. it doesn't happen with any other code. on rare occasions it succeeds, making testing more difficult. i tried setting my global to nothing at the two points of contention. the only thing that seems to work, and i would have to test further, is muting all code related to multiline. i have no idea how that would make sense. 


    • Edited by Xittenn Monday, February 26, 2018 9:25 PM
    Monday, February 26, 2018 9:22 PM
  • I see you are doing a .MoveLast. Maybe doing a .MoveFirst right after that will get you back into position for your FindFirst

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Monday, February 26, 2018 10:57 PM
  • i found that that was unnecessary, so i stopped doing it. i only do a move first when the result is a single record--for no reason. but no, that doesn't help. again, i non figuratively have a dozen main tables, with a ~dozen fields each. i have two dozen modules, with fifty-thousand lines of code. it's all dao with an odbc connection to a mysql backend. this is the very first problem i have not been able to find a solution for. as it stands, it does not affect my database in anyway if i just ignore it. obviously that isn't my preference, but it works.

    thanks for viewing my issue.

     
    • Edited by Xittenn Tuesday, February 27, 2018 6:19 AM
    • Marked as answer by Xittenn Monday, March 5, 2018 12:02 AM
    • Unmarked as answer by Xittenn Friday, March 16, 2018 2:08 AM
    Tuesday, February 27, 2018 6:18 AM
  • Hi Xittenn,

    It seems you have found a workaround, if so, I would suggest you mark the helpful reply as answer to close this thread.

    If not, please feel free to let us know.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 28, 2018 5:47 AM
  • i allowed the error through. it was still waiving. i changed the odbc connector 'details' all to false. the flag stopped. i can no longer recreate the error anywhere in my program. if i ever get an actual answer, i'll probably post it.
    Monday, March 12, 2018 9:49 AM
  • it broke again, as i expected it to. it's currently working, after having given every recordset a unique name. my idea here is that the generic recordset rst_ctl in use was conflicting when i called the update function--from another function. both were using rst_ctl. now one is rst_ctl_client_information_nav_id_code39 and the other is rst_ctl_update_client_information for example. this still may fail in the end.
    Tuesday, March 13, 2018 9:54 AM
  • Hi Xittenn,

    I would suggest you post a new thread, and share us a simple project which could reproduce your issue.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, March 13, 2018 9:57 AM
  • i was unable to reproduce the problem. if i had otherwise  been able to, i would have queried google. it currently seems to be running with default odbc detail options, and with uniquely named recordsets across functions. otherwise i'll just ignore the error for all dao.recordset updates.

    • Edited by Xittenn Wednesday, March 14, 2018 12:09 AM
    Wednesday, March 14, 2018 12:02 AM
  • the answer to my question is a simple one. MySQL requires that all tables to be updated contain a timestamp field with default=CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. the requirement is poorly documented and is selective in nature. if you are having troubles with editing tables as well, this could very well fix that. it has for me.

    the following documentation expresses this explicitly, but doesn't really go into any detail

    https://dev.mysql.com/doc/connector-odbc/en/connector-odbc-usagenotes-apptips-microsoft-access.html

    using workbench edit a table. at the bottom of the list add a new field. declare the field timestamp--DELETE THE BRACKETS. add default=CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. move the field to your desired place in the list. always click off of the entry before you run apply. this will fill your field with the creation date/time.

    and there you have it. simple. 7days simple.
      
    • Marked as answer by Xittenn Friday, March 16, 2018 2:08 AM
    • Edited by Xittenn Friday, March 16, 2018 2:08 AM
    Friday, March 16, 2018 2:07 AM