1

I need fetch data from an MS Access database using a SQL query in a VB program.

The result is stored in a recordset, one of the fields is f100, it's calculated by connecting multiple strings.

If the length of f100's value is less than 255, the result is correct, but if it's longer than 255, the result is wrong: f100 just remains 255 characters in length and subsequent characters disappear.

My SQL statement is like this:

Select iif(f1=1,'123','')+iif(f2=1,'456','')+...... as f100 from Table1

My program is like this:

Dim Conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strConn$, strSQL$
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\try.mdb;Persist Security Info=False"
Conn.CursorLocation = adUseClient
Conn.Open strConn
strSQL="Select iif(f1=1,'123','')+iif(f2=1,'456','')+...... as f100 from Table1"
With rst
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.Open strSQL, Conn
End With

'here rst.fields("f100") remains a max of 255 char
'debug.print rst.fields("f100")

rst.Close
Conn.Close

Is there any solution that hold a long calculated string in recordset just by SQL query?

Lee Mac
  • 15,615
  • 6
  • 32
  • 80
bob
  • 11
  • 3
  • Possible duplicate. Have you verified the actual length of string using the len() function? https://stackoverflow.com/questions/17927042/concatenation-and-max-length-of-string-in-vba-access – Mike May 06 '19 at 14:03
  • Yes, I have verified that, If I open a .mdb file and run the query statement in Access, the result is correct. If I fill data to recordset by the same query statement in programming environment, the result is wrong. – bob May 07 '19 at 16:32

0 Answers0