Solved Automated search for Database Corruption Assistance

[Pat Hartman]

And I thought you were talking about linking the BE. The BE would never be in a \username folder. I'm really confused.

CurrrentProject.Path is whatever you opened the database from

98 messages is way too many to read through to find out what you are actually talking about. At least you solved some problem. You might want to mark this thread as "solved" and move on.

 

[Marshall Brooks]

@Pat Hartman:
Last week I added code to only allow the front end to open from the user's Desktop, the U:\ Drive, or one of my documents folders.

That worked fine, except as I said, the users could map any folder to U:\ and open the database from there. (Most of them would not know how to.)

I changed the code to only allow opening from "\\Network_Name\Folder\<UserName>" and the database would open from there, but not from the U:\drive.

I added a function to determine what network U:\ was mapped to and allow the front end to open from U: if it was mapped to the correct network name.
So now it will open from U: if and only if U: is mapped to the correct folder.

But some users had a fully-qualified path - i.e. "\\Network_Name.domain.com\Folder\<UserName>" so I either had to change the drive mapping for them or change the front end to account for any possible domain/subdomain mapping.

98 messages is way too many to read through to find out what you are actually talking about

At least this forum is linear, so I can't reply to post #23 and there is a new message on Page 2.

 

[Marshall Brooks]

Just want to add one quick item.

I said earlier that the PrimaryKey was duplicated in some cases and I thought this was a sure sign of corruption as I didn't think Access would allow a duplicate PrimaryKey.

I'm not sure it will, but what I had was a table with an autonumber field NAMED PrimaryKey with a value of "Indexed - Duplicates Allowed", but it was not SET as a PrimaryKey.

I think I'm going to check each table in the backend and rebuild the PrimaryKey field and set it as PK and no duplicates and Indexed.

 

[isladogs]

Sounds like someone didn't know what they were doing when they created the table.
PrimaryKey is a bad name for a field. Surprised its allowed

By definition, PK fields cannot have duplicates.
Although I've heard of this happening, I've never experienced it in over 25 years of using Access

 

[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.

 

[Pat Hartman]

Just for my knowledge, could you explain WHY it is a poor choice of a field name? (Other than obviously naming it "Primary Key"

Because in the context of DDL - which is the query language that manages the definition of objects (DML - data manipulation language - is the sql that we all know and love that is used in our queries that work with data), PRIMARY is used as a reserved word. The following is the DDL to create a table using a query. Notice the use of the word PRIMARY when the PK is defined. In this case the actual PK name is DocID and the syntax encases it in square brackets. I don't know that using Primary as the name of a PK would cause a problem, but it is best to just avoid reserved words. I can tell you for a fact and even post an example database, that shows how the use of "Date" and "Name" as column names actually causes issues with code but the code does not raise an error. You just may not get the result you thought you would get. For example, is Me.Name a reference to the column named [Name]? You need to see this for yourself to see the types of problems using reserved words can cause.

CREATE TABLE [dbo].[tblDocuments](
    [DocID] [int] IDENTITY(1,1) NOT NULL,
    [DocType] [nvarchar](50) NOT NULL,
    [DocNum] [nvarchar](50) NULL,
    [DocDescription] [nvarchar](50) NULL,
    [DependentInfo] [bit] NOT NULL,
    [OutBoundEnvelope] [int] NULL,
    [InBoundEnvelope] [int] NULL,
    [ActiveFlg] [bit] NOT NULL,
    [EnvelopeOnly] [bit] NOT NULL,
    [UpdatedBy] [nvarchar](20) NULL,
    [UpdatedDT] [datetime] NOT NULL,
    [upsize_ts] [timestamp] NULL,
CONSTRAINT [aaaaatblDocuments_PK] PRIMARY KEY NONCLUSTERED
(
    [DocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

 

[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?

 

[Pat Hartman]

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 don't know in this specific case whether or not, you will run afoul of any database engine. Maybe it works if you are using ACE but if you convert to SQL Server or Oracle it breaks. Then what? The further down the road you are when you discover the issue, the worse the fix will be. If an expert tells you to specifically NOT do something and can explain why and point to documentation to support the position, then why would you argue? Do you know for a fact that you are not going to ever run into a problem if you persist in using reserved words? Desc and Description are another problem. Access (Jet/ACE) doesn't like one of them and SQL Server doesn't like the other.

Just because "you" don't "see" the issue doesn't mean that it isn't real.

Take a look at this sample database to see in action the bad effects of using some common reserved words.

Useful code AND the effects of using bad column names

This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...

www.access-programmers.co.uk

 

[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.