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