MySQL Forums
Forum List  »  MySQL for Excel

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

Options: ReplyQuote


Subject
Views
Written By
Posted
runtime error Run time error'-2147217887(80040e21) odbc driver doesnt support requested properties
4912
March 28, 2014 12:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.