Unable to Delete Record In Access 2007 Table Using VBA

Urgent help needed.

can you post vba

Are you enforcing referential integrity?  If so have you deleted the dependant records first?

I have three tables. One table lists software by make and manufaturer. It has an autonumber key field. The second table lists all of the subject matter experts in our company. The third table links them. There will be one software item assigned to a single subject matter expert. But a single subject matter expert can be assigned to many software items. The third linkage table has only two fields; the software ID and the email address of the SME. They are the combinational key for that table. I can write a query that will delete the row. But when I use the exact same SQL in a VBA module it says that it deleted the row but it really did not and it did not give me an error. I will copy the code:

strSQL ="DELETE LinkageTable.ID, LinkageTable.EmailAddress FROM LinkageTable "
strSQL = strSQL & "WHERE (((LinkageTable.ID = " & "" & intID & "" & ")  "
strSQL = strSQL & "AND ((LinkageTable.EmailAddress =  " & """" & strEmailAddress & """" & "));"

StrSQL is a string, intID is a long Integer, and strEmailAddress is a string.  The source of the variables are combo boxes. The form is linked to the LinkageTable. I use Message Boxes to show me the ID, email address, and SQL code. All look normal. Nothing happens! Thank you both.

Youre deleting an entire record vs individual fields, so try this cleaned up version:


strSQL ="DELETE * FROM LinkageTable WHERE ID = " &  intID &  "AND EmailAddress =  " & chr(34) & strEmailAddress & chr(34)

I appreciate it. Thank you.

And run it using CurrentDB.Execute:

strSQL ="DELETE * FROM LinkageTable WHERE ID = " &  intID &  "AND EmailAddress =  " & chr(34) & strEmailAddress & chr(34)
CurrentDB.Execute strSQL, dbFailOnError

Oops - You were missing an important space before the AND, too...

strSQL ="DELETE * FROM LinkageTable WHERE ID = " &  intID &  " AND EmailAddress =  " & chr(34) & strEmailAddress & chr(34)
CurrentDB.Execute strSQL, dbFailOnError

Dear mbizup,
     I followed your directions. This time I did not receive a notice that the deletion of one record took place. I did not get an error. But, the stubborn record remains. I can manually delete it from the table. I can use the query wizard and give it the ID and emailaddress and delete the record. But VBA seems to be unable to. Any other ideas would be appreciated. Is there a possible record lock? I turned off Referenctial Integrity on the relationships to see if that would help. No luck!

That should work...

If you can post a sample of your database with any sensitive data masked or removed, I'll take a look.

Just a quick note -

If this is code running 'in the background' and you are observing records on a form, you will have to requery that form for the deletion to be noticeable (it should be noticeable right away if you look at the records directly in the table).

<This time I did not receive a notice that the deletion of one record took place. I did not get an error.>

CurrentDB.Execute does not give the 'warning' that RunSQL does.  However, the dbfailOnError parameter associated with CurrentDB.Execute will let you know if your query fails, by giving you any error message encountered (this is a benefit of the Execute method - runSQL is not so informative).

I will try to find a way to send you a version that doesn't have real email addresses in them. I trust a Genius but it would be irresponsible of me to not be prudent.

I have a form with combo boxes. The entire function of this form is to remove the link between the software item and the SME. This happens because my SMEs go to other projects and I have to unlink them and then link new SMEs. First I select the SME from the Table of SMEs. That table cascades the list of software that that SME is responsible for. I select the software item and then click on the 'Delete Link' command button. That button initiates the VBA code that takes the software ID and the SME email address and puts them into variables. The variables are used in the SQL statement to select the correct record for deletion. I don't rely on the form to confirm the deletion that is why I didn't requery it. I put a MsgBox in to show me the SQL code before execution and it looks exactly like what you sent me with the variable values included. It may take a day to figure out how to send a version without real names. But I do appreciate your help.

No trouble - anything posted out here is publically visible, not just to EE Members so while samples are a tremendous help in getting problems resolved, you do have to be very careful.

You helped. Your code suggestion allowed for a 75% solution. If I try to delete one link, close the form, and then look in the table there is no change. If I go back into the form and do two unlinks in a row and close the form, only the first of the two is deleted. If I keep the form open and do any number of unlinks they are deleted. It seems that closing the form replaces the last delete. I am attaching the database. I use the unlink form to delete the link. You will see all when you see the code. This is all very unusual. Please look into this. Thank you team of experts and you mbizup for the code. Support-Copy-PreProd-N-1TrackerV.accdb

Hi-
I'll take a look at it first thing  in the morning.   Offhand, it sounds like an issue with refreshing and/or requerying the form.

I think you are right. It seems that I can delete one or more records but the last record I delete is always refreshed in the table. I played with it for hours last night and came to this conclusion. Thank you mbizup.

Can you briefly describe in terms of forms opened, items selected, buttons clicked, etc the steps that you are following (a list of steps to mimic what you are doing would be ideal)

I open form frm RemoveSMESoftwareLinkage090611
Using drop downs I select the SME (the E-Mail_Address field at the botton of the form is filled in). Then I select the Software Manufacturer, then the Software Make and then the Software Version. When I select the software version the ID is populated by the record ID of the software. Now I have the two components for the linkage table. Finally I select 'Delete Record'. If I close out of the form the record will not be deleted (possibly refreshed). If I delete two records then close the form, the first record is deleted and the second is not deleted. It seems that when the form is closed it is refreshing the linkage table with the very last record.

I can understand exactly why you hold the rank of Genius! Thank you very much. I would have never thought of the cause of the issue on my own.

You are awesome AND a Genius. Thank you.

I'm glad that worked out for you.

Thank you for the good words :)