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

 

[Jon von der Heyden]

How do you intend on trimming all of the values in one field? If you are intending on unloading the recordset to Excel anyway then I think it would be better to do it once it is in a range. I would use the evaluate method, e.g:

With Range("A1:A100")
    .Value = Evaluate("TRIM(" & .Address & ")")
End With

 

[ClimoC]

I did consider doing that, or at least trimming it as part of the other functions I perform on it.

But considering I have a dictionary to fill and test, sheetnames to make, and pathseparators to remove, I didn't really want to litter all the code with replaces,instrs,lens,lefts, and trims - I'd have to use a conjunction of these in about 7 different places, all in loops.

It has been my standard practice to create recordsets as object, then modify their settings and fill them bit by bit. But A)The code takes a while to write, B)It's messy, C) As I use this sort of thing regularly I'd prefer to just quickly get the recordset this way.

 

[ClimoC]

Have I just got the order wrong?

 

[Kyle123]

Why not trim it in the sql?

have you tried using the recordset object to query the db, the object allows you to specify the cursor type in the sql call?

 

[ClimoC]

@Kyle - You can do that?

How?

 

[Kyle123]

SELECT rtrim(ltrim(field1)) as trimmed

Note this is sql server rather than access, but Im sure there will be an equivelant

 

[ClimoC]

Select trim(*) from...?

 

[Jon von der Heyden]

I kinda do and don't get what you're saying. I would still like to know how you will trim the field going down the lines you propose. For me the Trim I suggested is a one hit solution. If you need to trim it before you unload it to a range (or if you never intend on unloading it) then I would have trimmed it when I queried the table, e.g:

Select Field1, Field2, Field3, [B][COLOR="Red"]Trim([Field4]) as Field4x[/COLOR][/B]
From Table;

 

[ClimoC]

I kinda do and don't get what you're saying. I would still like to know how you will trim the field going down the lines you propose. For me the Trim I suggested is a one hit solution. If you need to trim it before you unload it to a range (or if you never intend on unloading it) then I would have trimmed it when I queried the table, e.g:

Select Field1, Field2, Field3, [B][COLOR="Red"]Trim([Field4]) as Field4x[/COLOR][/B]
From Table;

Like this (hence the OP about needing to connect with an optimistic locktype)

If RS Is Nothing Then: MsgBox ("You have either picked the wrong file, or the file is empty!"): Exit Function
With RS
        If Not .BOF And Not .EOF Then
                Do Until .EOF = True
                    .Fields(12).Value = Trim$(.Fields(12).Value)
                    .Fields(12).Value = VBA.Replace(.Fields(12).Value, "/", "", 1, Len(.Fields(12).Value), vbTextCompare)
                    If Not mycoll.Exists(.Fields(12).Value) Then mycoll.Add .Fields(12).Value, I
                Loop
        End If
End With