Solved Automated search for Database Corruption Assistance

[Marshall Brooks]

Thanks Colin. I think I was the one who didn't know what they were doing.

I think what happened was at one point we were looking into converting the BE to SQL and the SQL Migration assistant said every table had to have a autonumber Primary Key field. So I created the field, but I didn't know to SET it as Primary Key.

The name seems to work. The term is "Primary Key", so I'm not surprised PrimaryKey is allowed. I'm a bit surprised "Primary Key" is also allowed. But I have some front end code that relies on the field name, so I don't want to rename it.

 

[isladogs]

Just checked: Primary IS a reserved word but not Primary Key (with or without a space).
Nevertheless a very poor choice of field name

 

[Marshall Brooks]

Just for my knowledge, could you explain WHY it is a poor choice of a field name? (Other than obviously naming it "Primary Key" implies that it IS the PK, but as long as I set it up so that it was ...)

 

[Gasman]

Thanks Colin. I think I was the one who didn't know what they were doing.

I think what happened was at one point we were looking into converting the BE to SQL and the SQL Migration assistant said every table had to have a autonumber Primary Key field. So I created the field, but I didn't know to SET it as Primary Key.

The name seems to work. The term is "Primary Key", so I'm not surprised PrimaryKey is allowed. I'm a bit surprised "Primary Key" is also allowed. But I have some front end code that relies on the field name, so I don't want to rename it.

That might not be you.
I just discovered a key called the same in a table of my Diabetes DB just the other day. It was a table for dates, that I use with code from @arnelgp to generate a dates table. However mine is unique.
However it is not a name I would ever use, so unless I copied the file from arnelgp's DB I do not know.

 

[Marshall Brooks]

@Gasman - Thanks, but I'm pretty sure it WAS me in this case.

What I'm not understanding is what makes it a bad field name assuming it actually WAS a PK field.

 

[CJ_London]

There was a recent case in this forum of using the reserved word ‘order’ causing all sorts of misleading error messages

 

[Gasman]

@Gasman - Thanks, but I'm pretty sure it WAS me in this case.

What I'm not understanding is what makes it a bad field name assuming it actually WAS a PK field.

Not really descriptive I would say?
Like calling all the autonumber fields ID

And if it is not the PK, then downright misleading.

 

[Marshall Brooks]

I'm more inline with Pat, Colin, and CJ London - not good practice using a reserved word in the description.

I don't see a problem with it or a field named ID if the field is only used as an autonumber, which it basically is in this case.

I agree, having a field named PrimaryKey that is NOT the PK was poor coding, but as I said, I think at the time I just got a message that every table must have a PrimaryKey field, and I misunderstood what it meant.

I'm not saying I'll NEVER rename that field (although I'm not sure what I'd call it - probably PK), but I have bigger fish to fry - (like making sure each table has a DEFINED PK field).

Renaming it would require updating the front end and the backend simultaneously, and being careful I didn't miss any locations.

 

[MajP]

What I'm not understanding is what makes it a bad field name assuming it actually WAS a PK field

It is not "invalid" but It is so out of the norm it would confuse most people. You may say the only person ever looking at the DB is you, until you come here looking for help. The majority of people would name the primary key fields something like
EmployeeID
Employee_ID

You can call fields in different tables the same name, but IMO causes a lot of confusion and extra work. A bunch of tables with a field called ID is extra work when you join these fields and have to decipher it and fully identify it.

Imagine you have a pet dog and you name it "Kitty". You are outside looking for your dog yelling "Here Kitty, Kitty." Guaranteed you will confuse the crap out of your neighbors and not get a lot of useful help finding Kitty. (But I have known people who have done this)

If I read a piece of posted code and it says
txtBoxUName.Rowsource = ''something"
I am going to get into a multiples posts trying to figure out what the hell is going on, only to learn that it in fact a combobox and it stores a User ID.

Names should be descriptive so that you and others can come back and decipher what is going on. If I was to see code or sql with the word "Primary Key" I would assume that is some kind of pseudo code or place holder.

 

[isladogs]

On that very subject, my first cat was called Dragon. That certainly confused everyone when I called Here Dragon to bring him in for his food

 

[CJ_London]

Just as an aside. When you create a primary key field access automatically creates a no duplicates index called - PrimaryKey

 

[Marshall Brooks]

Just as an aside. When you create a primary key field access automatically creates a no duplicates index called - PrimaryKey

