How to track or limit multiple uploads to a SQL Server database?

Question

I am new to dev work and I am working on an excel sheet with an upload macro button that allows a user to upload (insert statement) their data into our SQL Server database.

There are only 7 users. How would I go about limiting the number of uploads to our database in case they click the upload button multiple times?

We are trying to have all 7 users submit it to an in charge person that will do the clicking of upload but just want to minimize user error. Any help much appreciated thanks.

score 0 · Answer 1 · answered May 23 '16 at 14:34

0

There are various ways to achieve this by properly architecting your solution. The most obvious approach is to design your DB schema in such a way that duplicates are not allowed by design.

Primary keys should be your friend here. Make sure the primary keys on your tables in question are properly defined.

So the problem with repeated inserts can be mitigated even if inserts are attempted by all the 7 users, any number of times.

answered May 23 '16 at 14:34

user46743

89
1
10

thanks . some one had told me to assign keys to reference months since each month only 7 can be uploaded. looking into that now. – T Dang May 23 '16 at 17:16
If you don't have straightforward columns as primary keys, you can also try out assigning complex primary keys. These keys should help identify as to what constitutes a duplicate. So pretty much you define your key rules and let the sql server engine do the heavy lifting. [link](http://stackoverflow.com/questions/3922337/how-to-create-composite-primary-key-in-sql-server-2008) – user46743 May 23 '16 at 17:54

score 0 · Answer 2 · answered May 23 '16 at 14:43

Store the number of uploads per user in a hidden sheet and use something like this in the VBA:

Sub Upload()

Dim uploadWS As Worksheet
Dim userRow As Range
Const MAX_UPLOADS As Byte = 3 '// Change to whatever you want

Set uploadWS = Sheets("Upload Data")

'// lets assume userID in column A and number of uploads in column B
Set userRow = uploadWS.Range("A:A").Find(Environ$("USERNAME"))

If userRow Is Nothing Then
    With uploadWS.Range("A" & uploadWS.Rows.Count).End(xlUp).Offset(1, 0)
        .Value = Environ$("USERNAME")
        .Offset(0, 1).Value = 1
    End With

    '// Code for uploading here
Else
    With userRow.Offset(0, 1)
        If .Value <= MAX_UPLOADS Then
            .Value = .Value + 1
        Else
            MsgBox "You have exceeded max allowed uploads, please contact admin.", vbOkOnly + vbCritical, "Error"
            Exit Sub
        End If
    End With

    '// Code for uploading here

End If

End Sub

How to track or limit multiple uploads to a SQL Server database?

2 Answers2