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

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

Answer 1

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.

Answer 2

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 [e-mail].

Answer 3

>> 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

Answer 4

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. 

Answer 5

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

Answer 6

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.

 

Answer 7

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 [e-mail].

Answer 8

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.

Answer 9

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.

Answer 10

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 [e-mail].

Answer 11

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.

Answer 12

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.