VBA ADO Connection issue

[Naillmclean]

Hi All,

I am connecting to a SQL Server compact table and I am getting the following error: Run-time error '-2147217887 (80040e21)

The odd thing is if i remove the rs.CursorLocation = adUseClient from my code it works fine, but i am wanting the connection closed because the bandwidth is extreamally small due to geographical issues.

The other thing i have noticed is that if i leave rs.CursorLocation = adUseClient in but change the server table coulmn to length 100 from length 200 it works ok.

Does anyone know if there is a field limit of 100 characters when using adUseClient?

Code Below:

Dim cn As Connection
Dim rs As Recordset
Dim MySQL As String
MySQL = "select Item_Description, Item_Number, Measure from items where Item_Description like '%" & Search_TB & "%' Order By Item_Number"
Set cn = New ADODB.Connection
cn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\SQL Express\MyDatabase#1.sdf;;SSCE:Database Password='password'"
cn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
rs.Source = MySQL
rs.CursorLocation = adUseClient
rs.Open

Set rs.ActiveConnection = Nothing
cn.Close
If rs.RecordCount = "0" Then
MsgBox ("No Records Found")
Else
i = 0
With Inventory_Tool.Results_LB
.Clear
Do
.AddItem
.List(i, 0) = rs.Fields("Item_Number").Value
.List(i, 1) = rs.Fields("Measure").Value
.List(i, 2) = rs.Fields("Item_Description").Value
i = i + 1
rs.MoveNext
Loop Until rs.EOF
End With
End If

If Not rs Is Nothing Then Set rs = Nothing
If Not cn Is Nothing Then Set cn = Nothing

Thanks
Naill