Search code examples
sqlvbams-accesslivelink

Memo truncated from Access database to VBA string

I have a memo field in my Access (2003) database to store the EntryID of folders from Outlook (about 750 characters). I'm trying to get back that string to move some mail to that folder ID with this code:

Dim myNameSpace As Outlook.NameSpace
Dim StoreID As String
Dim target as String 'This is the long EntryID string
Dim objMail as mailitem 'some mail

Set myNameSpace = Application.GetNamespace("MAPI")
StoreID = Application.GetNamespace("MAPI").folders("LiveLink").StoreID
Set dossier = myNameSpace.GetFolderFromID(target, StoreID)
objMail.Move dossier

The target var only has the first 252 characters instead of 748 in this case. What is interesting is that Outlook will still find the right folder IF there is no other folder available with the same ~255 first characters. But in some cases, it crashes because there are more than one. I'm using a Recordset to get the memo from the database. Here is my SQL:

SELECT EntryID FROM Folder

I finally found some informations on this behavior: http://allenbrowne.com/ser-63.html. However, I'm not using any union or anything particular in my query as you can see...

Why is it still being truncated?

Original memo/string in the database:

00000000CE5B922DF5D7654C993FFDB4FF79A7A00100000057010000307E7E2D317E305C307E4C6976656C696E6B204851457E307E2D315C307E4C6976656C696E6B204851457E2D357E305C307E4C6976656C696E6B204851457E313233373235387E2D355C307E4C6976656C696E6B204851457E31303233363334317E313233373235385C307E4C6976656C696E6B204851457E31323930393430387E31303233363334315C307E4C6976656C696E6B204851457E31343539333439307E31323930393430385C307E4C6976656C696E6B204851457E31383735353632377E31343539333439305C307E4C6976656C696E6B204851457E3131363434333236317E31383735353632375C307E4C6976656C696E6B204851457E3131363434333236347E3131363434333236315C307E4C6976656C696E6B204851457E3131363434333238397E3131363434333236345C307E4C6976656C696E6B204851457E3131363434333330337E313136343433323839

Truncated memo/object/field/string after the SQL query:

00000000CE5B922DF5D7654C993FFDB4FF79A7A00100000057010000307E7E2D317E305C307E4C6976656C696E6B204851457E307E2D315C307E4C6976656C696E6B204851457E2D357E305C307E4C6976656C696E6B204851457E313233373235387E2D355C307E4C6976656C696E6B204851457E3130323336333431

Solution

  • as shown in your link this shoud work

    Set rs = CurrentDb.Execute("SELECT Mid(EntryID,  1,250)  AS part1, " & _
       " Mid(EntryID,251,250)  AS part2," & _
       " Mid(EntryID,501,250)  AS part3," & _
       " Mid(EntryID,751,250)  AS part4," & _
       " Mid(EntryID,1001,250) AS part5 " & _
                          " FROM Folder;")
target = rs("part1") & rs("part2") & rs("part3") & rs("part4") & rs("part5")