Character Limitations and Do.CMD RunSQL "Insert ... "

[RobertSOakes]

I have run into a problem with a form I am developing for a simple medical records system. I am attempting to insert a text string into a Memo Data column in a table, however, the INSERT statement I using is truncating the length of the comment to 255 characters only (even the Memo data type is capable of storing a much longer string).

Does anyone know of a quick work around to this problem? I have tried explicitly declaring the variable a string and attempting to integrate it that way, but I have not had success. Also, is anyone aware if this same truncation a part of the UPDATE procedure?

Any guidance would be hugely appreciated.

Thanks,

Rob Oakes

 

[bigstav]

INSERT with Long Strings

Hi Rob, i have no problems doing this. I am using Access 2002 (SP03) and the following works fine (The Notes Field is Memo datatype) :-


Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String

Set Conn = CurrentProject.Connection
Set rs = New ADODB.Recordset

sql = "INSERT INTO tblPeople (SURNAME, Notes) VALUES ('SMITH', 'pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp');"

Conn.Execute sql

The string i am inserting into the Table is 500+ characters and it goes in fine - it may be the way you are generating your string data ?.

TO ADD TO THIS :- The RunSQL Command in Access cannot exceed 256 characters for the built string FROM A MACRO, so you will need to either run "DoCmd.RunSQL" from VBA, or use VBA Code as above for inserts / updates.


Hope this helps.

 

[RobertSOakes]

Using ADO appears to work, but still truncates

Thank you very much for the recommendation. I have tried (both as a Public sub and as a part of the private sub) without any success. I get no errors when inserting and updating, however, it still truncates the text. It is very possible that there is something wrong in the way that I am constructing the string. Here is the insert instruction for one of the values which I am working on. Is there anything which jumps out as a problem?

Again, thank you for any help. I really appreciate it.

Rob Oakes

If modInstruction.Value = "CareIssues" Then

Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String

Set Conn = CurrentProject.Connection
Set rs = New ADODB.Recordset

If EditInstruction = 1 Then
Dim tmpCareIssues As String
tmpCareIssues = Replace(txtCareIssuesComment.Value, "'", "''")
SQL = "INSERT INTO [Medical Frequent Fliers_Care Issues](MedicalStayID, PatientID, [Comment Date], [Care Issues]) VALUES(" & [InPatient].Value & ", " & [current patient].Value & ", #" & [TodayDate] & "#, '" & tmpCareIssues & "');"
Conn.Execute SQL
End If

If EditInstruction = 2 Then
SQL = "UPDATE [Medical Frequent Fliers_Care Issues] SET [Care Issues] ='" & Replace(txtCareIssuesComment.Value, "'", "''") & "', [Modified On] = #" & TodayDate.Value & "# WHERE CareIssueID=" & ListCareIssues.Value & ";"
Conn.Execute SQL
End If

If EditInstruction = 3 Then
DoCmd.SetWarnings False
SQL = "DELETE FROM [Medical Frequent Fliers_Care Issues] WHERE [CareIssueID] = " & Me!ListCareIssues.Column(0)
Conn.Execute SQL
DoCmd.SetWarnings True
End If

Else
Exit Sub

End If

 

[bigstav]

Try This !

Hi Rob, still not sure why you have a problem, your code looks ok to me. Please see attached .mdb - i have tried to emulate your situation as close as i can and it works fine.

There is just 1 Table, 1 Query (to show length of string in Table), 1 Form (to show long string text displayed), and 1 Module (to do the insert).

Have a look and see what you think.

 

[WayneRyan]

Robert,

When there are intermediate things such as queries, single-quotes, double-quotes:

When all else fails ...

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Select * from YourTable")
rst.AddNew
    rst!Field1 = ...
    rst.Field2 = ...
Rst.Update

Wayne