Memo truncated from Access database to VBA string

Question

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

`Set rs = CurrentDb.Execute("SELECT EntryID FROM Folder")`, and then `target = rs("EntryID")` — dan, Oct 12 '12 at 14:50
I'm not a SQL guru, but it seems like I could use some of that: http://bytes.com/topic/access/answers/922291-memo-field-get-truncated-while-exporting-into-excel-docmd-transferspreadsheet. 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, Oct 12 '12 at 14:53
`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, Oct 12 '12 at 14:54
Are you sure you're able to run `Set rs = CurrentDb.Execute("SELECT EntryID FROM Folder")` because `Execute` doesn't return anything. This should be a compile error. I used `OpenRecordset` and was able to get the full memo back in both the recordset field and a string variable. I was using a DAO recordset. ( I just saw this is super old. I'm leaving this here regardless) — Brad, Aug 24 '15 at 18:02

score 1 · Accepted Answer · answered Oct 12 '12 at 15:10

1

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

answered Oct 12 '12 at 15:10

Saic Siquot

6,513
5
34
56

That's what I was actually trying. For some reason, the *parts* are null. Time for diner, I will get back in an hour to see what's wrong again, but ya, it should work I guess. – dan Oct 12 '12 at 15:13
It works perfectly now, thank you. I don't know what exactly was the problem, but now it works. – dan Oct 12 '12 at 16:29

score 0 · Answer 2 · answered Aug 24 '15 at 16:50

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;
    }

Welcome to Stack Overflow! Please consider editing your post to add more explanation about what your code does and why it will solve the problem. An answer that mostly just contains code (even if it's working) usually wont help the OP to understand their problem. — SuperBiasedMan, Aug 24 '15 at 17:06
The query to retrieve data from database return only one record and only memo field. The "ExecuteReader" method with "SequentialAccess" parameter enable to read much more of 256 chars. I have used the function "GetChars" to retrieve 1024 chars every time. Also, this pattern can be used to read fields of BLOB type using the function "GetBytes" in place of "GetChars". — Giuliano Piergentili, Sep 01 '15 at 08:09

Memo truncated from Access database to VBA string

2 Answers2