CODEWITHSUNDEEP
mysqlvbaexcel
Prachi Agarwal
Prachi Agarwal

Reputation: 79

Deleting mysql table entries using a macro

I am writing a macro by which I want to delete few names in column A of sheet 2 from the server table called login.

Database name is "my_db" and table name is login. I calling connection to connect to the server databse. Following is the complete code :-

Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim Sql As String
Sub Button1_Click()
Dim Ws As Worksheet
Dim Var As String
Dim i As Integer
i = 1
Set Ws = Worksheets("Sheet2")
Var = "login"
Sql = "name"
Call Connection


Do

Sql = " Delete From " & " " & Var & "" & " Where name" = "Ws.Cells(i, 1)"





    Call Connection
  i = i + 1
  Loop Until Ws.Cells(i, 1) = ""

  MsgBox "All entries deleted from login table"


End Sub

Dim strUserName As String
Dim strPassword As String
Dim ConnectString As String
Set cnt = New ADODB.Connection


strServerName = "localhost"
strDatabaseName = "my_db"
strUserName = "root"
strPassword = "root1"


ConnectString = "DRIVER={MySQL ODBC 5.1 Driver};" & _
                "SERVER=" & strServerName & _
                ";DATABASE=" & strDatabaseName & ";" & _
                "USER=" & strUserName & _
                ";PASSWORD=" & strPassword & _
                ";OPTION=3;"



 With cnt
        .CursorLocation = adUseClient
        .Open ConnectString
        .CommandTimeout = 0
        Set rst = .Execute(Sql)
    End With
    'rst.Close
     'cnt.Close

    Set rst = Nothing
    Set cnt = Nothing

End Sub

But the entries are not getting deleted. There is an error in SQL syntax, can anyone help me with the sql syntax ?

Upvotes: 0

Views: 1318

Answers (2)

Lorenz Meyer
Lorenz Meyer

Reputation: 19895

Since you pasted the code only partially, I can only guess. 

Do
Sql = " Delete From " & Var & " Where name" = "Ws.Cells(i, 1)"
Call Connection (Sql)
i = i + 1
Loop Until Ws.Cells(i, 1) = ""

Clean up your query. And more important pass it as a parameter to the connection.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166126

Sql = "delete From " & Var & " where name ='"  & Ws.Cells(i, 1).Value & "'"

but it would be better to open the connection only once, run all the deletes, and then close the connection.

Also you don't need a recordset here (since you're not returning any records), so you can ignore the return value from cnt.Execute

Upvotes: 1

Related Questions