I have a table in Access that uses one column as it's
Primary Key.  I would like to change it so that it uses a
combination of 2 columns as it's Primary Key.  

When you create a table in Access, you can create a table with multiple
columns as it's primary key, of which some users do this with tables that
deals with Many to Many relationships.  One such example is that items are
purchased from time to time, so just one item may have a history of many
POs, but at the same time, each Purchase Order may have many items on it, so
to resolve this, one would have an item table with the basic info for the
item, a PO header table that has basic info for the PO, and then another
table that has both the Item number and the PO number with other information
relavent to that particular item and PO.

Now one of 2 things could be done with this many to many table, either a
unique ID field could be created (which is normally highly suggested such as
in this case), or both, the Item number and the PO number fields get
selected, then the Primary Key toolbar button is clicked on (What you are
looking for from what I could tell).  By doing this, this allows for
duplicate Item numbers and PO numbers, but it does not allow for any 2
records to have the exact same Item number and PO number.  Example, no 2
records can contain the Item number of 333333 and PO number of 666666.

I don't normally do this type of task in VB environment cause this is more
or less a design issue normally speaking, so when I have a change like this,
I just go to the table and make the change.  Now if there are several tables
that needs similar adjustments, that might be one of those exceptions that I
would make to not using VBA for design type issues.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

You would have to do a

  ALTER TABLE MyTable
  DROP CONSTRAINT PrimaryKey

and then another

  ALTER TABLE MyTable
  ADD CONSTRAINT idxNewPrimary PRIMARY KEY (OneField, TwoField);

Mind you, you'd first have to drop all the relationships that refer to this
key. You'd also have to de-duplicate the data in the two fields, and make
sure that there are no nulls. Next you'd have to reconfigure all the tables
that refer to this key to make sure that both parts of the key are
available and correct. Then you need to rewrite all the queries that use
this table, oh and all the queries that refer to the related tables you had
to fix too. Then you can create the new primary key. Then you can recreate
all the dependent relationships. Now test all the joins. And the queries.
And redesign the forms, and the reports, and the export queries. And all
the applications that handle the export queries. And retrain the users in
the new forms. And remind the customers that their reports are going to
look a bit different.

You did remember to back up first, didn't you?

Ronald is not joking when he says that the choice of PK is a pretty central
part of the design, and there is almost no reason to want to do this
programmatically: it's very much a one-off affair which is why Access gave
us the Table Design window.

Best wishes

Tim F

I didn't even realize there was so much stuff that had to be redone, but
then I guess that's one such reason why I don't like to use too much of the
automation stuff in Access.  All of this rework makes me think of the issues
that I had with creating tables in Access 2.0.  That issue in Access 2.0 was
so bad, if you even made one mistake, you couldn't just correct and move on,
but rather you had to delete the entire table and then start from scratch
again.

However, this type of situation does bring out one very good point that
needs to be taken right from the beginning of any development project.  For
anything that's being developed, you must plan out your development before
you get started.  Does this mean you have to have every little code planned
before developing?  No, but you do at least need to have general guidelines
planned out and not only that, but try to think about the various possible
issues that you as the developer may run into.  For the project that I'm
working on as we speak, I spent at least a good solid 2 months planning
before I even touched any code for the project.  This 2 months just mainly
dealt with having to setup general guidelines of the purpose of the program,
how it was going to address the different issues, what sort of things are
going to have to be dealt with, and whatever else may come at my way.  As I
got started with the coding, some other issues started popping up at me, and
I'm still having to address those other issues

I still use the Primary key in Access, but I don't use the relationship tool
in Access.  I personally have not had much success with it's use in regards
to queries when it comes to more complex DB systems, so I instead use SQL
with the proper joins to run queries.  Of course, the project I'm working
on, it would be a lot easier if I was able to do it in SQL server
environment, but corporate politics are at play, so I have to make due with
what I have, which makes the objective, "Striking a balance among the 3
points of view" a lot tougher: Administrator/Owner/Programmer (I.e. IT
Department, Top management, and the developer), Casual user of the
information (I.e. Supervisors, middle and upper management, Accounting
Department, etc), and those that inputs the information (Data Transcriber,
Operators, other temp or entry level positions in various departments, Sales
force)

The issue that we currently face is that the User Friendliness of the main
DB system that the company use is not there.  I could care less what the
administrator says with regards to that, but there are various issues at
hand, thus why we don't have our operators using the interface to report the
data.  Here's a list of the different issues at hand:

