Menu
I support users who use MS Access as a front-end for viewing some Oracle tables at our intstitution. Their typical use is to write ad-hoc queries with the Access querybuilder, and also they LOVE Access because it lets them build printable reports without having to submit to the lengthy & expensive programming-request process through our tech support department.
When creating a link to an external table, they are sometimes asked to identify the primary key of the table. Of course, how can they possibly have any idea what the primary key field(s) would be?
My question is this: Why does Access want to know what the linked table's primary key field(s) is? Indexing should be happening in the source db, shouldn't it?
We had an issue recently when users started moving to Access 2007 that was finally "fixable" in two ways: 1) new ODBC connection, or 2) identifying a different primary key column for the linked table. My tech support tried to insist that I would have to modify my practice by telling my users to select the correct primary key field. I contended that it shouldn't matter. But I can't dismiss the thought that MS Access wouldn't be asking if it didn't matter for something. Just not sure what that might be.
Note: the "issue" is described in this post: Access 2007 to Oracle 10g linked table -- query with flawed results, but no errors thrown
290
Primary key allows you to create a unique identifier for each row in your table. It is important because it helps you link your table to other tables (relationships) using primary key as links.
Key FieldsA table must have exactly one primary key to qualify as relational, but that key can be composed of multiple columns. A foreign key, by contrast, is one or more fields or columns that corresponds to the primary key of another table. Foreign keys are what make it possible to join tables to each other.
A primary key is a special relational database table column (or combination of columns) designated to uniquely identify each table record. A primary key is used as a unique identifier to quickly parse data within the table. A table cannot have more than one primary key.
If your users just need read-only access, for querying and reporting, have them click cancel in response to Access' question about which field(s) to use as primary key. They should still be able to create a link but it will be read-only.
If your users need to modify data in the linked Oracle tables, give them copies of an Access database file which you create for them. You set up the linked tables, and the connection properties are stored in the link's TableDef ... Access doesn't need to refer back to your DSN for that link afterward. So the users won't need the DSN, either.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
