mysql back end insert problem

[icemonster]

i have a field that i encrypt using AES but the trouble is with some certain special characters it doesn't seem to work. any ideas on how to get around this?

 

[mdlueck]

Please show us the VBA code which drives the query.

I have had the best success using ADODB.Command and ADODB.Parameters objects. Please see this MS Access optimized example code:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

You would just need a ADODB.Connection object correctly configured to your SQL BE DB.

 

[icemonster]

                            strSQL = "UPDATE tbl_client SET " _
                                    & "cli_medical_record_no = '" & Me.txtmedrecno & "', " _
                                    & "cli_lastname = '" & Replace(Me.txtlastname, "'", "''") & "', " _
                                    & "cli_firstname = '" & Replace(Me.txtfirstname, "'", "''") & "', " _
                                    & "cli_middlename = '" & Replace(Nz(Me.txtmiddlename, ""), "'", "''") & "', " _
                                    & "cli_id_lib_name_suffix = '" & Nz(Me.cbosuffix, 0) & "', " _
                                    & "cli_id_lib_gender = '" & Me.cbogender & "', " _
                                    & "cli_ssn = '" & AESencr(gbl_str69, Me.txtssn1) & "', " _
                                    & "cli_dob = " & Format(Me.dtdob, "yyyymmdd") & ", " _
                                    & "cli_id_lib_ethnicity = '" & Nz(Me.cboethnicity, 0) & "', " _
                                    & "cli_id_lib_marital_status = '" & Nz(Me.cbomaritalstatus, 0) & "' " _
                                    & "WHERE id_client = " & Me.txtcliid1 & ";"
                            .Execute strSQL, , adCmdText + adExecuteNoRecords

here you go sir, the problem here is when the ssn is then encrypted, it would work but sometimes it would just create an encrytion that doesn't allow me to pass it through insert.

 

[mdlueck]

The way you have it coded, without the use of ADO.Parametes objects, I am concerned the data will not survive the translation into a string datatype.

If you were to recoded it according to my INSERT example, then perhaps the variable survives in-tact since it never gets translated into a string.

 

[icemonster]

ok, i'll give yours a shot, but hwo do i specify the connection?

 

[mdlueck]

how do i specify the connection?

Here are my rough notes on building a connection object...

ADO.Connection object to SQL BE DB
http://www.access-programmers.co.uk/forums/showthread.php?t=231923#post1184259