[RESOLVED] ado check if a primary key exists in access table

[newbie2]

Hello
This is how I'm removing the primary key

DB.Execute "DROP INDEX PRIMARYKEY  ON [tbl_exam]"

But this is giving an error once the key is removed.
Thus I want to check if a primary key exists to avoid the error.
thanks

[Elroy]

This is how I do it for the DAO. I suspect it'll work the same way for the ADO:

Private Function DbIndexExists(sTableName As String, sIndexName As String) As Boolean
    ' The table should exist.  If it doesn't, it returns FALSE.
    Dim s As String
    '
    On Error Resume Next
        s = dbTheDatabase.TableDefs(sTableName).Indexes(sIndexName).Name
        DbIndexExists = (Err = 0)
    On Error GoTo 0
End Function

Good Luck,
Elroy

EDIT1: That dbTheDatabase is a Database variable, and the database is already open. Just FYI.

[newbie2]

thanks sir
I tried the function

If DbIndexExists(tbl_exam, ID) = true then

but it gives error

[Elroy]

You need to either modify it to pass in YOUR database variable, or rename that dbTheDatabase variable to DB (which it looks like you're using).

Also, I assume that tbl_exam and ID are strings with your table and index name in them? If not, they need to be.

EDIT1: Also, I fear you're not using Option Explicit. If not, please do.

EDIT2: I'm taking shots in the dark here, but you might try something like:

	If DbIndexExists("tbl_exam", "ID") = true then

[Schmidt]

You need to either modify it to pass in YOUR database variable, or rename that dbTheDatabase variable to DB (which it looks like you're using).

... I'm taking shots in the dark here, ...

You already did "take shots" by posting your original function -
because ADO does not have "a DBVariable" in the same sense as DAO supports them.

ADO has a "Connection-Object" at the root of things -
and the usual way to use that to get to a given DBs Indexes is:
Cnn.OpenSchema(adSchemaIndexes, ...) '<- returning an ADO-Recordset, which you can filter etc.

@the OP
Deleting a Primary Key (Index) from a table is actually a thing, I've never seen used in any example, ever.
Why do you do that - what's the purpose?

Olaf

[Elroy]

Olaf,

Maybe it's just me (but I don't think so). Olaf, why are you always so rude?

I tried to be as up-front as I could be that I was primarily familiar with the DAO.

I suppose I'm glad you came along to help newbie2. I'll bow out now, and let you continue questioning why he's doing what he's asking about.

Good luck, newbie2,

Elroy

[Schmidt]

Maybe it's just me (but I don't think so). Olaf, why are you always so rude?

I tried to be as up-front as I could be that I was primarily familiar with the DAO.

I was not rude - I was stating a fact.

You *did* "take a shot" in posting your DAO-function (misleading the OP) -
altough *before* posting anything, you could simply have:

- Opened up a new VB6-IDE-session (0.2seconds)
- Check an ADO-reference in (2 seconds)
- Open the IDEs Object-Explorer (0.1) seconds
- Search for the String (or MethodName): TableDefs (in the appropriate SearchField)

"Nothing found" gets reported.

I suppose I'm glad you came along to help newbie2. I'll bow out now, and let you continue questioning why he's doing what he's asking about.

Do you mean, what the OP does there (deleting the PK-index from a table), is a "viable path to follow"?

If "no, not a viable path to follow" (as I do), why on earth do you not point that out to the Newbie in question?

We are here to help them to "become better" - not to "lead them astray".

Olaf

[dilettante]

Nothing like a guy with car trouble getting advice on shoeing a horse.

The checking could be done via schema rowsets or via ADOX.

Option Explicit

Private Connection As ADODB.Connection

Private Sub Command1_Click()
    Set Connection = New ADODB.Connection
    Connection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='Rent.mdb'"
    Command2.Enabled = True
    Command3.Enabled = True
End Sub

Private Sub Command2_Click()
    'You must close the table before you can delete it or remove an index from it.
    'Since we never opened a Recordset on this table we don't have it open.
    'Thus we can proceed.
    Connection.Execute "DROP INDEX [pk_ID] ON [Payments]", _
                       , _
                       adCmdText Or adExecuteNoRecords
End Sub

