What is the name of the violating unique index constraint in dao / ms-access

Question

hello everyone

I am trying to insert a record into a table with DAO (within MS-Access) and doing so, I receive an Error 3022 (which indicates that a unique index is violated). The error is correct since in fact the tried-to-insert record has a value which is already found in the table.

Now, I'd like to find out the name of the violated unique index. Does someone have a clue how I'd get this?

Thanks for any pointer René

Answer 1

Here are some notes:

Sub WithADO()
''Reference: Microsoft ADO Ext x.x For DLL and Security
Dim catTables As ADOX.Catalog
Dim cn As ADODB.Connection
Dim ndx As Object

Set catTables = CreateObject("ADOX.Catalog")

Set cn = CreateObject("ADODB.Connection")
dbfile = "C:\Docs\LTD.mdb"

    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & dbfile & ";"

Set catTables.ActiveConnection = cn

For Each ndx In catTables.Tables("Table1").Indexes
    strlist = ndx.Name & " " & ndx.Properties("Primary Key") & vbCrLf & strlist
Next
MsgBox strlist

Set catTables = Nothing

End Sub

Sub WithDAO()
''Reference: Microsoft DAO x.x Object Library
Dim db As DAO.Database
Dim tdf As TableDef
Dim ndx As Object

Set db = CurrentDb
Set tdf = db.TableDefs("Table1")

For Each ndx In tdf.Indexes

    If ndx.Primary = True Then
        MsgBox ndx.Name
    End If
Next
End Sub

You could also use schemas.

Answer 2

Hello all

Thanks for your answers. But I have a different problem. I do not need all index/constraint names, but only the offending one.

Here's some pseudo code to demonstrate my problem:

...

dim rs as dao.recordSet
set rs = db.openRecordSet("aTable", dbOpentable)

rs.addNew
   rs("aField"        ) = 10
   rs("anotherField"  ) = "foo"
   rs("differentField") = "BAR"
  .... more fields ... more values
rs.update

...

now, DAO fires a 3022 at the rs.update line because one of the values violates one of the unique indexes on the table ("aTable"). So, I'd like to find out which index it is/was that fired the error.

Hope I could clarify.

René