May 2 2012, 09:02 AM
Yesterday i started having issues pop up with the database that has been up and running since 2010. The last update i have done to forms was 3/22/2012. But the issues seem to be related to lost primary indexies. What i mean by that is that when trouble shooting i noted that tables that once had a primary index key, no longer showed they were indexed. I double checked my self by opening up my last backup and in the back up 02/2012 it was indeed indexed. I was able to fix one of the problems in forms by merely resetting the tables to their primary index keys. But another one of my forms, is still having issues. My question here is does this "just happen"? or Do i have to start wondering about someone possibly sabatoging this db?
The second form i am having trouble with has 3 tables. The first has "id" as the primary index key with BLN as indexed(duplicates ok). The second table's primary index key is "bln" and the third table's primary index key is "index" with "bln" indexed(duplicates ok). I have heard something about the "id" or "index" should not be used as a field name. But, which one is it?
May 2 2012, 09:38 AM
Neither should be the name of a field, IMO. Some people do retain the "ID" default name for the Primary Key field of a table. I find it a bit confusing, but technically it's okay as long as you fully qualify it when using it in VBA or in SQL. "Index", of course, is a reserved word and should never be used for anything else.
You also need to be clear about the difference between a "Primary Key" and an "index". All Primary Keys are also indexed. However, not all indexes are "primary" (I'm not even sure what that would mean). You add indexes to fields that are frequently used in filtering and sorting operations.
If, in the BACK END, your tables have lost their Primary Key and Foreign Key designations, you do have a problem. That can happen when a table has been corrupted and then a Compact and Repair is run on that database file. Fortunately it's rare.
In the FRONT END, when you use a table in a query, or as part of a record source SQL statement in a form, you may not see them. What you should do is relink all of the tables, after verifying that the Primary and Foreign Keys are properly defined in the Back End. Then, recreate the query or SQL Statement in the Form's recordsource. Access should recognize and identify the PK and FK in the Query Designer.
May 2 2012, 09:43 AM
In addition to what George has said, you may want to look at this http://www.UtterAccess.com/forum/ACCGenera...ys-t637301.html
May 2 2012, 10:24 AM
Thank you Guys for the support and corrections in my erroneous ways.
I went back and took a deeper look at the corrupted tables.
I first tried linking the 3 tables back together in the corrupted be and it still had issues.
I tried linking the same tables again on the back be and it worked.
I looked closer at the 3 tables and found the "bln" was indeed unique in the first table and "duplicates were not ok"
After resetting that table, there was an error which told me that there were duplicate BLN that i needed to fix.
Then after resolving that issue, i tried the linking the 3 tables again and was successful.
Re-tested the FE and the issue was indeed resolved.