Deleting and Updating records in Access

[kidkarma]

Hi, I'm an undergraduate who's trying to learn Access, Excel and
correspondingly VBA as well.(hasn;t been easy)

Using Access as a database that is updated fortnightly from an excel
sheet.
The macro is supposed to pick out whether the details have been
uploaded(by matching staffname and date) and if it matches, to Delete
the old records of that particular staffmmember.

However, at the moement its hitting a
'run time error -
'syntax error(missing operator) in query expression 'tblData Where
StaffName = mystaff'


Code:

If rst("StaffName") = mystaff Then
If rst("Date") = startDate Then
response2 = MsgBox("You have already uploaded your details to the
database for this period. Do you want to replace previous
information?", vbYesNo)
End If
End If

If response2 = vbNo Then
ActiveWorkbook.Close
MsgBox "Data upload not performed"
End If

If response2 = vbYes Then
'will delete old data
With New ADODB.Connection

.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
.Execute "DELETE tblData Where StaffName = " & mystaff
rst.Update
.Close

End With

End If

Call sendto_managers2

Appreciate any help with this!

 

[Jason Lepack]

kidkarma said:

.Execute "DELETE tblData Where StaffName = " & mystaff

the problem is in your SQL Statement. First of all you have to tell
it which fields you want to delete and include the FROM. Also is
StaffName a text field? If so then you will need quotatin marks
around it.

Assuming StaffName is text you should have this:
"DELETE * FROM tblData WHERE StaffName = '" & mystaff & "'"

Note that "'" = " ' " without the spaces.

Cheers,
Jason Lepack

 

[Guest]

Hi kidkarma,

Congrats on learning Access... hang in there!!

Two things, you are missing the keyword FROM, and if mystaff is a text
field, your SQL needs to have some single quotes in it like this:

"DELETE FROM tblData Where StaffName = '" & mystaff & "'"

After the equals is single quote double quote then after the mystaff & is
double quote, single quote, double quote.

Hope this sorts it out for you.

Damian.

 

[onedaywhen]

Congrats on learning Access... hang in there!!

Two things, you are missing the keyword FROM, and if mystaff is a text
field, your SQL needs to have some single quotes in it like this:

"DELETE FROM tblData Where StaffName = '" & mystaff & "'"

Do you think 'learning Access' is something that has an end point <g>?
If so, hang in there, Damian, for I have spotted a flaw in your
reply...

If the variable mystaff contains a single quote, which is quite common
in some regions (e.g. O'Reilly), your code will create another SQL
syntax error. See this recent discussion about using ADO Parameter
objects to avoid the problem of having to 'manually' escape such
characters:

http://groups.google.com/group/micr...5513fdbb6c/9315af1f055374a5?#9315af1f055374a5

Jamie.

--

 

[kidkarma]

hey...you guys were completely right about the sql statement!!..it
runs smoothly now
Didn't expect such 'complete' help when i initially posted and
appreciate 'onedaywhen' and his link about using ADO parameters to
avoid the code breaking.

I'm pretty sure learning Access OR Excel doesn't have an end point, or
if there is, it's a long way ahead so thanks again for the help.

cheers