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
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
**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
Greetings community, I hope you can help me with this.
