I want to be able to delete a record from a sql server table based on a value in a cell on an Excel 2007 Spreadsheet this to be done via a VB button.

The table I'm accessing is a stand alone table of just Title and FirstName, created and used by myself only, It is utilised for sql extracts for comparison for male and female names ie if the name exists in the table don't pull the record and so on. There are no security issues here.
I'm very new to VB, being primarily an Oracle developer, so I'm doing this as a test project (No help from company) to make my work a bit easier when working on spreadsheets.
I can do all the operations I need in SQL Server Manager using SQL but I want to try this as an exercise.
Obviously if someone could show me some coding I would be grateful.
I have already created the search, and add new record functions but I'm finding no joy in getting any code for the delete function.

Hoping someone can help.



[Edited by Fluff63 on 12-01-2012 at 09:26 AM GMT]

You don't say how you are connecting to the SQL-Server from your VBA project, so I will assume that you will be using ADODB.Connection and have set a Variable "cnn" for the Connection, and also that your Table ("NamesList")has a Primary Key for each name entry - Lets call it "NameID" - that you will be retrieving with the Search function that you already have.
Create a Stored Procedure in your SQL-Server Database to handle the "Delete" -

CREATE PROCEDURE DeleteNameEntry  (@NameID int) AS DELETE FROM NamesList WHERE     (NameID = @NameID) GO

You can now call this SP from your VBA project code with a Sub using ADODB.Command Object and passing the NameID Parameter to your Sub from the "Search" Result

Private Sub DeleteNameEntry (lngID As Long)         Dim cmd As New ADODB.Command         Dim param As ADODB.Parameter                          cmd.CommandType = adCmdStoredProc         cmd.ActiveConnection = cnn         cmd.CommandText = "DeleteNameEntry " Set param = cmd.CreateParameter("@NameID", adInteger, adParamInput) cmd.Parameters.Append param param.Value = lngID cmd.Execute Set cmd = Nothing End Sub

You may want to add some Error trapping to the Sub if someon else is going to be running it, but do it without first so that you know your code is working.


____________________________
multi-tasking - the ability to hang more than one app. at the same time.

Thanks for the reply. sorted now.