Text fields truncated from Access 2007

When I retrieve a text field (255 char) from an Access Database, the data is always truncated at about 130 characters.
When I retrieve a text field (255 char) from an Access Database, the data is truncated at about 130 characters.

I am using the Windows 7 OS, Access 2007 and Xojo 2013r2. I connect to the Access database using the ODBC connection string. I can open the database successfully using the connection string with or without DSN. The problem remains the same either way. I tried my application using Real Studio 2012r2 and got the same result.

I am able to use Xojo (as well as Real Studio) to insert records into the text fields in the Access table, and they are NOT truncated. However, when I read them using a RecordSet they are always truncated at around 130 characters.

Is there a limit in Xojo concerning the length of text strings returned from an ODBC connection? I tried increasing the buffer size in the connection string and got the same result.
Is there a limit in Access concerning sending data to an external application that I am unaware of?
Is there some buffer size in the Windows 7 OS that I am unaware of?

Any help would be greatly appreciated.

Ruth Watkins

Trying to help rule things out - I can retrieve pages of information for a single field with the ODBC plugin with non Access databases. I don’t have access to Access to test. (tongue twister)

I have never attempted to get data from a non-Access database using the ODBC plugin. I know that the Oracle plugin works great with long Varchar(2) fields, while Access does the insert correctly but not the read. I just wish I could find out whether it’s Access, the ODBC plugin, or the Win 7 OS that’s causing the problem. Thank you for responding.

Hello Ruth,

Thanks for bringing this to the forums. I followed your steps and can confirm that this is what is happening. I created a field in Microsoft Access (mdb) and added the following text to the field:

Hello, this bit of text has exactly 254 characters. How do we know this? I found a program online which counts all the characters in a paragraph made up of sentences and counts each one. As long as the program is correct, then this is 254 characters. ok?

Then, I used the following code in a Xojo program:

[code] //Getting Information from the Database
Dim db as ODBCDatabase
Dim rs as RecordSet

db = new ODBCDatabase

db.DataSource = “DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:\Users\Eugene\Desktop\ew\Truncated\Truncated.mdb;User Id=admin;Password=;”

if db.Connect() then //Was the connection successful?
'MsgBox “Connected”
else
MsgBox "Connection failed: " + db.ErrorMessage
end if

rs = db.SQLSelect(“SELECT * FROM Widgets”)

if rs <> Nil then
LblText.Text = rs.Field(“254Chars”)
else
MsgBox “No RecordSet exists.”
end if
rs.Close
db.Close[/code]

The result was the following in a label:

Hello, this bit of text has exactly 254 characters. How do we know this? I found a program online which counts all the characte

It looks like Xojo is cutting off after exactly 128 characters - including a carriage return. If a variable in the plugin from Xojo is limited to 128 characters then this would happen.

This looks like a bug. Please report it with the feedback program and include an example.

Happy to help!

Eugene

Eugene,

I will have to figure out how to use the feedback program, and then I will report it – unless, of course, I can save time by convincing you to use the feedback program to send in the example you have already created.

I would like to concentrate on another problem I found using the ODBC plugin with Access. It involves creating queries with a varying number of “and columname = ‘value’” statements depending upon what the user selects on the screen. I did not bring this one to the attention of the forum because the query results are always short enough that I can use a full table scan and store the entire recordset in a doubly dimensioned array, and then do my own filtering depending upon what the user entered on the screen. That’s a clumsy way to do it, but it works whereas the SQLSelect does not.

Thanks a lot for your help.

Ruth

[quote=28811:@Eugene Dakin] LblText.Text = rs.Field(“254Chars”)
[/quote]
Dont do this this way :stuck_out_tongue:
This could contribute to the problem as it has to convert a variant to a string.
You should alwats define the encoding & explicitly ask for the specific value (string, double, etc)

LblText.Text = DefineEncoding(rs.Field(“254Chars”).StringValue, Encodings.ASCII) // or some suitable encoding

Since all of my database columns are text, I tried adding the “Encodings.ASCII” to the code snippet shown below:

Rst = App.DB.SQLSelect(sQy) // read ODBC database
iRows = -1
If Rst <> Nil Then
Rst.MoveFirst
While NOT Rst.EOF

  iRows = iRows + 1
  For i = 0 To iCols
      RS(iRows,i) = DefineEncoding(Rst.Idxfield(i + 1).StringValue, Encodings.ASCII)
  Next i
  
  Rst.MoveNext
