ADO query keeps cutting off the full string

[bradyboyy88]

Hi,

I am using the following ADO connection and I am not sure why but depending on the query the recordset value for a string may be cut off. Maybe there is some sort of restriction for ADO data transfers or for Recordsets?

Public Sub SQLOpenDatabaseConnection(StrDBPath As String, EngineType As Integer)
   
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    'Define Connection String by inputting StrDBPath into a larger string
    'Access Support for engine type
    If EngineType = 0 Then
    
        sConn = "Provider=Microsoft.ACE.OLEDB.15.0;" & _
                                 "Data Source=" & StrDBPath & ";" & _
                                 "Jet OLEDB:Engine Type=5;" & _
                                 "Persist Security Info=False;Mode=Share Exclusive;"
    
    'Excel Support for engine type
    ElseIf EngineType = 1 Then
        
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=NO;ReadOnly=0;"";"
    
    End If
    
RetryConnection:
    DoEvents
    On Error GoTo ErrorHandler
        'Connect to the database
20       oConn.Open sConn
    On Error GoTo 0


Exit Sub


ErrorHandler:
'triggered by connection error. Most likely locking type
'MsgBox "looks like we had a connection error. The error number is " & Err.Number & " and the description is " & Err.Description & " on line " & Erl


Err.Clear
Resume RetryConnection


End Sub

 

[Norie]

Can you post the code for the query you are having problems with?

 

[bradyboyy88]

Here is the query which results in text cut off.

DatabaseMethods.SQLOpenDatabaseConnection DatabaseDirectory, 0
    SQLQueryCode = "Select Distinct STATE_NAME, STATE_PRODUCTS_MC_NOTES,PROGRAM_NAME,PROGRAM_DD FROM ((PROGRAM_NAME_DD INNER JOIN STATE_ASSIGNMENTS_DD ON PROGRAM_NAME_DD.STATE_DD = STATE_ASSIGNMENTS_DD.ID) INNER JOIN BASEREVIEW ON PROGRAM_NAME_DD.STATE_DD = BASEREVIEW.STATE_DD) WHERE STATE_ASSIGNMENTS_DD.STATE_NAME = '" & ShaunStateSelection & "'"
    Set StateListRecordSet = DatabaseMethods.SQLQueryDatabaseRecordset(SQLQueryCode)
    DatabaseMethods.SQLCloseDatabaseConnection
  
MsgBox StateListRecordSet.Fields("STATE_PRODUCTS_MC_NOTES").Value

This will return the string cutoff despite in the database it has the full value in there. Its an access database 2013

 

[bradyboyy88]

I tried to shorten the sql string by removing the table names which is what shows up there. I can repaste with the table names for the fields in select if you would like.

 

[Norie]

Which field(s) are cutoff and what data type(s) are they?

 

[bradyboyy88]

Which field(s) are cutoff and what data type(s) are they?

The field STATE_PRODUCTS_MC_NOTES and the data type is long text in the access database.

 

[Norie]

In earlier versions of Access Long Text fields were known as Memo fields and it's a known issue that they get truncated with certain types of queries.

If you remove Distinct from the query what happens with the STATE_PRODUCTS_MC_NOTES field?

 

[bradyboyy88]

So I removed distinct and it still gets cut off. I am using access 2013 for the database and excel 2013 for the userform and ado code. Its strange this one query would cut it off when the tables are not really that big . Should I try a different data type in the database?

 

[Norie]

That's strange, I asked you to try removing Distinct based on what I found here.

Without Distinct no 'processing' of the data should be happening, as far as I can see anyway, and the entire field should, apparently, be returned.

You could try changing the field type to Short Text but if you had any data with more than 255 characters in the field it would be lost.

What's actually in the field?

Is it just bunch of 'random' notes or are they organised in any way, eg by date, person?

 

[bradyboyy88]

I must not have rerun my code right. I removed distinct and your right.... Wow great article and find. I really appreciate that. So is there anyway around this since distinct is sometimes important to use for queries?