Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

VB6 - error when deleting a row in MS access

Hello all

I have an error with the code bellow when i try to delete a row from my VB6 app

In my VB6 app, i have a textbox called: ID_NUM.

In my ID_NUM textbox, i can put a row ID number from my MS Access table.

But when i click on the delete command, i have this error saying:
Runtime error -2147217913. Data type mismatch in criteria expression.

How can i fix that?

Thanks again for your help


 Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub delete_Click()
    Select Case MsgBox("Do you realy want to delete that record?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Delete...")

    Case vbYes
Set con = New ADODB.Connection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & link_for_base.Caption
        con.Open
        rs.Open "SELECT * FROM [TDM_Rate_form_no1] WHERE ID='" & ID_NUM.Text & "'", con, adOpenForwardOnly, adLockReadOnly

        con.Execute ("DELETE * From [TDM_Rate_form_no1] WHERE ID= '" & ID_NUM.Text & "' ")
        con.Update
        con.Close


    Case vbNo

    End Select
End Sub

Open in new window

Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Is the data type for ID numeric and not String.  If so it should be:
WHERE ID= " & ID_NUM.Text

I don't see where you need to use the recordset (rs) in your code.  It seems you could just shorten it to:
Private Sub delete_Click()
    Select Case MsgBox("Do you realy want to delete that record?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Delete...")

    Case vbYes
        Set con = New ADODB.Connection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & link_for_base.Caption
        con.Open
        con.Execute ("DELETE * From [TDM_Rate_form_no1] WHERE ID= '" & ID_NUM.Text & "' ")
        con.Update
        con.Close
    End Select
End Sub

Open in new window

What are the data types of id and id_num?
ASKER CERTIFIED SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Wilder1626

ASKER

Thank you so much

all work with:

       Set con = New ADODB.Connection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & link_for_base.Caption
        con.Open
        con.Execute ("DELETE * From [TDM_Rate_form_no1] WHERE ID= " & ID_NUM.Text)
        con.Update
        con.Close

Open in new window