Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2001
    Posts
    4

    Unanswered: Query for primary keys in Access

    The MDAC ODBC driver for access does not implement the SQLPrimaryKeys function. Does any know where in Access 2k the primary key information is stored? I have searched all of the system/hidden tables, nothing. The only thing I can think of are a couple of binary fields in the system tables. I am looking for a way to issue a query that will return the primary key information given the table name/objectid. In MS SQL server you have access to this information through the system tables. It's not a big deal writing a query to do this. How do you write a query in Access to do the same thing, find the primary keys?

    Thanks,
    Jason Jennings
    jasonj@equitrac.com

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Here is some VBA code you can use to obtain info on indexes in your mdb file.

    Dim o1 As DAO.TableDef
    Dim i1 As DAO.Index
    Dim s As String

    s = ""
    For Each o1 In CurrentDb.TableDefs
    If Left(o1.Name, 4) <> "Msys" Then
    ' no system tables
    For Each i1 In o1.Indexes
    s = s & "Table: " & o1.Name & IIf(i1.Primary, ", [PK],", ", ") & "Index Name: " & _
    i1.Name & ", Fields: " & i1.Fields & vbCrLf
    Next
    End If
    Next
    If s <> "" Then MsgBox s

    Igor

  3. #3
    Join Date
    Dec 2001
    Posts
    4
    Thanks for the code, but... We are developing in C++ and not using the MS ADO technology. I need either a SQL blah function from the ODBC driver or a solid query. SQLPrimaryKeys is not implemented in the MDAC Access driver.


    Jason Jennings
    jasonj@equitrac.com

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Hi,

    The code I posted uses DAO technology (Jet Engine). Even if your are coding in C++, you can always modify posted code to use Automation.

    HTH

  5. #5
    Join Date
    Dec 2001
    Posts
    4
    True. Our goal is to remain DB independent and OS independent. Currently our DB goals are Oracle, MS SQL 2000, Access 2000, and MySQL. There is a chance we may have to support our code under linux. We don't want to corner ourselves in the MS technology until it is too late. We use a table to list all of the primary keys for the tables. This works fune until someone changes the keys and forgets to update the table. If selecting the pks from the sys tables in access is near mpossible, then we may just stick with our pk table as a band-aide.

    Jason Jennings
    jasonj@equitrac.com

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Access does not provide much in the way of binary tools, so reading the primary keys from that Large Binary Object in the MSysObjects is impossible.

    I would do this:

    It appears you are using Access as a backend. I would add to that backend a function which would create, FROM SCRATCH a table containing your table names and primary keys. I would then refer to that table for your information.

    In other words, make it so that when your developer's opens this mdb it deletes this description table. Now the database is essentially useless unless the developer "rebuilds" the table. You can easily have a macro which runs a function which does this automatically. This methodology would give you a bit more insurance as to maintaining this table.

    Just an idea.

  7. #7
    Join Date
    Dec 2001
    Posts
    4
    I thought the binary field in the MSysObjects held the pk information. Basically we use a table to manage the primary keys. All db access runs through our layer, so we can code all day to protect the db and its' pks. Thank you for the advice. Access is our bottom line product solution.

    Thanks
    Jason

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •