Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Array help (2000)

    I am trying to change some code from:

    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''
    <font color=blue> 'Get Field Names
    ' Fld1 = Range("Field1")
    ' Fld2 = Range("Field2")
    ' Fld3 = Range("Field3")

    ' Sql = "INSERT INTO " & sDbTable & "(" & Fld1 & "," & Fld2 & "," & Fld3 & ")" & _
    " SELECT " & Fld1 & "," & Fld2 & "," & Fld3 & " FROM" & _
    " [Excel 8.0;HDR=YES;Database=" & sThisWbk & ";].MyAddRange;"</font color=blue>
    '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''
    which works but is cumbersome with a fixed number of fields,

    TO THIS:

    'Get Field names into the vFields array
    Set LastField = Cells(Range("Field1").Row, 255).End(xlToLeft)
    Set MyFields = Range(Range("Field1"), LastField)
    ReDim vFields(MyFields.Cells.Count)

    For i = 1 To MyFields.Cells.Count
    vFields(i) = MyFields(i)
    Next

    Sql = "INSERT INTO " & sDbTable & "(" & vFields() & ")" & _
    " SELECT " & vFields() & " FROM" & _
    " [Excel 8.0;HDR=YES;Database=" & sThisWbk & ";].MyAddRange;"

    But I am getting a Type Mismatch Error
    Suggestions?
    thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Array help (2000)

    An SQL string is, well, a string. You can't insert an array into a string.

    If you're trying to use ALL fields, try * instead of enumerating the individual fields:

    Sql = "INSERT INTO " & sDbTable & _
    " SELECT * FROM" & _
    " [Excel 8.0;HDR=YES;Database=" & sThisWbk & ";].MyAddRange;"

    If you need or prefer to enumerate the fields, you must assemble the field list as a comma-delimited string:

    Dim strFields As String

    'Get Field names into the vFields array
    Set LastField = Cells(Range("Field1").Row, 255).End(xlToLeft)
    Set MyFields = Range(Range("Field1"), LastField)

    For i = 1 To MyFields.Cells.Count
    strFields = strFields & ", " & MyFields(i)
    Next

    ' Get rid of first ", "
    strFields = Mid(strFields, 3)

    Sql = "INSERT INTO " & sDbTable & "(" & strFields & ")" & _
    " SELECT " & strFields & " FROM" & _
    " [Excel 8.0;HDR=YES;Database=" & sThisWbk & ";].MyAddRange;"

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Array help (2000)

    thanks Hans.
    Just what I was looking for

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •