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

 

[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?

I should have been more specific. I guess in regards to the query or using some other type of ado engine. The workarounds from the article seem to change the data structure or having to requery the current recordset which I guess I may to do some research on. Actually I may not even understand their workarounds to be honest haha.

 

[Norie]

I think the idea of the workarounds is to use a query to do the distinct part without the memo field and then take the results from that query and use them to return all the fields, including the memo field, in another query.

Not sure how exactly you would do that in ADO but perhaps something involving a sub query that returns the primary keys of the distinct records.

 

[bradyboyy88]

Your completely right. I re did my query logic (using a sub query that has distinct but no memo field) then inner joined and wallah it works. Thanks for all your help Norie!