Max string length for update querry?

[PL01]

Is there a maximum string length that can be used in an update querry with VBA? the string prints ok in the immediate window and the field being updated is a Memo, but the string comes out truncated anyway. Any work around on this? I was able to use a long string with an Insert querry in another program.

 

[genomon]

Maybe some insight here:

http://office.microsoft.com/en-us/access-help/access-2007-specifications-HA010030739.aspx

Beir bua agus beannacht!

 

[lameid]

I believe there is a limit more so on the VBA string side than the query side.

Having said that if you are using 64 bit Access 2010 you could try the new string datatype intended for use with API's.

Workarounds...

You might have better luck assigning the value to a recordset than a query.... I don't know just a guess. The trick would be to assign the long expression and not a string value.

 

[MajP]

It is about 64k. You can test this

Public Sub TestInsert()
  Dim str As String
  Dim strSql As String
  Dim I As Integer
  str = String(64000, "X")
  strSql = "INSERT INTO tblOne (fldMemo) values ('" & str & "')"
  CurrentDb.Execute strSql
  MsgBox Len(DLookup("fldMemo", "tblOne"))
 End Sub

This breaks around 64500