THAT in my opinion is a valid reason to rename it. So I have an index with the same name as the field name, which isn't a problem except in other tables, I would have an index with the same name related to a field with a completely different name.

 

[Gasman]

Are we able to set a certain index in Access as you could in Foxpro? or are they just automatically used when needed?

 

[ebs17]

The Indexes listing is always table-bound, so identical names are not a problem. Also, indexes are simply used by the system, never explicitly invoked by a call.
An index is used if the SQL optimizer provides for it in its show plan, i.e. only indirectly.
The SQL optimizer can be influenced via your own query design. In the good case, the query design is already optimal, and the SQL optimizer follows it.

 

[Marshall Brooks]

To clarify - I'm not sure it comes across this way, but I'm not arguing, I'm prioritizing.

I'm not saying "PrimaryKey" is working fine as a field name and I'm going to use it in all my tables and in every new database I work on.

I'm saying the database has possible corruption and no PK defined on many of the tables. I'm going to fix that and some pseudo security issues like blocking the navigation pane and the ribbon.

After that, I will look at things like spaces in table and field names and fields with somewhat reserved words in the names.

And yes, I also get your comment on the longer I wait, the worse it will be. That's somewhat why I can't fix it immediately. If I only named the field PrimaryKey and never used it, I would just have to get exclusive access to the backend and change the field name. I don't reference the field often, but I know it is used in 2 or 3 queries per table, in 3 or 4 VBA subroutines, etc. (And - to your point - if I wait I may create a new VBA subroutine and need to reference that field, which creates another place I need to remember to change when I fix it.)

 

[ebs17]

Are you sure about that?

Quite. In the specifications, the maximum number of indexes refers to one table at a time. The automatic naming of the indexes when they are created by the table design includes the names of the fields used.
If you name fields in different tables the same (foreign key, timestamp and some other things), nothing breaks down. The qualified use is carried out with the table name (or table alias) anyway.

Relationships would be a different topic as they connect two tables and thus have a scope in the whole backend and not just in one table.

 

[Marshall Brooks]

I have less questions than I expected, but I'll get to those next:

The backend was rebuilt from text files and all tables (except one) now have a primary key defined. Thanks to all - especially @Pat Hartman.

Everything went smoothly. I expected to come into a bunch of error messages today, but everything seems to be working fine.

Next step is securing the interface. Plans:

I'm relying fairly heavily on @isladogs' CAI Form: https://www.isladogs.co.uk/control-application-interface/index.html

• I want to hide the navigation pane. I don't want to use a typical "SwitchBoard" Pane, but I want something similar to Colin's frmNavigation Pane. Not only does this hide my tables and queries, but it also fixes an annoyance with the default Nav Pane - i.e. with multiple forms enabled: Open FrmA. Open FrmB. Click back to FrmA - the Navigation Pane will still have FrmB highlighted.
• I want to hide the toolbars.
• I want to disable the Shift-Key Startup bypass.

More questions and issues in separate replies below:

 

[Marshall Brooks]

This is something I should have foreseen, but it caught me by surprise.

2/3'rd of my tables are copied locally from the backend to the frontend. The existing table contents are cleared and then the data is copied from the backend tables.

All of the tables that had a PrimaryKey field now have that defined as the Primary Key.

One table did not have a Primary Key so I added a field for it and then I got an error when it tried to import the table into the local front end table.

I'm thinking this is one of those situations where I need to add the field to the front end, add the field to the back end, and then release both the front end and back end co-ordinated at the same time so that the two versions are in synch.

The table that doesn't have a PK is the table with the FE version number and the location of the master copy of the front end. It only has one record.

For now, I took the PK field back out, but I don't know if there is any point in trying to add it.

 

[Marshall Brooks]

With the new navigation pane, our database will work better with tabbed windows and no tab labels. I found this thread https://www.access-programmers.co.uk/forums/threads/default-overlapping-forms.307143/ but it isn't entirely clear what controls this (embedded in the file or registry key).

Basic question: If I set up my front end as tabbed windows, will it retain that setting when users download it? (I don't usually have a problem with sending an E-mail saying "The database works better with tabbed Windows and set it from File Options - Current Database", but if I am hiding the toolbar …)

 

[Marshall Brooks]

Basic question of Nav Pane replacement:

Colin says to disable AFR on the subforms and during development. Automatic Form Resizing?

How do I set the form up so that the form is on the left and the main form that I want to display and work on is to the right of the main form?