469,588 Members | 2,766 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

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

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.

Expand|Select|Wrap|Line Numbers
  1. ' exports data from the active worksheet to a table in an Access database
  2. ' exports data from the active worksheet to a table in an Access database
  3.  
  4. Dim db As DAO.Database      ' PAR History Database location/file
  5. Dim rs As DAO.Recordset     ' Table name
  6. 'Dim rs As Recordset
  7. Dim cl As Long              ' Current Line counter
  8. Dim vcurpar As String       ' Current PAR number (N2)
  9.  
  10.  
  11. vcurpar = "Notifications!N2"
  12.  
  13.  
  14.     Application.StatusBar = "Open PAR History Database"
  15.  
  16.  
  17.     ' open the database
  18.     Set db = OpenDatabase("C:\My Stuff\Projects\GCUK\PAR Change Control\PARHistory.mdb")
  19.  
  20.     ' Delete any previously PAR with this number from history.
  21.     Application.StatusBar = "Checking and removing if PAR has been archieved previously"
  22.     vtSql = "DELETE * FROM CircuitHistory WHERE parnumber = " & vcurpar & ";"
  23.  
  24.     'db.Execute (vtSql)
  25.  
  26.  
  27.  
  28.     Set rs = db.OpenRecordset("SELECT * FROM CircuitHistory WHERE CircuitHistory.parnumber = 'PAR123456'")
  29.     If Not rs.EOF Then
  30.         MsgBox (rs.RecordCount)
  31.  
  32.         rs.Delete
  33.     End If
  34.  
  35.     rs.Close
  36.  
  37.  
  38.  
  39.  
Feb 13 '07 #1
1 2148
willakawill
1,646 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. vtSql = "DELETE * FROM CircuitHistory WHERE parnumber = '" & vcurpar & "';"
Good luck
Feb 15 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

21 posts views Thread by bobh | last post: by
12 posts views Thread by slinky | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.