Wend
Rst.Close

End If

No Change. It still truncates two long text fields and everything else is fine.
I know nothing about encodings. Maybe I’m not doing it correctly. Any ideas?

Thank you.

Ruth

Hi Ruth,

Although a TEXT field should be able to send out the full 255 characters, it doesn’t seem to work. I tried the same modification of the example program and also had the same answer you did - no change. I tried all of the common Windows/Access encodings (WindowsANSI, ASCII, UTF8 through UTF32) and had some interesting ( :slight_smile: ) results, and not the one we were wanting.

Workaround: The good news is that if the Data Type in Microsoft Access were changed to a Memo Data Type, then the full 255 characters is shown through a Xojo program (Backup your database before trying this).

Norman, do you have any other possible helpful suggestions we could try for a Text Field?

Thanks for everyone’s help,

Eugene

After more digging, there appears to be an issue with a Microsoft Command (which might be used in the plugin).

If the SelText command is used in the plugin, then the text is truncated to 128 characters.

GloMtgText = "(" & Me.DateCompleted & ") " & Me.Description.SelText

A workaround in the plugin is to use Mid instead:

Mid(Me.Description.Text, Me.Description.SelStart + 1,  Me.Description.SelLength)

Norman, should I create a feedback report to investigate this possible solution?

Thanks,

Eugene

I did not use the Memo field in Access because I have to be able to “Select” using “like.” The last time I studied Access this was not permitted on a Memo field. I have considered putting a separate table in Access with a one-to-one relationship on the primary key. It would contain only 2 fields which would be the 2nd half of the two long fields in the actual table. This would be one heck of a lot of added code and complexity that hopefully won’t be necessary if the SQLSelect can be made to work right.

I appreciate everyone staying on this.

Ruth

[quote=29054:@Eugene Dakin]After more digging, there appears to be an issue with a Microsoft Command (which might be used in the plugin).

If the SelText command is used in the plugin, then the text is truncated to 128 characters.

GloMtgText = "(" & Me.DateCompleted & ") " & Me.Description.SelText

A workaround in the plugin is to use Mid instead:

Mid(Me.Description.Text, Me.Description.SelStart + 1,  Me.Description.SelLength)

Norman, should I create a feedback report to investigate this possible solution?

Thanks,

Eugene[/quote]

Of course :stuck_out_tongue:

Feedback case 29070 added.

in order for you to SELECT with a LIKE on memo field, create a query with the memo field but do a LEFT(Memofield, 9999)

Hi Richard,

I tried your suggestion and couldn’t build the correct syntax. Here is what I tried:

rs = db.SQLSelect("SELECT * FROM Widgets WHERE 254Chars LIKE LEFT(254Chars,9999)='%s%'")

Is this correct?

Sincerely,

Eugene

Richard (and Eugene),

I changed both long text fields to memo fields and IT WORKED!!

What I was not expecting was that both of the following worked (with and without the LEFT):

sQy = “SELECT Title, FirstLine FROM Anthems WHERE Left(FirstLine,255) like ‘%He is a great King%’”
Rst = App.DB.SQLSelect(sQy)
sQy = “SELECT Title, FirstLine From Anthems WHERE FirstLine like ‘%He is a great King%’”
Rst = App.DB.SQLSelect(sQy)

I believe that my immediate problem is solved now. Sincere thanks to everyone who helped me!!!

Ruth

[quote=29249:@Eugene Dakin]Hi Richard,

I tried your suggestion and couldn’t build the correct syntax. Here is what I tried:

rs = db.SQLSelect("SELECT * FROM Widgets WHERE 254Chars LIKE LEFT(254Chars,9999)='%s%'")

Is this correct?

Sincerely,

Eugene[/quote]

Eugene,

what i usually did even on Ms Access is to do this

i create a query called qryWidgetBase and inside is “SELECT LEFT(254Chars, 9999) as v9999, * FROM Widgets”

and then create a second query “SELECT * FROM Widgets WHERE v9999 LIKE ‘%s%’”

Hi Richard,

Excellent! Thanks for your help.

Eugene