please help me out i am stuck here here i wanted to download data from excel to mysql but it is not downloading it insisted of it creating duplicate rows and columns please let me know i am new for this....
Sub downloadData()
Dim strSQL As New ADODB.Connection
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"
strSQL = "INSERT INTO [ODBC;DSN=mysql32;].wds " _
& "Select * FROM ;"
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"
Sheet3.Activate
Sheet3.Columns.Clear
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=mysql;" _
, 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\Documents\My Data Sources\mydbconnection.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
Bookmarks