Home / Questions / 463318
Accepted
Karla Rojas
Asked: 2022-06-20 10:45:11 +0800 CST 2022-06-20 10:45:11 +0800 CST

How to check if values ​​exist from Excel range to Access table before loading data?

  • 772

I am writing a macro that loads values ​​from Excel to a database table in Access, what I want it to do is to go through a range Range("D" & i).Valueof values ​​in Excel validating that they are registered in the Access table CAT_DOMINIO_REFERENCIA, and if they do not exist, add the registration of a specific form, the table CAT_DOMINIO_REFERENCIAhas 3 fields, which are INTERNO_DOMAIN , DESCRIPCION_DOMINIO , PALABRA_CLAVE, of these only the search match with DESCRIPCION_DOMINIO should be taken into account, which is related to the existence validation range in Excel, the other two fields must be registered in case there is no search match, the value "maximum +1" already registered in the case of the INTERNAL_DOMAIN field, and for the PALABRA_KEY_field the same value of DESCRIPCION_DOMAIN must be registered.

I'm working on this macro but I still can't get it to work

Sub Update()
Const Sig_DB As String = "SIG_2012.mdb"
Dim cnn As ADODB.Connection
Dim MyConn As String
Dim rs, rst As ADODB.Recordset
Dim QuerySql, ConsultaSql As String
Dim ClaseDem As String, i As Long

Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & Sig_DB
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn


With Sheets("DEM")
uf = .Range("D" & Rows.Count).End(xlUp).Row
End With
For i = 3 To uf
ClaseDem = Range("D" & i).Value
QuerySql = "Select* from CAT_DOMINIO_REFERENCIA where DESCRIPCION_DOMINIO = " & ClaseDem

ConsultaSql = "SELECT MAX(INTERNO_DOMINIO) FROM CAT_DOMINIO_REFERENCIA"
rst.Open ConsultaSql, Cnn, adOpenKeyset, adLockOptimistic

Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseServer
.Open Source:=QuerySql, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdText
If (.BOF And .EOF) Then
'No se encontraron coincidencias; añadir nuevo récord
.AddNew
!INTERNO_DOMINIO = rst.Fields(0).Value + 1
!DESCRIPCION_DOMINIO = ClaseDem
!PALABRA_CLAVE = ClaseDem
Else
'registro coincidente encontrado; continuar
End If
.Update
.Close
End With

Set rs = Nothing
cnn.Close
Set cnn = Nothing

Next i
End Sub

Here is a graphic example of what I want to do

The range of values ​​highlighted in Excel must be validated if they exist in the Access table enter image description here

enter image description here

If there is no match in the search QuerySql = "Select* from CAT_DOMINIO_REFERENCIA where DESCRIPCION_DOMINIO = " & ClaseDem, they must be added, remaining in this way enter image description here

**Note: It is possible that in the same macro Updaterecords associated with those already added to another table with a name CAT_CATALOGOrelated to table CAT_DOMINIO_REFERENCIA? are added, the table CAT_CATALOGOhas 2 fields named INTERNO_TABLA_REFERENCIA, INTERNO_DOMAIN; Below I show how it should look if possible enter image description here

Greetings community, I hope you can help me with this.

excel

1 Answers

  1. Best Answer

    I'm working on this macro but I still can't get it to work

    That description is vague, imprecise and confusing. Please, next time give more details, what kind of error, what do you expect me to do and what part fails. I understand that adding new records fails.

    I have greatly simplified the code. I access through DAO taking advantage of the fact that you use VBA and have Access installed, I take advantage of the Access tools themselves instead of accessing through ADO like you.

    My code connects to the database and counts, using DCOUNT if the record exists based on your criteria. If it doesn't exist, then it inserts it using SQL.

    Nothing more.

    Here are a couple of links just in case:

    Application.DCount Method (Access)

    SQL INSERT INTO Statement

    I have replicated your database in a simple way. My data is:

    enter image description here

    And my excel:

    enter image description here

    The two yellow rows are the ones that don't exist in the base and therefore I want them to be added automatically. The code:

    Sub TEST()
Dim i As Long
Dim UF As Long
Dim AppAccess As Variant
Dim MiSQL As String
Dim Ruta As String
Dim ClaseDem As String
Dim TotalRegistros As Long

Ruta = "D:\Temp\test.accdb" 'ruta a la base

Set AppAccess = CreateObject("Access.Application")

AppAccess.OpenCurrentDatabase Ruta, False 'abrimos base
DoEvents

With Sheets("DEM")
UF = .Range("D" & .Rows.Count).End(xlUp).Row
End With


For i = 3 To UF Step 1
    ClaseDem = Sheets("DEM").Range("D" & i).Value
    'primero comprobamos si ese valor existe en la tabla CAT_DOMINIO_REFERENCIA
    If AppAccess.DCount("[DESCRIPCION_DOMINIO]", "CAT_DOMINIO_REFERENCIA", "[DESCRIPCION_DOMINIO]='" & ClaseDem & "'") = 0 Then
        'si la cuenta de Dcount devuelve 0, significa que no existe ese valor en el campo DESCRIPCIÓN DOMINIO de la tabla CAT_DOMINIO_REFERENCIA
        'entonces hacemos un INSERT INTO mediante inyección SQL
        TotalRegistros = AppAccess.DCount("[DESCRIPCION_DOMINIO]", "CAT_DOMINIO_REFERENCIA") + 1 'total registros de la tabla para el campo INTERNO_DOMINIO
        
        MiSQL = "INSERT INTO CAT_DOMINIO_REFERENCIA (INTERNO_DOMINIO, DESCRIPCION_DOMINIO, PALABRA_CLAVE) VALUES (" & TotalRegistros & ", '" & ClaseDem & "', '" & ClaseDem & "')"
        With AppAccess
            .DoCmd.SetWarnings False 'DESACTIVAMOS AVISO VISUAL
            .DoCmd.RunSQL MiSQL 'insertamos mediante SQL
            .DoCmd.SetWarnings True 'reactivamos avisos visuales
            DoEvents
        End With
    Else
        ''registro coincidente encontrado; continuar. NO HACEMOS NADA
    End If
Next i

'cerramos base una vez ternimenos y limpiamos variables

AppAccess.CloseCurrentDatabase
Set AppAccess = Nothing

End Sub

    And the output I get once executed:

    enter image description here

    Regarding the 2nd question, is it possible that in the same Update macro, records associated with those already added to another table named CAT_CATALOGO related to the CAT_DOMINIO_REFERENCIA table are added? I recommend you open another question with that. Asking many things in one question is not correct.

    • 6