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]

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.

Familiarity, Filtering, Finding, Dumping

Plus I've learned all this in bits and bobs over the years - a more holistic approach and I might've done things differently. And I prefer to keep everything in memory where possible, especially if I have to write values

 

[Jon von der Heyden]

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.

That's interesting... My experience is that ADO is the quicker of the two methods you mention... Especially for large tables...

Though as ClimoC mentions, he is using ADO Filter method (I recall from a prior thread too). ADO recordset Filter is awesome and may make ADO the better method to choose.

 

[Kyle123]

Using arrays would be keeping everything in memory

Yeah, you can do all those things in ADO, they're just slow and splitting and filtering are really fast.

It's worth saying though that I've no idea what your project is and so I might be miles out with that suggestion

 

[Kyle123]

hmm, in a previous life on a previous board I had had a project with 3 tab delimited files with 40k lines in each and had to loop through a list to extract entries that could be in any of the three. Have a look here if you're interested http://www.excelforum.com/excel-programming/812291-array-slicing-optimisation.html after many trials and tribulations, ADO was by far the slowest solution I tried. Splitting and filtering was substantially faster.

That said it probably depends on the scenario, ADO would be preferable in some instances and arrays in others

 

[ClimoC]

Though still always goods to promote discussion

So does anyone know about modding or forcing the datatype? I can't do it before the RecordSet is created via the Open command (as it has no columns or anything), and I can't do it afterwards as it is already open.

Though considering querying the value returns "This is a string value/ blah blah" (incl. separator, which I need to remove).

I looked it up again, 203 datatype is 'adLongVarWChar ("memo"), type 203'

I think there's more to be done in the connection part of this?

 

[ClimoC]

Bah. The more I read, the more confused I'm getting

Shouldn't 203 and 200 (data types) basically be the same thing? Both variable character string types aren't they??

 

[Kyle123]

200 advarchar in SQL server is usually a fixed length field e.g varchar(255), however it can be varchar(MAX) which is the replacement for NTEXT (203) as it behaves like a string you can do string functions on it (like len, left etc), you can't on a 203 (at least in sql server). VarChar(MAX) tries to store it's data in the table so it's faster than nText which doesn't

I'm guessing that doesn't help you much though

What's your datasource?

 

[ClimoC]

200 advarchar in SQL server is usually a fixed length field e.g varchar(255), however it can be varchar(MAX) which is the replacement for NTEXT (203) as it behaves like a string you can do string functions on it (like len, left etc), you can't on a 203 (at least in sql server). VarChar(MAX) tries to store it's data in the table so it's faster than nText which doesn't

I'm guessing that doesn't help you much though

What's your datasource?

csv file

When I print the datatypes it is either interpreting or picking up, I get what looks 'pretty much right'... 203's, 3's, and 7's (which I will guess are date/times, strings, and 3 I think is an integer?)

So it's getting it from somewhere.

TBH I've decided to brute force open and clean the workbook.

But now in going down that route, I've managed to arise another issue, which is that by opening, editing, saving, and closing the book, then trying to connect, I get told 'This array is fixed or temporarily locked'

I'm honestly at the end of my rope with this, because grabbing a csv straight to a recordset and dumping it is **** easy. Why then is it a nightmare of all hellishness to open it read/writeable!!!!

I'm THIS close to throwing in the whole procedure, and just plain old appending columns and looping through range objects to add to the RS. Truth be told I can't even test the other 600 lines of code yet because the **** connection to the recordset falls over.

I miss Selection.Copy

 

[ClimoC]

also, I had a stab at using the ALTER TABLE sql and that fell over in my face. Plus I could'n't figure out where to use it - in the RS that hadn't been created yet (and therefor had no fields, let alone fields to change type to), or as part of the original SQL, or as a separate SQL and then what to do with it...

 

[Jon von der Heyden]

This is going beyond my knowledge I'm afraid. The only time I have ever concerned myself with the field data type is when I've built a recordset up field by field.

I'm not sure why you add each field to a dictionary object, nor do I know what your your final intentions are with the recordset and their fields, however, I really am getting the feeling that you ought to be doing all of this donkey work in the SQL, or after you have unloaded it.

With respect to the actual errors; I wish I had the time to set-up a sample and do some experimenting, because it would be very interesting to understand this behaviour...however I simply don't have that much time spare at the moment.