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]

My darn internet is so slow today I just can't get my posts in...

 

[Jon von der Heyden]

Also, have you experimented by using the recordset Open method instead of Execute?

 

[ClimoC]

I think that's what I was trying to do... I tried following the parts in the MSN doc that explained how to do it (see 'Command_3_click' below)

Option Explicit
   Dim cn As New ADODB.Connection
   Dim cmd As New ADODB.Command
   Dim rs As New ADODB.Recordset

   Private Sub Command1_Click()
   With cn
      .CursorLocation = adUseServer
     .ConnectionString = "dsn=yourdatasourcename"uid=yourUserId;pwd=yourPassword
      .Open
   End With  
   rs.CursorType = adOpenKeyset
   rs.LockType = adLockOptimistic
   End Sub

   Private Sub Command2_Click()
      Set rs = cn.Execute("select * from authors")
   End Sub

   Private Sub Command3_Click()
     With cmd
       .ActiveConnection = cn
       .CommandType = adCmdText
       .CommandText = "select * from authors"
     End With
     Set rs = cmd.Execute
   End Sub

   Private Sub Command4_Click()
    rs.Open "select * from authors", cn
   End Sub

   Private Sub Command5_Click()
     Debug.Print "CursorLocation " & rs.CursorLocation; ""
     Debug.Print "cursortype " & rs.CursorType
     Debug.Print "locktype " & rs.LockType
   End Sub

On two occasions, in a row, it uses execute. I tried following this and go no further.

In any case, Trimming the field on entry seems to be working.

For anyone else looking for a solution like this, you need to:

strsql = "SELECT [Field1NameasinCSVColHed], TRIM([Field1NameasinCSVColHed]) as Field1NameasinCSVColHed From [" & BK & "]"

and pay attention to where there are square brackets and not square brackets

 

[Kyle123]

I don't see it in your examples, but have you tried

   Private Sub Command4_Click()
    rs.Open "select * from authors", cn, adOpenDynamic, adLockOptimistic
   End Sub

 

[Jon von der Heyden]

I was referring to the recordset open method, not the connection open method.

Sub Demo(ByVal strBK As String, ByVal strDB As String)
    Dim rec As ADODB.Recordset: Set rec = New ADODB.Recordset
    Const strConnection As String = "Provider=Microsoft.ace.oledb.12.0;" & _
                                     "Data Source=" & strDB & "\;" & _
                                     "Extended Properties=""text;" & _
                                     "HDR=Yes;" & _
                                     "FMT=Delimited(,)"";" & _
                                     "Persist Security Info=False"
    Const strSQL As String = "Select * From [< TABLE >]"
    
    Call rec.Open(Replace$(strSQL, "< TABLE >", strBK), strConnection, adOpenDynamic, adLockOptimistic)
    
    'more code...
End Sub

 

[ClimoC]

Ha! Jeez that was stupidly easy. The stupidly part being played by me.

Though it now presents me with a final error to my required end result

Connection is made, Record count is 1022, BOF is false, EOF is false, I apply no filters, I have moved first, but when I move to do this:

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 = VBA.Replace(.Fields(12).Value, "/", "", 1, Len(.Fields(12).Value), vbTextCompare)
                    If Not chans.Exists(.Fields(12).Value) Then chans.Add .Fields(12).Value, I
                    .MoveNext
                Loop
        End If
End With

The result is 'Multi-Step operation generated error. Check each status value'

The only thing I can think, is that the datatype isn't correct for the column. (I recognise this from when you try and pass say a string into an adInteger type field.

Do I have to have 12 lines stipulating what data type each column is? That would make sense, but it seems to connect without it... and I can read from it, so it clearly knows it's a string. Has it also defaulted to some sort of OLE Static or variant type?

 

[Jon von der Heyden]

All the more reason to make the changes prior to constructing the recordset, or after it has been unloaded.

So perhaps that replacement is what results in say a number, when it expect a string. So try using string type declaration character with Replace, i.e.:

.Fields(12).Value = Replace[highlight]$[/highlight](.Fields(12).Value, "/", "", 1, Len(.Fields(12).Value), vbTextCompare)

or...

.Fields(12).Value = [highlight]Cstr([/highlight]Replace(.Fields(12).Value, "/", "", 1, Len(.Fields(12).Value), vbTextCompare)[highlight])[/highlight]

 

[ClimoC]

All the more reason to make the changes prior to constructing the recordset, or after it has been unloaded.

So perhaps that replacement is what results in say a number, when it expect a string. So try using string type declaration character with Replace, i.e.:

.Fields(12).Value = Replace[highlight]$[/highlight](.Fields(12).Value, "/", "", 1, Len(.Fields(12).Value), vbTextCompare)

or...

.Fields(12).Value = [highlight]Cstr([/highlight]Replace(.Fields(12).Value, "/", "", 1, Len(.Fields(12).Value), vbTextCompare)[highlight])[/highlight]

Still the multi-step generation issue

have tried both

                    .Fields(12).Value = CStr(VBA.Replace$(CStr(.Fields(12).Value), "/", "", 1, Len(CStr(.Fields(12).Value)), vbTextCompare))

and

.Fields(12).Value = VBA.Replace$(.Fields(12).Value, "/", "", 1, Len(.Fields(12).Value), vbTextCompare)

using the immediate window, I've done a ?Rs.Fields(12).type, which returns 203. That's 'adVarChar' isn't it?

 

[ClimoC]

All the more reason to make the changes prior to constructing the recordset, or after it has been unloaded.

And Whilst I do appreciate the learning to be had, as I said there're sheet names to be had about nyah... If the string in field(12) is clean, I don't have to do any other work later on. If it's not, it's a lot more hassle. And this has to run on lots and lots of rows

 

[Kyle123]

Im curious as to why you've opted to use ADO for this, I always found it really slow for large csvs (which i think you're querying).

I've always found that opening them, reading them to the end, splitting into arrays (by cr then commas) and then working on them is much faster.