Recordset = ConnectionString, preserving Locktype problem

[ClimoC]

Hello

I've followed the MSN brief on my problem as best I can, but still have issues!

I understand why it does it and why it happens, but can't seem to set my RecordSet and preserve optimistic locktype and dynamic cursor.

If I understood it (probably not) correctly, I need to Open the connection, but not execute it, then set the locktype, then execute the sql string to get it all together, but it still reverts to the default locked argument. I need to open locktype optimistic (or just not readonly) because I have to trim all the values in one field before proceeding with the guts of the code.

Global RS As Recordset, CS As Worksheet, cstrw As Long

Public Function Multi()

Dim chans As New Scripting.dictionary, iCh As Variant, ChK As Variant
Dim xWb As String, xWbSource As String
Set RS = CreateObject("ADODB.Recordset")
    xWb = ThisWorkbook.Sheets("Multipass").Range("B8").Value

    If Len(Dir$(xWb)) = 0 Then
        Call Err.Raise(vbObjectError + 1024, , "File does not exist!")
    Else
        xWbSource = Left$(xWb, InStrRev(xWb, Application.PathSeparator))
        xWb = Dir$(xWb)
    End If

ImportToRecSet xWb, xWbSource

'...

Public Function ImportToRecSet(ByVal BK As String, Src As String)

Dim strFilespec As String
Dim strConn As String
Dim sqlStr As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'If Not RS Is Nothing Then Set RS = Nothing
Set cnimportconn = New ADODB.Connection
strConn = "Provider=Microsoft.ace.oledb.12.0;Data Source=" & Src & "\;Extended Properties=""text;HDR=Yes;FMT=Delimited(,)"";Persist Security Info=False"
strsql = "SELECT * From [" & BK & "]"

With cnimportconn
        .CursorLocation = adUseServer
        .ConnectionString = strConn
        .Open
        .CommandTimeout = 0
End With


RS.LockType = adLockOptimistic
RS.CursorLocation = adUseClient
RS.CursorType = adOpenDynamic
Set RS = cnimportconn.Execute(strsql)
RS.MoveFirst

End Function

had been following this brief here: http://support.microsoft.com/kb/188857
Cheers

 

[ClimoC]

No worries, thank you very much for your help thus far Jon.

The collection (singular!) is to take advantage of the 'Exists' method, quickest and easiest way I know of getting a unique list built in a memory collection.

Like you, I always build RS's from the ground up.

I think tomorrow I'll just rewrite the whole lot

 

[Kyle123]

Or a Distinct SQL query

If can you send a sample of the csv that you're using, obviously with any confidential info removed, I'm happy to have a quick look