Private Sub Command3_Click()
    Dim Discard As String

    With CreateObject("ADOX.Catalog")
        Set .ActiveConnection = Connection
        On Error Resume Next
        Discard = .Tables![Payments].Indexes![pk_ID].Name
        If Err Then
            Label1.Caption = "No"
        Else
            Label1.Caption = "Yes"
        End If
    End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Connection.Close
End Sub

[ChrisE]

Hi newbie,

if you don't know the Index name you can use this.
You have to declare the Table

Private Sub Command1_Click()

IndizesShow

End Sub


Private Sub IndizesShow()

   Dim RsI As ADODB.Recordset
   Dim TblName As String
   Dim i As Long
   
      TblName = "Adressen"
      
      Set RsI = New ADODB.Recordset
      Set RsI = cn.OpenSchema(adSchemaIndexes)
      RsI.Filter = "Table_Name = '" & TblName & "'"
      
      Do While Not RsI.EOF
         List1.AddItem "IndexName : " & RsI.Fields("Index_Name").Value
         List1.AddItem "ColumnName: " & RsI.Fields("Column_Name").Value
         i = i + 1
         RsI.MoveNext
      Loop
      Set RsI = Nothing
End Sub

I hope you know what you are doing with deleting Indexes etc...

regards
Chris

[newbie2]

Thank you all for your interest

First, those who asked me why I want to remove the primary key,
This is because I want to allow duplication.

I tried all the approaches but i failed to check the existence of a primary key.
I think Elroy's function is exclusively for DAO.
dilettant's sample is very interesting but for strange reasons I failed to use it in my application.
Even in the attached sample, the code code fails to check the existence of the primary key If I remove the primary then add it again.
Thank you

[newbie2]

ChrisE 's code can retrieve Index name but I need to check if a column has a primary key.

[dilettante]

You may have a constraint to remove instead of any key or index. You might have to enumerate the rows in adSchemaCheckConstraints instead.

[ChrisE]

ChrisE 's code can retrieve Index name but I need to check if a column has a primary key.

add this line to the ...

 
Private Sub IndizesShow()

   Dim RsI As ADODB.Recordset
   Dim TblName As String
   Dim i As Long
   
      TblName = "Adressen"
      
      Set RsI = New ADODB.Recordset
      Set RsI = cn.OpenSchema(adSchemaIndexes)
      RsI.Filter = "Table_Name = '" & TblName & "'"
      
      Do While Not RsI.EOF
         List1.AddItem "IndexName : " & RsI.Fields("Index_Name").Value
         List1.AddItem "ColumnName: " & RsI.Fields("Column_Name").Value
         List1.AddItem "ColumnName: " & RsI.Fields("Primary_Key").Value

         i = i + 1
         RsI.MoveNext
      Loop
      Set RsI = Nothing
End Sub

regards
Chris

[IkkeEnGij]

chris did beat me to but anyway:
took me queit a while to look it up

the following lists the indexes of table clients, of the Northwind database (Access 2000 file format)
done in access 2003 sp3

Sub ListKeys()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = CurrentProject.Connection
    Set rs = cn.OpenSchema(adSchemaIndexes, Array(Empty, Empty, Empty, Empty, "Clients"))
    
    Do While Not rs.EOF
       Debug.Print rs.Fields("TABLE_NAME"), rs.Fields("COLUMN_NAME"), rs.Fields("INDEX_NAME"), rs.Fields("PRIMARY_KEY")
       rs.MoveNext
    Loop

    rs.Close
    cn.Close
    
    Set rs = Nothing
    Set cn = Nothing
End Sub

[IkkeEnGij]

First, those who asked me why I want to remove the primary key,
This is because I want to allow duplication.

duplication of what ?

[Schmidt]

duplication of what ?

It's what I thought his problem might be...
He wants to gain the ability to Insert Records into the table, which have the "same Primary Key" (which is of course causing a constraint-err-message)...

