How to Pass Number Variable to CurrentDb.Execute SQL Statement

[AlexB123]

Hi,

I'm working on a relatively old database that others created, and in order to deal with a duplicates issue I created a work-around by deleting all but one row on a specific problem table, tblRegisterCreation.

In the code in my workaround, I want to write an IF statement that counts the number of rows in tblRegisterCreation, and if there is more than one row then delete all but one.

To do this I created a numeric variable and tried to pass the value in to the .Execute command.

I am trying either:

Dim strSQL As String
Dim numRows As long


strSQL = "DELETE * " _
    & "FROM tblRegisterCreation " _
    & "WHERE " & numRows & " > 1 IN" _
      &  "(SELECT TOP (numRows -1) tblRegisterCreation " _
       &   "FROM tblRegisterCreation "


If DCount("*", "tblRegisterCreation") > 1 Then
 numRows = DCount("*", "tblRegisterCreation")
    CurrentDb.Execute strSQL

End If

Or

Dim numRows As long

If DCount("*", "tblRegisterCreation") > 1 Then
 numRows = DCount("*", "tblRegisterCreation")
    CurrentDb.Execute "DELETE * " _
    & "FROM tblRegisterCreation " _
    & "WHERE " & numRows & " > 1 IN" _
      &  "(SELECT TOP (numRows -1) tblRegisterCreation " _
       &   "FROM tblRegisterCreation);"
End If

At first I tried including "numRows" inside the quotations. I appreciate any feedback.

 

[xenou]

1) Is your aim to permanently have only one row in the table?
2) What is the primary key on this table and what is the datatype of that key?

 

[xenou]

I would probably run this this way:

Sub Fo22s()
    Dim minID As Integer
    Dim SQL As String
    
    minID = DMin("ID", "Table3")
    If minID > 0 Then
        SQL = "DELETE * from Table3 where ID > " & minID
        CurrentDb.Execute SQL
    End If
        
End Sub

This assumes that ID is the primary key, so basically get the smallest one and everything greater goes away. Sticklers for the truth will point out that ID can be less than zero, but I don't allow that haha. Basically, I don't want to run the query if the table is empty, but that's just a matter of preference.



But even better would be to stop additional rows from getting into the table in the first place.
For instance, if the one record in the table has ID 220 and ID is the primary key, then set a validation rule:
ID = 220

This effectively limits the table to one row with the one primary key value that is allowed.

 

[AlexB123]

1) Yes, my aim is to permanently have one row.
2) the primary key is just "ID", it is an AutoNumber (incremented long integer).

The table itself doesn't provide any useful information ... once upon a time it was going to do something, but it is just embedded in a number of joins.

 

[AlexB123]

I would probably run this this way:
But even better would be to stop additional rows from getting into the table in the first place.
For instance, if the one record in the table has ID 220 and ID is the primary key, then set a validation rule:
ID = 220

This effectively limits the table to one row with the one primary key value that is allowed.

I believe this worked pretty well! I had to put the field "ID" in as:

[ID] = 6

Thanks!

The SQL looks like it might work too, hopefully the validation rule does everything I need.

 

[xenou]

So my prefererred solution would be as above - delete all but one row, then set the validation rule to never allow any more rows to be entered. Then, problem is solved.

Edit:
Nevermind - you got it.