Memo truncated from Access database to VBA string

1
votes

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

2
sqlvbams-accesslivelink
which is the code around 'SELECT EntryID FROM Folder' ?? – Luis Siquot
Set rs = CurrentDb.Execute("SELECT EntryID FROM Folder"), and then target = rs("EntryID") – dan
paste a bit more!!! what about msgbox(rs("entryID")) – Luis Siquot
I'm not a SQL guru, but it seems like I could use some of that: bytes.com/topic/access/answers/…. I don't know if it is optimal however, and the EntryID doesn't always have the same length, so this could be a problem. – dan
rs("EntryID") only contains the first 251 characters. The problem isn't the string or my code, it's being truncated right from the SQL query. – dan

2 Answers

1
votes

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")
0
votes

i have implemented this c# code:

  private string GetMemoField(string TableName, string FieldName, string IdentityFieldName, string IdentityFieldValue, OleDbConnection conn)
    {
        string ret = "";

        OleDbCommand cmd1 = new OleDbCommand("SELECT " + FieldName + " FROM “ + TableName + “ WHERE " + IdentityFieldName + "=" + IdentityFieldValue, conn);

                var reader = cmd1.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);  // Create the DataReader that will get the memo field one buffer at a time

        if (reader.Read())
        {
            long numberOfChars = reader.GetChars(/*Field pos*/ 0, 0, null, 0, 0);   // Total number of memo field's chars

            if (numberOfChars > 0)
            {
                int bufferSize = 1024;
                char[] totalBuffer = new char[64*bufferSize];    // Array to hold memo field content

                long dataIndex = 0;

                do
                {

                    char[] buffer = new char[bufferSize];   // Buffer to hold single read
                    long numberOfCharsReaded = reader.GetChars(0, dataIndex, buffer, 0, bufferSize);

                    if (numberOfCharsReaded == 0)
                    {
                        ret = new string(totalBuffer,0, (int)numberOfChars);
                        break;
                    }

                    Array.Copy(buffer, 0, totalBuffer, dataIndex, numberOfCharsReaded);     // Add temporary buffer to main buffer
                    dataIndex += numberOfCharsReaded;

                } while (true);
            }
        }

        return ret;
    }