Hi,
My application background using access database, using Microsoft. Jet. The OLEDB. 4.0 way to connect to the database, the select query, there is a field of value is obtained by string concatenation, now found that if the length of the string is greater than 255, assigned to you, the result is wrong, will be truncated,

The same select statement execution in the access database, the results returned to normal, shows a select statement itself is no problem, please, the length of the string is greater than 255 results how to assign a value to you, thank you!

Procedure is as follows:

Dim Conn As New ADODB. Connection
Dim RST As New ADODB. You
Dim strConn $, strSQL $, strExpression $

StrConn="Provider=Microsoft. Jet. The OLEDB. 4.0. The Data Source=c: \ try. MDB; Persist Security Info=False "
Conn. CursorLocation=adUseClient
Conn. Open strConn

'this shall be the string expression here, in order to simplify the problem, I use a fixed string replacement, a total length of 554
strExpression="'1,2,3,4,5,6,7,8,9,11,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,201,203,204,205,206'"

StrSQL="Select *," & amp; StrExpression & amp; "As MayViewFields from SheetList
"
With RST
The CursorType=adOpenStatic
. LockType=adLockOptimistic
. CursorLocation=adUseClient
. Open strSQL, Conn
End With

The Debug. Print RST. Fields (" MayViewFields ")
'print string, only 255 characters: 1,2,3,4,5,6,7,8,9,11,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,
'that is, the total length of 554 string, through SQL select statement after the assigned to field, only 255 characters, other truncated lost

RST. Close
Conn. Close

CodePudding user response:

Access the longest only is 255

CodePudding user response:

Quotes are said to conversion, you this kind of situation, try to use an array to process the data,

CodePudding user response:

Access the longest is only 255, suggested in a data type, attachments, and can solve the problem

CodePudding user response:

What kind of query will type in such a long keywords? I have not come across such a practical application demand, whether can try from the source to change the thinking of the solution to this problem, but not limited to the database,