Retrieving primary key names from Access 2003


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Retrieving primary key names from Access 2003

  1. #1
    Join Date
    Jul 2006
    Posts
    1

    Retrieving primary key names from Access 2003

    Does anyone know how Access 2003 stores the names of the primary keys of a database? I'm using an ODBC connection, but the current driver I have for Access (4.00.6304.00) doesn't support the function SQLPrimaryKeys(). I looked in the system tables, but I can't find the names. I've searched this board but can't seem to find someone who has had a similar problem.

    I want to know the names because I want to be able to build queries to drop an existing primary key from a given table.
    Last edited by aleksander3; 07-18-2006 at 03:34 PM.

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    I don't know whether you are using DAO or ADO but one method is to use ADOX (Microsoft ADO Ext 2.x for DDL and Security):

    Code:
    Dim catADOX As New ADOX.Catalog
    
    catADOX.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                         "Data Source=C:\Test Files\db1 XP.mdb;"
    
    For Each keyADOX In catADOX.Tables("Table1").Keys
        If keyADOX.type = adKeyPrimary Then
            Debug.Print keyADOX.Name
            Exit For
        End If
    Next keyADOX
    
    catADOX.ActiveConnection.Close
    
    Set catADOX = Nothing
    Set keyADOX = Nothing
    OpenSchema would be another option.

    Personally I can't recommend using ODBC with a Jet database since it's not as stable as Jet OLEDB and is lacking in features.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

Similar Threads

  1. Displaying the access key assignments
    By Sunil Menon in forum Web
    Replies: 1
    Last Post: 07-26-2002, 09:00 PM
  2. Access to the registry key is denied.
    By Prabhakar in forum ASP.NET
    Replies: 0
    Last Post: 04-29-2002, 06:34 PM
  3. Access 97 to 2000 Conversion Error
    By Michelle in forum VB Classic
    Replies: 8
    Last Post: 04-19-2002, 05:01 PM
  4. Create Constraint Primary Key
    By M Gunawan in forum VB Classic
    Replies: 1
    Last Post: 06-13-2001, 09:48 PM
  5. Primary Key Indexes
    By Don G in forum VB Classic
    Replies: 4
    Last Post: 04-12-2001, 05:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center