CODEWITHSUNDEEP
sqlsql-servervbaexcel
user2237168
user2237168

Reputation: 303

Check if record exists before adding it to the database

Is there a way in VBA that allows one to check if a record exists in a database before adding a record?

I tried to capture a select query in a variable:

sSQL = "select count(*) from OptionRestriction where Feature_ID_1 = 1021 AND OptionValue_1 = 3 AND value = 0 AND Feature_ID_2 = 403 AND OptionValue_2 = 5 and visible = 1"

I used the variable in an if statement to check whether the count is not 1. If the statement is true the record should be added:

            If sSQL <> "1" Then
                conn.Execute "INSERT INTO OptionRestriction (Feature_ID_1, OptionValue_1, value, Feature_ID_2, OptionValue_2, visible) SELECT TOP(1) CF.FeatureID As Feature_ID_1, CF.OptionValue  As OptionValue_1, 0, OV.FeatureID As Feature_ID_2, OV.OptionValue  AS OptionValue_2, 1 From Value as CF Cross Join Value as OV INNER JOIN Feature f on CF.FeatureID = f.ID INNER JOIN Feature f_2 on OV.FeatureID = f_2.ID Where CF.Name = '" & Replace(s_OptionValue_1_s, "'", "''") & "' AND OV.Name = '" & Replace(s_OptionValue_2_s, "'", "''") & "'"
            End If

However this won't work in VBA. Any ideas how I can make this work?

UPDATE

Based on the comments of Vityata, I made the following changes:

I added a function:

Public Function RestrictionCount() As Long
     RestrictionCount = DCount("Feature_ID_1", "OptionRestriction", "Feature_ID_1 = 1023 AND OptionValue_1 AND value = 0  AND Feature_ID_2 = 403 AND optionValue_2 = 5 AND visible = 1")
End Function

And used the function in the code where my if resides:

Private Sub CommandButton1_Click()

   Dim conn              As New ADODB.Connection
   conn.Open             "<string to connect to DB. Not displayed here due to confidentiality reasons>"


            If RestrictionCount() <> 1 Then
                conn.Execute "INSERT INTO OptionRestriction (Feature_ID_1, OptionValue_1, value, Feature_ID_2, OptionValue_2, visible) SELECT TOP(1) CF.FeatureID As Feature_ID_1, CF.OptionValue  As OptionValue_1, 0, OV.FeatureID As Feature_ID_2, OV.OptionValue  AS OptionValue_2, 1 From Value as CF Cross Join Value as OV INNER JOIN Feature f on CF.FeatureID = f.ID INNER JOIN Feature f_2 on OV.FeatureID = f_2.ID Where CF.Name = '" & Replace(s_OptionValue_1_s, "'", "''") & "' AND OV.Name = '" & Replace(s_OptionValue_2_s, "'", "''") & "'"
            End If

End Sub

Now I am getting a compile error stating that a sub or function is not defined, while the DCount resides in another function. Is there a way to solve this? Sorry for my questions (I am new to VBA).

UPDATE

