delete method in access

[osi2301]

I have a problem with deleting records. I have a form in my database where I have included a delete button. The delete function works perfectly with an access tables (backend). When I connect that same form to the converted SQL tables (backend) it gives me an error message: run time error '3146' ODBC --call failed.

Here is my code:

Private Sub Command466_Click()
If IsNull([AsirID]) Then
MsgBox "The record contains insufficient information to delete", vbInformation, "OSMS"
ElseIf Not IsNull([AsirID]) Then

Dim lresponse As Integer

lresponse = MsgBox("Are you sure you want to permanently delete this record number from the OSMS database?", vbYesNo, "OSMS")
If lresponse = vbYes Then

Recordset.Delete

ElseIf lresponse = vbNo Then
DoCmd.CancelEvent
End If

What am I doing wrong?

 

[ssatech]

Not a SQL expert, but 3146 is generic error could be a connection failure error. It could be a timeout issue,wrong password etc.

"The delete function works perfectly with an access tables (backend)." and it would probably work (SQL backend)if you go to Queries and use a delete query.

Have you tried using the Docmd.RunSQL with a delete query behind you cmdbutton on your form to delete the record?

Otherwise, try posting your question in the SQL Server forum.

 

[PHV]

Recordset.Delete
???

 

[osi2301]

I have tried several different delete methods but it makes no difference. They all return the same error message

 

[PHV]

And why not simply this ?
DoCmd.RunCommand acCmdDeleteRecord

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[osi2301]

I have tried that and it returns another error message:

Run Time Error 2501
The DoCmd action was cancelled.

I'm baffled?

 

[PHV]

I meant this:
Private Sub Command466_Click()
If IsNull([AsirID]) Then
MsgBox "The record contains insufficient information to delete", vbInformation, "OSMS"
ElseIf MsgBox("Are you sure you want to permanently delete this record number from the OSMS database?", vbYesNo, "OSMS") = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End Sub

So, I wonder which action was cancelled ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[osi2301]

Gives me the same error message

 

[osi2301]

I am lost in no man's land. I have posted it on the SQL forum and they say they don't know because it is an Access problem.

I posted it on the Access forum and they say they don't know becuse it is a SQL problem.

Can someone please help?

 

[cmmrfrds]

It appears these are odbc linked tables.
1.Show your SQL.
2.Show your primary key defined on the sql server side. Was this key selected when you did the odbc link.
3. Is recordset.delete the Forms recordset? If so, is this a bound Form. If so, why are you not just deleting it in the Form.
4. Why not do the delete through SQL?

 

[osi2301]

I wish I was smart enough to know what you are talking about. Sorry for being dumb. However, you said something that intrigued me. Let me try to answer your questions.

1. Yes these are sql linked tables. These tables were created by first building them in Access and then converting them to sql through the upsize wizard.

The delete commands work perfectly when the tables are in Access but after converting to sql they throw up the error message.

You asked, "why not delete using sql". That sounds like an excellent idea. Can you tell me how this is done?

Cheers

 

[ssatech]

Have you tried:

Private Sub Command466_Click()
Dim strSQL As String

If IsNull([AsirID]) Then
MsgBox "The record contains insufficient information to delete", vbInformation, "OSMS"
ElseIf MsgBox("Are you sure you want to permanently delete this record number from the OSMS database?", vbYesNo, "OSMS") = vbYes Then

DoCmd.RunSQL "DELETE tblName.AsirID, tblName.SomeName FROM tblName WHERE (((tblName.AsirID)=[Forms]![frmName]![AsirID]));"

End If

MsgBox "Record Deleted"
DoCmd.Requery

End Sub

 

[cmmrfrds]

The first thing is to check that there is an unique index on the SQL Server side. If you don't know how to do this check with your DBA. When you link the odbc table you must select this unique index. In order to maintain records (update or delete) there must be an unique identification of the record. This should be done through the primary key which should be an unique index on the table. Another alternative to provide uniqueness is adding a timestamp field to the sql server table, but this is not the preferred way of handling.