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?
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
If s <> "" Then MsgBox s
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.
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.
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.
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.