Now we only have to explain to the OP, why it is important to have (or keep existing) PKs on most tables -
and that he instead should consider solving the problem in a different way (usually over a secondary table -
or alternatively over a redesign of the original one (which should still maintain a PK).

Olaf

[dilettante]

Even if the constraint is removed from the table, there might be logic in the program (if any, he keeps saying "Access" so maybe we're talking about MS Access and not VB at all) that will defeat this.

The entire enterprise seems ill conceived.

[newbie2]

Thank you all
Solved

If RS.Fields("Primary_Key").Value = True Then DB.Execute "DROP INDEX PRIMARYKEY  ON [tbl_exam]"

[newbie2]

duplication of what ?

As far as I know the primary key is there to prevent duplication.
I have a numeric field "Id" with a primary key which prevents duplication of the same value.
Now I need to be able to insert the same value. So I wanted to drop the primary key.
Thanks to all of you, now I can insert the same value in the ID field.

[Schmidt]

Thanks to all of you, now I can insert the same value in the ID field.

Hmm, then at least make sure, to rename that column then, because "ID" is no longer a Name this Column deserves...

Besides - good luck with future Update-Statements on that table, because if you don't have an "ID"-Field anymore
(or a combination of other fields, which are unique across the table) - what "Where Clause" will you
choose and specify, to make "single-field-updates" on an already existing single-Record in that table?

Olaf

[newbie2]

Schmidt
Excuse me sir I couldn't grasp the idea
Do you mean that "ID" is a reserved word?
I feel like I'm doindg something wrong by removing the the primary key.
Let me clarify the situation.
My tbl_exam had a primary key since a student was supposed to take only one exam.
But later, another exam was added in the school system.
So the foreign key should be removed to allow duplication in the ID field.
My query is as follows:

sSQL = "SELECT * FROM tbl_exam WHERE " & _
           "ID=" & Main_Frm.TxtID  & _
           " AND " & _
           "Date_exam = #" & Format(DTPicker1.Value, "mm/dd/yyyy") & "#"
    RS.Open sSQL, DB, adOpenForwardOnly

Thank you

[Schmidt]

Do you mean that "ID" is a reserved word?

It is not a "reserved word", but a Column-Name with a "strong meaning" ("ID" = "identifier" - usually a unique (PK) identifier of a single record in a given table).

I feel like I'm doindg something wrong by removing the the primary key.

You do - and it was pointed out to you, quite early in this thread.

Let me clarify the situation.
My tbl_exam had a primary key since a student was supposed to take only one exam.
But later, another exam was added in the school system.

In that case you should have redesigned your DB-Schema, by adding a "third" table (tbl_StudentsExams) -
accompanied by a bit of "refactoring" on the already existing tables "tbl_Students" and "tbl_Exams".

Here a rough layout-suggestion:

tbl_Students: (used to describe only a "Student-Entity" - the Person in question)
- ID Integer Primary Key
- Name Text
- Birthday DateTime
- ...

tbl_Exams: (used to describe only an "Exam-Entity" - the current Name and description as e.g. "Math 2017, (regular exam)" - or another e.g. as "Math 2017, (second chance for strugglers)")
- ID Integer Primary Key
- Name Text
- Description Text
- ExamDate DateTime
- MaxAchievablePoints Integer
- ...

tbl_StudentsExams: (used to describe which Student, took what Exam - identified over their IDs - accompanied with a few Result-Columns perhaps)
- Students_ID Integer
- Exams_ID Integer
- PointsAchieved Integer
- ...
The above Table-Def having a combined PK which is unique across two fields: Sutdents_ID and Exams_ID

A re-layouting in a similar way as shown above, is the only way, to solve your problem decently (with all the consequences it involves "down the line"
(as in "adapting your VB-Code to the new structure", which you could have established in the DB already in the first place, if you'd asked for it in the forum)

HTH

Olaf

[newbie2]

Schmidt thanks a lot for your interest
Your layout is very original and very clean.
I wish I had used this layout but unfortunately I can't make use of it because using such layout will lead to the loss of data that was already saved.
Moreover the student table is linked with two other child tables. Thus I don't want to overload it with more foreign keys.
I hope you understand my worry.
thanks

[Schmidt]

Your layout is very original and very clean.

Nah, not at all - it is "entirely normal" (as in "normalized" - please google for [DB Schema Normalization]).

I can't make use of it because using such layout will lead to the loss of data that was already saved.

I highly doubt that, because you will only have to introduce that additional third table, and fill it with the related IDs of your other two tables.

Moreover the student table is linked with two other child tables. Thus I don't want to overload it with more foreign keys.

There will *not* be any new keys necessary in your students-table -
as said - you only need to introduce a new table StudentsExams and fill it properly (using max. 10 lines of code in a little VB6-loop).

Olaf