i am trying to download data or save from excel to mysql server but it is not downloading to mysqlserver

[ubaig]

i am trying to download data or save from excel to mysql server but it is not downloading to mysqlserver please help me with the solution
this is the code i used it

Option Explicit
'Public strConn As String
Public projCode, batchCode, batchID, prdTblName
Public Const strConn = "Provider=MSDASQL.1;Password=bmx1;Persist Security Info=True;User ID=root;Data Source=mysql32;Initial Catalog=wds"
Sub init()
projCode = ""
batchCode = ""
UserForm1.Show


End Sub




Sub dbConnect()
Dim strConn As String
Dim dbconn As New ADODB.Connection

strConn = "Provider={MySQL ODBC 5.2 ANSI Driver};Password=bmx1;Persist Security Info=True;User ID=root;Data Source=mysql32;Initial Catalog=wds"
' strConn = "DSN=mysql32;DESCRIPTION=data;SERVER=localhost;UID=root;PWD=bmx1;DATABASE=wds;PORT=3306"
strConn = "Provider=MSDASQL.1;Password=bmx1;Persist Security Info=True;User ID=root;Data Source=mysql32;Initial Catalog=wds"
'dbconn.Open dsn = mySQL_UserDSN
dbconn.Open strConn


If dbconn.State = adStateOpen Then
MsgBox "Connected"
Else
MsgBox "DB Connection Failed"
End If
dbconn.Close

End Sub

Sub updateData()
Dim dbconn As New ADODB.Connection


Dim totColumns, totRows, i, j, WDS_id
Dim prBatchName, prTableQry, inTableQry, prTableQry1, dbQry
Dim rs As New ADODB.Recordset


Sheet1.Activate
prBatchName = "tblProd_AGR_007"


totColumns = ActiveSheet.Cells(2, 1).CurrentRegion.Columns.Count
totRows = ActiveSheet.Cells(2, 1).CurrentRegion.Rows.Count


dbconn.Open strConn
rs.Open "select * from " & prBatchName, dbconn, adOpenStatic, adLockOptimistic
For j = 3 To totRows
WDS_id = ActiveSheet.Cells(j, 1)
rs.Find "WDS_ID=" & WDS_id

For i = 2 To totColumns
rs(ActiveSheet.Cells(2, i).ID) = ActiveSheet.Cells(j, i)
Next
rs.Update

Next
rs.Close
dbconn.Close
MsgBox "Data updated sucessfully"


End Sub




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\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

 

[mole999]

this is getting close to spamming the forum

http://www.mrexcel.com/forum/excel-questions/767685-downloading-data-mysql.html