Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2014
    Posts
    8

    Unanswered: Run time error'-2147217887(80040e21) ODBC DRIVER DOESNT SUPPORT REQUESTED PROPERTIES

    please let me know

    Sub downloadData()


    Dim dbconn As New ADODB.Connection
    Dim i ', prdTblName
    'prdTblName = "tblProd_" & projCode & "_" & batchCode
    Dim qryStr, dataStr As String

    qryStr = "SELECT * FROM tblbatch_headers where idBatch= " & batchID & "order by col_seq asc"

    Dim rs As New ADODB.Recordset
    dbconn.Open strConn
    rs.Open qryStr, dbconn, 3, 1 -> GETTING ERROR HERE
    i = 0
    Do While rs.EOF = False

    i = i + 1
    If i = 1 Then
    dataStr = rs("tblColName")
    Else
    dataStr = dataStr & ", " & rs("tblColName")
    End If
    rs.MoveNext
    Loop
    ' FP = For Prodction, IQR = In Process Quality Rejected, PC = Production Complete
    ' IQA = In Quality Check Approved, FQA = Final Quality Review Approved, FQR = Final Qaulity Review Rejected

    qryStr = "SELECT " & dataStr & " FROM " & prdTblName & " where FQR_User_Code='" & Application.UserName & _
    "' and line_status in('QP') order by line_status"
    Sheet1.Activate
    Sheet1.Columns.Clear
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=mySQL32;" _
    , Destination:=Range("$A$2")).QueryTable
    .CommandText = qryStr
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    ' .SourceConnectionFile = _
    "C:\Users\SwamyXPS15\Documents\My Data Sources\wds.odc"
    '.ListObject.DisplayName = "Table_wds"
    .Refresh BackgroundQuery:=False
    End With
    Range("C2").Select

    rs.MoveFirst
    i = 0
    Do While rs.EOF = False
    Dim rng As Range
    i = i + 1
    ActiveSheet.Cells(1, i) = rs("Comments")
    ActiveSheet.Cells(2, i) = rs("ActualColName")
    ActiveSheet.Cells(2, i).ID = rs("tblColName")
    rs.MoveNext
    Loop
    ActiveSheet.Cells(2, i + 1).ID = prdTblName
    rs.Close
    dbconn.Close

    MsgBox "Data downloaded sucessfully"
    Unload UserForm1

    End Sub

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Which line throws the error?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and what actually is the SQL you are running that reports this error
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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