CODEWITHSUNDEEP
sqlsql-server-2008ms-accessvbams-access-2002
user3608243
user3608243

Reputation: 63

MS Access 2002 / MS SQL 2008 R2 - Problems deleting records based on certain criteria

I'm trying to get my code to delete records from an access 2002 database table that is linked to an MS Sql 2008 database table through a form event. I just want it to delete all records where User is null and Date is null.

Here's the snippet that's giving me trouble: 

Dim delSQL As String

DoCmd.SetWarnings False
delSQL = "DELETE * FROM Notifications WHERE User IsNull;"
Set rst = CurrentDb.OpenRecordset(delSQL, dbOpenSnapshot)

DoCmd.RunSQL delSQL
rst.Close
Set rst = Nothing
DoCmd.SetWarnings True

I honestly don't know why this isn't working. I ran the SQL statement above (not including the Date part as I wanted to get the User criteria working first) on the SQL server side and it works. But every time I try to get it to run in VBA for Access 2002, I get an error when it goes to execute the SQL.

Upvotes: 1

Views: 78

Answers (1)

HansUp
HansUp

Reputation: 97101

User is a reserved word. Either enclose it in square brackets, or qualify the field name with the table name or alias.

IsNull is a function. Either use Is Null as @AlexK. suggested or use the field name as the argument to IsNull().

The * in DELETE * is not required. Access will not throw an error if you include it. And it can be useful when you wish to preview the affected rows in the Access query designer's Datasheet View.

delSQL = "DELETE * FROM Notifications AS n WHERE n.User Is Null"

I suggest you leave SetWarnings on, and use CurrentDb.Execute with dbFailOnError to execute the DELETE. Since the data source is a linked SQL Server table, also include dbSeeChanges:

DoCmd.SetWarnings True
CurrentDb.Execute delSQL, dbSeeChanges + dbFailOnError

That approach will give you better information about any problems encountered.

Upvotes: 1

Related Questions