Re: Problem with the output of Split

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"cowznofsky" <jhcorey@xxxxxxxxx> wrote in message
news:9dd5823f-394c-402e-90cd-a3555a7a394e@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I have comma-delimited text, and I'm trying to add the values to a
recordset in the most economical way possible. I've added two fields
to the recordset and made them both advarchar(30) just to test this.

The first AddNew statement works, but the second one throws an error
"Arguments are the wrong type, are out of acceptable range, or are in
conflict with each other".

The Array and Split statements here seem equivalent except when I try
to use them in this context.
Any idea what the difference is?
I was hoping to use Split on a long string rather than have to refer
to every field by an index.


Dim varFieldNames As Variant
varFieldNames = Array("itemDT", "Latitude")
myRS.Open
myRS.AddNew varFieldNames, Array("01-06-2009", "42.889901")
myRS.AddNew varFieldNames, Split("01-06-2009,42.889901", ",")

Array() returns an array of Variants, while Split returns an array of
Strings. This code illustrate this:

Option Explicit

Private Sub Form_Load()
Dim v As Variant
Dim i As Long

v = Array("01-06-2009", "42.889901")
Debug.Print LBound(v), UBound(v), TypeName(v)
For i = LBound(v) To UBound(v)
Debug.Print "'" & v(i) & "'"
Next

v = CVar(Split("01-06-2009,42.889901", ","))
Debug.Print LBound(v), UBound(v), TypeName(v)
For i = LBound(v) To UBound(v)
Debug.Print "'" & v(i) & "'"
Next
End Sub

It prints:

0 1 Variant()
'01-06-2009'
'42.889901'
0 1 String()
'01-06-2009'
'42.889901'


The solution is to use a Split alternative. Below are alternative Split
functions. They are slower than VB6 Split function when the number of
columns is below ~120. See "The Charts" heading in this page(First row under
VB6 shows VB's Split speed):

http://www.xbeat.net/vbspeed/c_Split.htm

Another solution is using ParseCSV01 function below. It seems twice as fast
as Split when the number of columns is small(~5):

http://www.xbeat.net/vbspeed/c_ParseCSV.php

You need to change the second parameter to "asValues() As Variant". This
will slow things down, but you can compare the speed.

Another option is using "INSERT INTO" statements, which should be faster if
all fields are strings. Example:

sSQL = "INSERT INTO MyTable (itemDT, Latitude) VALUES ('01-06-2009',
'42.889901')"

If some fields are Date or numeric, then you have to specify them
differently, using #mm/dd/yy#(Always US Date for MS Access) or using
DateValue('') which uses Control Panel settings. For numeric fields you have
to remove the quotes. You can loop through the Fields collection and check
the Type property to determine how to specify the values.

Microsoft Access SQL Reference:
http://msdn.microsoft.com/en-us/library/bb245488.aspx

Transact-SQL Reference(MS SQL Server)
http://msdn.microsoft.com/en-us/library/aa299742(SQL.80).aspx


.

Relevant Pages

  • Re: Max/Min Functions
    ... You said the Array function won't work here. ... Dim myArray() As Variant ... > Dim vMax As Variant ...
    (microsoft.public.word.vba.general)
  • Re: Max/Min Functions
    ... You can stuff a value of any other data type into a Variant ... > Dim myArrayAs Variant ... > a whole array into a single Variant variable, ... > Dim vMax As Variant ...
    (microsoft.public.word.vba.general)
  • Re: Same Contents, Regardless of Order
    ... Dim Arr1As Variant ... ' Second Array ... Dim blnFound As Boolean ...
    (microsoft.public.excel.programming)
  • Re: Passing Recordset to a Form
    ... Siteand Count which is the array length. ... to my recordset, but not into my database table. ... Dim rstview As adodb.Recordset ...
    (microsoft.public.vb.database)
  • Re: transferring array to range
    ... Dim arrValues(NumRows, NumColumns) As Variant ... declared an array of variants instead of a variant that contains an array. ...
    (microsoft.public.excel.programming)