How to Pass Number Variable to CurrentDb.Execute SQL Statement

AlexB123

Board Regular
Joined
Dec 19, 2014
Messages
207
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:

Code:
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

Code:
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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?
 
Last edited:
Upvote 0
I would probably run this this way:
Code:
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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:
Code:
[ID] = 6

Thanks!

The SQL looks like it might work too, hopefully the validation rule does everything I need.
 
Upvote 0
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top