The check can now be performed thanks to the last comment of Vityata. The code is now as follows, including the loop I am using to add my data to the database:

    rs.ActiveConnection = conn
    rs.Open "select * from OptionRestriction where Feature_ID_1 = 1021 AND OptionValue_1 = 3 AND value = 0 AND Feature_ID_2 = 403 AND OptionValue_2 = 5 and visible = 1"

    i = 2
    Do Until IsEmpty(Cells(i, 1))
        s_OptionValue_1_s = ActiveSheet.Cells(i, 1)
        value_s = ActiveSheet.Cells(i, 2)
        s_OptionValue_2_s = ActiveSheet.Cells(i, 3)

        If value_s <> "" Then

            If rs.RecordCount <> 1 Then
                conn.Execute "INSERT INTO OptionRestriction (Feature_ID_1, OptionValue_1, value, Feature_ID_2, OptionValue_2, visible) SELECT TOP(1) CF.FeatureID As Feature_ID_1, CF.OptionValue  As OptionValue_1, 0, OV.FeatureID As Feature_ID_2, OV.OptionValue  AS OptionValue_2, 1 From Value as CF Cross Join Value as OV INNER JOIN Feature f on CF.FeatureID = f.ID INNER JOIN Feature f_2 on OV.FeatureID = f_2.ID Where CF.Name = '" & Replace(s_OptionValue_1_s, "'", "''") & "' AND OV.Name = '" & Replace(s_OptionValue_2_s, "'", "''") & "'"
            End If

            conn.Execute "INSERT INTO OptionRestriction (Feature_ID_1, OptionValue_1, value, Feature_ID_2, OptionValue_2, visible) SELECT TOP(1) CF.FeatureID As Feature_ID_1, CF.OptionValue  As OptionValue_1, '" & Replace(value_s, "'", "''") & "', OV.FeatureID As Feature_ID_2, OV.OptionValue  AS OptionValue_2, 0 From Value as CF Cross Join Value as OV INNER JOIN Feature f on CF.FeatureID = f.ID INNER JOIN Feature f_2 on OV.FeatureID = f_2.ID Where CF.Name = '" & Replace(s_OptionValue_1_s, "'", "''") & "' AND OV.Name = '" & Replace(s_OptionValue_2_s, "'", "''") & "'"
        Else
            conn.Execute "INSERT INTO OptionRestriction (Feature_ID_1, OptionValue_1, value, Feature_ID_2, OptionValue_2, visible) SELECT TOP(1) CF.FeatureID As Feature_ID_1, CF.OptionValue  As OptionValue_1, 1, OV.FeatureID As Feature_ID_2, OV.OptionValue  AS OptionValue_2, 0 From Value as CF Cross Join Value as OV INNER JOIN Feature f on CF.FeatureID = f.ID INNER JOIN Feature f_2 on OV.FeatureID = f_2.ID Where CF.Name = '" & Replace(s_OptionValue_1_s, "'", "''") & "' AND OV.Name = '" & Replace(s_OptionValue_2_s, "'", "''") & "'"
            conn.Execute "INSERT INTO OptionRestriction (Feature_ID_1, OptionValue_1, value, Feature_ID_2, OptionValue_2, visible) SELECT TOP(1) CF.FeatureID As Feature_ID_1, CF.OptionValue  As OptionValue_1, 0, OV.FeatureID As Feature_ID_2, OV.OptionValue  AS OptionValue_2, 1 From Value as CF Cross Join Value as OV INNER JOIN Feature f on CF.FeatureID = f.ID INNER JOIN Feature f_2 on OV.FeatureID = f_2.ID Where CF.Name = '" & Replace(s_OptionValue_1_s, "'", "''") & "' AND OV.Name = '" & Replace(s_OptionValue_2_s, "'", "''") & "'"
        End If

    i = i + 1
    Loop

The result in the database is now:

enter image description here

Yet, the row that is selected should not be there. So it needs to add 1021 - 3 - 0 - 403 - 5 - 1 only once. What do I need to change in my loop to achieve this?

The excel file from which the data is inserted into the database looks like this:

enter image description here

Upvotes: 2

Views: 10598

Answers (1)

Vityata
Vityata

Reputation: 43575

If you work in MS Excel, this is a sample of what you can use:

Public Sub CheckDataInSQLServer()

    Dim cnLogs              As Object
    Dim rsData              As Object

    On Error GoTo CheckDataInSQLServer_Error

    Set cnLogs = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")

    cnLogs.Open ConnectionString 'this is a string function

    rsData.ActiveConnection = cnLogs
    rsData.Open "SELECT * FROM A WHERE B = C;"

    If rsData.EOF Then
        debug.print "no values"
    Else    
        debug.print "has values"
    End If    

End Sub

In VBA the function that you need is called DCount, if you work with MS Access:

You can use the DCount function to determine the number of records that are in a specified set of records (a domain). Use the DCount function in Visual Basic, a macro, a query expression, or a calculated control. For example, you could use the DCount function in a module to return the number of records in an Orders table that correspond to orders placed on a particular date. MSDN

See more how to use DCount - DCount with 2 criteria

This is a sample how to build DCount in a function and to use it:

Public Function OrdersCount(ByVal strCountry As String, ByVal dteShipDate As Date) As Long
    OrdersCount = DCount("[ShippedDate]", "Orders", _
                  "[ShipCountry] = '" & strCountry & _
                  "' AND [ShippedDate] > #" & dteShipDate & "#")
End Function

If OrdersCount("Bulgaria", Now()) = 0 then
    'Execute the SQL query.
End if

Upvotes: 1

Related Questions