Delete records from table using VBA

[Barbados]

Hi,

I am trying to use the following code for deleting records in a table:

    Dim adoCMD As Object
    Dim adoRS As Object
    Dim strSQL As String
    Dim adoERR As ADODB.Error
    'Define a query to DELETE records from table
    strSQL = "DELETE FROM [" & "tblStrategy" & "] WHERE [StrategyName] = " & Forms(formName).cmb_RemoveStrategy.Value
    'Define attachment to database table specifics
    Set adoCMD = CreateObject("ADODB.Command")
    With adoCMD
        .ActiveConnection = CurrentProject.Connection
        .CommandType = adCmdText
        .CommandText = strSQL
 
        Set adoRS = .Execute
    End With

It results in the error: No value given for one or more required parameters.

Any idea what is missing?

Thanks.

 

[mdlueck]

What is the data type of column StrategyName? I would think it a string data type, thus single quotes are missing around the value being supplied.

About error handling, here you go for some reading:

Robust VBA Error Handler
http://www.access-programmers.co.uk/wiki/index.php/Robust_VBA_Error_Handler

Interrogation of ADO object details in the Error Handler
http://www.access-programmers.co.uk/forums/showthread.php?t=245942

You have an excellent start, using an adoCMD object. How about using an ADO Parameter object and named parameter syntax instead of concatenating the value into the SQL string? Some helpful posts in that light:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

I just do not happen to have a sample worked up of a DELETE query.