runtime error Run time error'-2147217887(80040e21) odbc driver doesnt support requested properties
Posted by:
usman baig
Date: March 28, 2014 12:43AM
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
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