It's all text based.  Some may not find this so bad, but others barely even
know how to turn on the system let alone having to try to know all of the
different codes or know how to really type as some of them are the 1 (or
maybe 2) finger peck and point type.

Some may find it easy to use, but also find it's real easy to fudge the
numbers too thus again doesn't allow for accurate reports

There are no real controls in place to rectify the problem above within the
main DB program (the adminstrator disagrees with me by saying the
supervisors can view a report.  Yeah, this can be checked for certain
things, but it's still not near as accurate as it would be with the proper
controls in place)

Times has to be calculated by hand before they can enter that into the
system (I have seen some horrible math errors with regards to this, and not
only that, but when they report the amount of times, like say they spent 30
minutes, some will type .50 (the correct way) while others will type .30
(the wrong way).

Now to address these different issues, I have created a temporary program of
which it took me 1 month to create it, but cause of the various issues and
it was done from within Excel (given at the time, I didn't know Access
objects too well from the VBA side and I only had 4 weeks tops to get this
program developed), I told them specifically that this program is only meant
as an intermediate program until I can get a more long term solution in
place.

This long term type solution not only has to meet the different goals of the
Production department, but it must also meet a long list of various
objectives as a major aspect of it is going to be dealing with Production
and Inventory Control while at the same time being able to provide such
things like Cost Accounting capabilities, so I will also have to be able to
put in it's program the various stipulations dealing with the Accounting
profession.  Of course, this DB program will have to bear all of the
essentials of the different business and computer requirements.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

This is a bit like a learner driver saying he doesn't like messing about
with gear sticks and clutches, but is quite happy storing his suitcase in
the boot (trunk?).

Get this: queries and relationships are completely different things,
despite the Access team's attempts to mix them up together.

SELECT queries are structured windows on your data; alternatively think of
them as intelligent switchboard operators who can get all the people you
want to talk to connected in the right order at the right time. They are
only about data output. To be sure, update and insert queries are about
altering data in the tables, but without proper protection they are just
plain dangerous.

Relationships constitute the basic anatomy of your data structure: tables
can be persuaded to hold any old garbage and it is only the constraint
rules - unique indexes, validation rules, and foreign keys - that prevent
them all turning into a grey goo. So what are you going to do with the
orders charged to non-existent customer accounts? What happens to all the
students when someone accidentally deletes the Maths department record? If
none of this stuff matters to you, then perhaps you would be better off
working in Excel, which has much better list-handling logic than Access.
The only job of a rdbms is the maintenance of a robust and complex data
model (ok, and a sophisticated security system), and if you are spitting at
that then I don't see why you are using Access at all.

I understand that you are a mathematician and should know all this already,
so forgive me if this feels like old hat. As you've discovered, Access is
unlike all the other Office products where the enduser can simply start it
up and start typing. System analysis is a skill rather than an instinct and
does need learning; I would say that you do need much more than "general
guidelines" before starting to draw up tables. There is a body of knowledge
to be acquired too, which should be fairly easy since it's all based on set
theory anway! If this is a system on which peoples livelihoods are going to
depend, you might like to take some further advice.

All the best

Tim F

Okay, maybe we got a bit of a misunderstanding cause you right, you do have
to plan to great depth, but if you are trying to say that you have to plan
to the exact code, even further than having to use psuedo coding, that's
where I disagree with.

=====Clip=====

==============

As I did hint at, there are several different things that has to be taken
into account, of which your are absolutely correct in that security of the
data must be taken into account, and how does orphins (data that no longer
has a parent data anymore) get handled?  Besides just math and logics, does
the term "FlowChart" come to mind?  How about Document Control, Data
Validation not only to be sure the right type of data is entered, if the
data exists for things that deals with lookups, but also is it reasonable
and other issues?  How are the reports going to get the data it needs?  How
must the data be stored to support the various requirements while at the
same time allowing for efficiency use of the system resources (Data
normalization)?  What safeguards are in place to help prevent human errors
and fudging of data?  Who has what authorization of what data and to what
level of the data?  What sort of data backup is there planned and/or in
place, and when the any part (especially the main part) of the system, or
even the entire system goes down, what sort of backup plan is in place?  The
list can go on for quite a ways, but I think you get the idea.  Do you think
I have not had experience with a lot of these different issues?  Are we
going to have a perfect system, probably not, but we can certainly try to
work towards it.  Would I care about all of this, if I wasn't concerned
about accuracy of my reports?  Would the users be willing to use the system,
if they can't use it too well?

You are right to the extent that one can't just instinctly use Access, cause
one is most defintitely gonna have to spend some time learning Access.
However, if something doesn't work too good or I don't really care for
something (I.e. some of the error messages, ordinary users won't understand
too well, so I create my own instead), I will create work arounds.  Just as
I did in Excel using VBA to get around a lot of the issues that I had on the
spreadsheet side, I will use the same type tools and patterns within Access.
Now for me, I still had to go through this same process just like anyone
else, though I may have went through it a lot faster, but that's only cause
of the years of experience I have had on the software side, my experience
with SQL (Showcase Strategy) and VBA (Excel then MS Project, and now
Access).  Granted, Showcase's SQL may be a large part written from visual
choices, but when it comes to some of the more complex issues like certain
criterias, I most certainly had to learn the SQL language.

Even if I do know the stuff inside out programming wise, does that
necessarily mean I know how to put together a good system.  Probably not,
but I come with an Accounting Education and APICS training background along
with working in the Production department (about 1/4 of my time is on the
floor either doing things or helping others).

The main thing I was getting at, one should not take their planning down to
the exact code.  They do have to plan for various things, but even with all
of the proper planning, things may happen along the way of building the
program that will cause the develop to go back and make minor adjustments to
the plans or even have to refer back to the customer in certain cases
(though this should most definitely be kept to a limit as this can get old
pretty fast).  I was not trying to say that Access is necessarily a bad
product, but there are issues within Access that needs to be addressed (some
of which are also within VBA).  As far as certain issues are concerned such
as bugs, once I create a work around, I will stick to that work around until
there's a later documentation that states otherwise that the issue(s) has
been resolved, which brings me to another point, do you not think I am
constantly looking around doing some research trying to find ways to
improve?

As one last comment, I'm not trying to say that I know everything as that
would be far from the truth, but you did come over me basically as if I knew
next to nothing when it comes to this DB stuff.  I have been working with
computer systems probably longer than you may think.  I started out working
on computers on the software side, messing around with different things like
spreadsheet formulaes and such.  I was even introduced to computers
initially by a teacher who had me write my own spelling program in BASIC.
She had all of the codes typed out on paper and everything, so all I had to
do was type it in and run it, but none the less, that's what started my
whole computer experience.  Now that I have worked with various systems and
codes, I do have a high tendency to find things out a lot faster than most
ppl.  I guess that's also cause I have developed tools and methods along the
way.  Even with Paradox 4, of which I didn't have any DB experience prior to
then, it only took me 20 actual hours to learn it.  Added to that, as I was
learning it on my own, I showed 3 students how to do a couple of things and
their comments were that I tought them more in that short few minutes than
what the instructor taught in the whole 1.5 hours.  There were times when I
have had to turn for help with learning different things, but most of the
time, I learned from playing around in the systems and from well written
technical manuals.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

Ronald

First thing is to apologise if you felt I was disregarding your experience,
but I was attempting to steer a line between your professional standing and
what was clearly a naive question.

I disagree. There is plenty of space between "the exact code" and rushing
into building tables before the analysis is complete. A rough rule-of-thumb
is 90% analysis and 10% keyboard time -- but this may be for people who are
a bit impatient! As you have discovered, an error like having to change a
PK after implementation is more than a PITA -- it costs the customer and
should have been picked up at the pencil-and-paper stage. This is not "the
exact code", but more like changing your mind about where to put the
engine.  

Access is not perfect, but it is pretty mature and there are fewer bugs
than user misperceptions. What issues are you referring to?

Again, I appreciate that I am on thin ice here, but you might consider some
formal training in RDBMS design and analysis, or at least some reading that
addresses the theory and knowledge base than underlies it. The old file
managers like Paradox and dBase are not good starters for life in the real
database world.

Best wishes

Tim F

Maybe, we again have a differences in how we interpret "the exact code", as
I would say it does need to be taken down to psuedo coding (I hope you know
what psuedo coding means as it's a programming term that has been used quite
often).  Basically putting it, it's a writing of all of the logical steps
that the program is going to be running through, but yet, this writing may
not necessarily be in the proper syntax for the programming language that is
being used.  When I mention "all of the logical steps", what I mean is every
little step that the code has to do, sort of like asking a classroom full of
students, "How is the robot going to make a peanut butter sandwich?"

One student may say, put the peanut butter on one slice.

How is the robot going to do that, use it's metal limbs?  Dump it on the
slice?  Which slice?  How much?

So psuedo code does work out the details (if done properly), just not taken
to the syntax level of the actual programming language, which actually
allows some flexibility in that regards cause even if the developer realized
one language isn't working out, maybe another language would work and the
psuedo code can work for that language too.

This particular tool is one that I use quite often to help me work through
the different issues while at the same time, not having to use up too much
time having to worry about putting these steps into the required syntax code
that the language requires.  Does this tool resolve all of the different
issues?  No, not the least, cause you still have things like what code is
going to be modulated, how is the code going to be setup to be more easily
debugged.  However, as far as the question, "How is the code going to be
modulated?", that can normally be answered by the different patterns that
noticed as the psuedo code is being written down.  As far as how to setup
the code to make it easier to debug, use the resources efficiently, and
other similar issues, that's a matter of having standards of coding in
place, which these standards should be well documented so as others can
follow through with them.

I"m not sure if I really go along with the 10% keyboard and 90% analysis
cause part of that 90% analysis would be using the keyboard for writing test
code to test things out, thus what had me find these issues pretty quickly.
As I go through the process of setting up the code, I tend to do various
tests as I have setup some pretty high standards of having the code work as
it should be while at the same time, having it user friendly.  Let's face
it, there are a lot of ppl out there that barely even knows how to turn on a
system much less know how to work through different issues.

There are 2 particular issues of recent that I have ran into, one of which
is something I will just have to deal with regardless and the other, I am
working on creating an emulation for.

Issue 1: KeyCodes for Keypad

When any of the white keys (0-9 and .) is pressed to return an action, not
the digit or decimal, it does not return the keycode that's in the KeyCode
Constants list, and regardless if Numlock or Shift is used or not, the
"Enter" key on the keypad returns the value of "13", not the value of "108",
of which even the work around based on article 188550 (VB6), does not work
either.

Issue 2: DataValidation of when it should take place

I am in the process of creating forms, but when you don't want data
validation to take place, such as if there is a help button on the same form
as the form that the user is working in, you don't want data validation to
take place on the active textbox (if it currently has the focus) when the
user goes to click on this "Help" button.  As it currently stands, there is
no default way of allowing this option to take place within the Events of
Access (97, 2000 or 2002).  VB6 has a way to address this sort of issue.
The way VB6 addresses it, it uses the "CausesValidation" property for the
object that is recieving the focus, and the "Validate" Event for the object
that will lose the focus.

In this example with regards to the "Help" command button, this particular
button would have it's "CausesValidation" property set to "False" thus the
"Validate" event would NOT be triggered on the textbox that just had the
focus.  Pretty much all other controls would have this same
"CausesValidation" property set to "True" thus when any of them does get
triggered to recieve the focus, the Validate Event of the previous control
(if it's setup) gets triggered and ran.

There are 3 general reasons why I can see validation of the data itself
would not be needed, and for that matter, probably wouldn't want to have
taken place.

    1:    Getting help on something
    2:    Resetting the form (Some warning may be in place, but this would
be within the Click event of the command button)
    3:    Backing out of the form (Some warning may be in place, but this
would be within the Click event of the command button)

So to address this particular issue, I tried to see what I could do via
keystroke method, and that's when I learned of the issues at hand with the
KeyCode Values.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

There is a problem of viewpoint here. I appreciate that you posted your
question in a programming NG, but the subject itself revealed a basic error
in the db schema design process -- which is way, way, way previous to any
kind of coding. Franky, if you are still messing about choosing what to use
for a primary key, all the coding you are doing now is a Waste Of Money,
Brains And Time. You are painting the hull without knowing how many masts
are going up.

If this is meant to say that interviewing users as part of systems analysis
is hard, then you are right! That is why it takes the lion's share of the
project time. Users do not know how they do things, they don't know what
they want, they don't understand their own business practices -- but that
is no excuse for you not getting all that information out of them! It takes
a long time, it's boring, and the project managers hate you for it: but
they'll hate you a lot more if you attemtp to short-circuit the process and
deliver a useless product on time or, more likely, complete 90% of it and
then rip it all up and start from scratch because there is an error on page
2.

Best wishes

Tim F