empty recordset with memo field

[Big_Ape]

hi all,

I am having a frustrating issue and was hoping someone might have had some experience with it.

I have some code in Excel which runs some SQL on an Access DB and returns a recordset with the result.

Now, one of the fields is a memo field. I know that memo fields can be problematic, and I hear of people saying that it only retuns 250 characters, etc when they run it, which I am prepared to deal with (by splitting it up, eg 'left(Notes, 250) as Notes1, mid(Notes, 251, 250)as Notes2, mid(Notes, 501, 250) as Notes3,'...etc). However the problem I have is that when I select the memo field to be selected the recordset is literally empty (not just truncated). Even when I used things in the SQL statement like 'SELECT left(memofield, 10) FROM table". The code I am using to get the data is below:

Sub GetRecordset(DBSource As String, Qry As String, DestRs As Recordset)

Dim cn As ADODB.Connection
Dim i As Long
Dim j As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DBSource & ";Persist Security Info=False;Jet OLEDB:Database Password=" & PWord & ";"
    
    ' open a recordset
    Set DestRs = New ADODB.Recordset
    
    DestRs.Open Qry, cn, adOpenForwardOnly, adLockReadOnly, adCmdText

End Sub

The SQLSTr can be as simple as "SELECT Memofield from table" and it does this.

I have messed around with different variables with the .open command, etc, but just cant seem to get it to work. It is only when I include the said memo field in the SQL's 'SELECT' statement. It doesnt even return anything in the other non-memo fields when it is included. It is just empty. if I dont include it in the SQL's SELECT statement, it is fine.

Anyway, and help would be appreciated.

 

[Kyle123]

Your connection string looks odd, why do you have references to ACE and Jet?

 

[Big_Ape]

This is the conneciton string i used when getting data.

I have tried it with:

I have tried it with cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBSource & ";Persist Security Info=False;Jet OLEDB:Database Password=" & PWord & ";"

but I still get the same issue.

How would you suggest I do it?

 

[RoryA]

How are you verifying the recordset is empty?

 

[Kyle123]

Hmm scratch that the connection string is correct, just odd.

Could you post the SQL you're using?

 

[Big_Ape]

Hi,

believe it or not, you have helped me solve the issue.

by you asking me to send the SQL it forced me to trim it down, and in the process discovered it was the 'Like' criteria that was doing it. Instead of using * as a wildcard you have to used % if it is via ADO. as a result, running the SQL directly in Access worked, but not via the code.

Thanks,