Deleting records in Access with VBA in Excel

[carlleese24]

hi

I am trying to delete all records in Access using a VBA in excel by typing a persons name in cell A1 and then the VBA will delete all the records in MS Access that contains that name. The Field in access is called names

I would really appreciate it if someone could help me with this please

 

[carlleese24]

TOP

 

[carlleese24]

hi

I managed to get some sort of code for it now

Sub DeleteAccessData22()
    Dim cnn As ADODB.Connection
    Dim sql As String
    Dim sPath As String
    Dim sTblName As String
    
    Set cnn = New ADODB.Connection
    
    'Define Database Path
    sPath = "E:\Files ready for disc inclu sp2\Files Ready for disc\racing data\Football Data\Football database\Football_Spread_Betting_Analysis.mdb"
    
    'Define Table Name
    sTblName = "TableName"
        
    'Define Connection String
    cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
                         & "Data Source=" & sPath
    'Open Connection
    cnn.Open
    
'Create Sql
    sql = "DELETE " & sTblName & ".FieldName FROM " & sTblName & " WHERE (((" & sTblName & ".FieldName)=Sheets("Sheet2").Range("a1")));"
    

    
    'If you Want to delete All data from the table uncomment below
    'sql = "DELETE FROM " & sTblName
    
    'Execute Sql
    cnn.Execute (sql)

    'Close
    cnn.Close
    Set cnn = Nothing
End Sub

I am getting this error message when I run it

compile error
Syntax error

Pointing to this line of coding

WHERE (((" & sTblName & ".FieldName)=Sheets("Sheet2").Range("a1")));"

Hope someone can help me with this it would appreciated

 

[srcera]

Maybe others were reluctant to post due to the "deletion" nature, but I corrected your code and found it helpful in my own project.

Try

sSQL = "DELETE " & sTblName & ".FieldName FROM " & sTblName & " WHERE (" & sTblName & ".FieldName) =" & Sheets("Sheet2").Range("a1") & ";"

Assuming you have a field named "FieldName" and it is not a variable.