VB Take a value from Excel to delete row from Access table

[SteveBark]

Hello all

I am currently trying to develop a script that will take a value from an Excel spreadsheet cell and use that to run a query against an Access table to delete all rows that match the cell.

I have tried to do this by first using a recordset and then secondly using a db.Execute (sqlstring).

The idea of using the recordset was that I thought it would return all of the records matched, however it just deletes the 1 row not all of the entries that match.

Is this just the way I have opened the recordset?

For the db.Execute I'm getting a 3061 Error - Too few parameters!

Any help truly appreciated.

' exports data from the active worksheet to a table in an Access database
' exports data from the active worksheet to a table in an Access database
 
Dim db As DAO.Database      ' PAR History Database location/file
Dim rs As DAO.Recordset     ' Table name
'Dim rs As Recordset
Dim cl As Long              ' Current Line counter
Dim vcurpar As String       ' Current PAR number (N2)
 
 
vcurpar = "Notifications!N2"
 
 
    Application.StatusBar = "Open PAR History Database"
 
 
    ' open the database
    Set db = OpenDatabase("C:\My Stuff\Projects\GCUK\PAR Change Control\PARHistory.mdb")
 
    ' Delete any previously PAR with this number from history.
    Application.StatusBar = "Checking and removing if PAR has been archieved previously"
    vtSql = "DELETE * FROM CircuitHistory WHERE parnumber = " & vcurpar & ";"
 
    'db.Execute (vtSql)
 
 
 
    Set rs = db.OpenRecordset("SELECT * FROM CircuitHistory WHERE CircuitHistory.parnumber = 'PAR123456'")
    If Not rs.EOF Then
        MsgBox (rs.RecordCount)
 
        rs.Delete
    End If
 
    rs.Close
 
 
 
 

[willakawill]

Hi. A couple of things. When you are not assigning a return value, don't enclose your parameters in brackets as in MsgBox (rs.RecordCount) or db.Execute (vtSql)
Secondly, in the two sql statements you have alternately referenced parnumber as numeric and textual. As the textual version seems to work in the second case here is a change to the code that will most likely work in the first case:

vtSql = "DELETE * FROM CircuitHistory WHERE parnumber = '" & vcurpar & "';"

Good luck