Cant Delete Records from SQL in MS Access

[1DMF]

Hello,

This one is doing my head in, I have a table in SQL which is used for reporting, so I want to clear ALL records before processing and inserting the new ones.

The INSERT works fine, however why won't either of these work

CurrentDb.Execute "DELETE FROM EOM_Reporting WHERE 1=1", dbSeeChanges

CurrentDb.Execute "DELETE * FROM EOM_Reporting", dbSeeChanges

why won't SQL delete the records, if I run the first direct in the SQL Analyser it works fine and deletes the records, so what is wrong with Access ?

 

[PHV]

Has the connection's user the sql permission to delete records in this table ?

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

 

[1DMF]

Yes permissions are fine as we have DBO for the access system/users in it.

I've tracked it down to a lock violation but do not understand what is locking the table?

any ideas why access won't delete the records with a lock violation, i found this out when changing the code to.

DoCmd.RunSQL "DELETE FROM EOM_Reporting WHERE 1=1", dbSeeChanges

 

[1DMF]

Does anyone know why my SQL table has lot's of lovely records in it but when I view the table Via Access it has
#Deleted #Deleted #Deleted #Deleted
in every field for every record.

 

[Crowley16]

because you've run the DELETE sql in your access front end...

a lock is exactly that, something which LOCKS a record so you can't change/delete it, but can still see it. ANY locks will stop you from deleting records, irrespective of the frontEnd you're using...

--------------------
Procrastinate Now!

 

[1DMF]

No - i've removed the table from Access - relinked it, and the records Show fine in SQL and #Deleted in Access ? WHY

Also the delete won't work if I run the delete the records stay in SQL, what am I doing wrong ?

 

[1DMF]

Ok this is where the problem is and I have no Idea why it is happening, can anyone help ?

1stly it is nothing to do with the delete, I am inserting records into SQL the records show in SQL but show #Deleted in Access, WHY?

The insert is working fine the records appear in SQL but opening the linked table in Access they all show #Deleted.

It's all beyond me, here is my code....

=========================================================
Function EOM_Rep(sRep As String)

Dim rs1 As DAO.Recordset
Dim sSQL As String
Dim iAdv As Integer
Dim iP_Adv As Integer
Dim iFee As Integer
Dim iLead As Integer

On Error GoTo end_EOM

If vbNo = MsgBox("This will produce End of Month Reporting, are you sure you want to do this?", vbYesNo) Then
Exit Function
End If

If DCount("*", "EOM_Reporting", "ID IS NOT NULL") > 0 Then
' Delete ALL records from Reporting Table
CurrentDb.Execute "DELETE * FROM EOM_Reporting;"
End If

'Grab Member AR's
sSQL = "Select [CompanyName],[FSA],[Membership Date],[AppDate] FROM [Contacts] WHERE [ContactTypeID] = 'Member' AND [MembershipLevel] = 'Appointed Rep' ORDER BY [CompanyName]"
Set rs1 = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot, dbSeeChanges)

Do While Not rs1.EOF
'Get Advisers
iAdv = DCount("*", "Contacts", "[CompanyName] = '" & rs1.Fields("CompanyName") & "' AND [ContactTypeID] = 'Member' AND ([MembershipLevel] = 'Adviser' OR [MembershipLevel] = 'Trainee')")
'Get Prospective Advisers
iP_Adv = DCount("*", "Contacts", "[CompanyName] = '" & rs1.Fields("CompanyName") & "' AND [ContactTypeID] = 'Prospect - ADV'")
'Get Fee Charges Scale
iFee = Nz(DLookup("[BrokerActual]", "[Charges]", "[CompanyName] = '" & rs1.Fields("CompanyName") & "'"), 0)
'Workout Lead Time
iLead = DateDiff("D", rs1.Fields("AppDate"), rs1.Fields("Membership Date"))
'MsgBox "AR = " & rs1.Fields("CompanyName") & ", ADV = " & iAdv & ", Pro = " & iP_Adv & ", Fee = " & iFee & ", Lead = " & iLead

'Build insertion string
sSQL = "'" & rs1.Fields("CompanyName") & "','" & rs1.Fields("FSA") & "','" & rs1.Fields("Membership Date") & "'," & iAdv & "," & iP_Adv & "," & iFee & ",'Current','" & rs1.Fields("AppDate") & "'," & iLead
'Insert Record
CurrentDb.Execute "INSERT INTO EOM_Reporting ([AR_Name],[FSA_No],[Appointed],[Advisers],[P_Advisers],[Fee],[Catal],[App_Rec],[Lead]) Values (" & sSQL & ")", dbSeeChanges
' Next Record
rs1.MoveNext
Loop

'Clear recordset
Set rs1 = Nothing

'Grab Member AR's
sSQL = "Select [CompanyName],[FSA],[Membership Date],[AppDate] From [Contacts] WHERE [ContactTypeID] = 'Prospect - AR' AND [AppDate] IS NOT NULL ORDER BY [CompanyName]"
Set rs1 = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot, dbSeeChanges)

Do While Not rs1.EOF
'Get Advisers
iP_Adv = DCount("*", "Contacts", "[CompanyName] = """ & rs1.Fields("CompanyName") & """ AND [ContactTypeID] = 'Prospect - ADV'")
'Build insertion string
sSQL = """" & rs1.Fields("CompanyName") & """,'" & rs1.Fields("FSA") & "','" & rs1.Fields("Membership Date") & "',0," & iP_Adv & ",0,'Prospect','" & rs1.Fields("AppDate") & "',0"
'Insert Record
CurrentDb.Execute "INSERT INTO [EOM_Reporting] ([AR_Name],[FSA_No],[Appointed],[Advisers],[P_Advisers],[Fee],[Catal],[App_Rec],[Lead]) Values (" & sSQL & ")", dbSeeChanges
' Next Record
rs1.MoveNext
Loop

'Clear Recordset
Set rs1 = Nothing
CurrentDb.Close

Exit Function

 

[PHV]

Does access recognize the PrimaryKey of EOM_Reporting ?

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

 

[1DMF]

How do you mean recognise it ? SQL is getting the records inserted and SQL has the key is set with Identity

 

[1DMF]

OK - Got it sorted, Thanks PHV - you were spot on - Access wasn't recognising the Primary Key

The key was defined as BIGINT which I guess access can't handle. I changed it to INT and it works fine.

Regards,

1DMF