I need some expert advice on database design

[jpwgh via AccessMonster.com]

I work for a non-profit organization. With the help of a grant, we hired a
consultant to come in and design a database incorporating 3 divisions of our
organization. He dazzeled and intimidated us with his technical jargon. He
has been working on this project since Feb. In Aug. I bought some books and
began learning to program using VBA. By Oct. I created a small database to
automate repetitive tasks done by staff with little or no computer skills.
Granted, there are some things that probably could be done better, but I did
do more than just creating some macros and converting them to code. I used
some SQL stmts to copy and delete tables, some DAO.recordsets to put values
in specified fields and used code to specify to which drawer on the printer
to use to print on legal paper. I am not claiming to be an expert but I know
some basics. I am no longer intimidated by our consultant. Problem is when
we asked questions, some of his answers didn't jive. I took a look at his
"code". The majority of his database was programmed using macros (he didn't
even bother to convert them to code). What little he did code was done
strictly using the wizard and much of that did not apply to our database.
There were command buttons that did not exist referencing forms and queries
that were not in our database. I think I have management almost convinced
there is a problem. I don't think they took me serious at first being a self
taught novice.

Since I only know VBA and I know there are other programming languages out
there, are there other programming languages better suited certain designs?
How can I find out what program design would work the best in our situation.
I need to be able to state my case to management.

Any suggestions/recommendations would be greatly appreciated.
Thanks.

 

[Larry Daugherty]

From what you report, you may have just cause for concern...

Anyone who programs using Access's "Macros" as opposed to VBA is
highly suspect as a professional Access developer. Professional
Access Developers implement their designs using VBA to code just about
everything (depending on the version of Access there may be
justification for a few, very few, Macros). They use VBA from the
start. They don't program using Macros and then convert to VBA later.
Complex programs can develop mysterious bugs. VBA provides a rich
debugging environment. Macros are difficult and sometimes impossible
to debug.

From February to January is a calendar year. If there has been full
time development effort going on then you folks mush have some pretty
hefty requirements! That must cover several separable applications or
chunks of functionality. There must be some prototypes out and
running by now; at least for evaluation. Maybe replacing the
applications that went before.

Has anyone been working diligently with your developer in the role of
analyst to help her or him really understand the requirements and
workflows? What Specifications have been developed between the
developer and yourselves that you have both signed as essentially the
"contract" for the work being done? No matter how far along you think
you are in the project you need the specifications: Problem Statement,
Product Specification and Functional Specification. Those documents
are required to know that management, developer and all interested
parties really know the goal and when it has been achieved. They are
required before a Preliminary Design and Project Plan and Estimate can
be completed.

One absolute test of basic competence in developing with Access is
familiarity and proficiency in splitting the application into Front
End and Back End components and knowing several reasons why that is a
good thing to do. A competent developer can perform the actual
splitting (with or without the supplied tools) in under 5 minutes.
Make sure that you have copied the current application to a safe
backup area before you brace the developer with actually doing it. If
s/he is not familiar with the process the application could get
screwed up. Backup first! If you're concerned with demonstrating
misrepresentation or even fraud on the part of the developer then that
whole test might be carried out in the presence of yourself and one or
more senior managers in the office of a senior manager.

Another hallmark of a good developer is knowledge of Relational
theory. It is absolutely, positively impossible to produce good
schema without understanding the theory and practice of relational
design. The rule of thumb is to normalize one's data unless there is
a good reason not to do so on a case by case basis. Just so that
you'll have some understanding, visit

www.mvps.org/access and look for the "10 Commandments of
Relational Databases".

Keep an open mind. With one client I was asked to come on site and
overhaul and enhance all of their Access applications. There were
quite a few. With one exception, they were all enhanced and delivered
to rave reviews. That one balky project never did get totally
finished. The primary customer for that application was insecure and
balky and would not communicate. The parts of that project that were
defined by others were well done and quickly done. Her part never got
defined and never got done. The difference in the outcomes was
directly related to the difference in the quality of communication
with the analysts. None were "Analysts"; they were all users of the
applications in question.

Be aware that all is not lost regarding Macros and VBA. There is a
facility within Access to convert Macros to VBA. Again, back up your
complete application first. If the design was good then the
conversion should bring you along the desired direction. My concern
is that someone using Macros reflexively probably doesn't understand
the capabilities of Access.

Some other things:
Your superior knowledge of the desired outcomes may have given you
a huge leg up in producing your results.
You may have taken a different path to achieve the results you
did. You may be comparing apples and oranges.

Good luck with your application,

HTH

 

[Jeff Boyce]

One approach to development is laying out an extensive list of requirements
before beginning. Then, as the situation changes, implementing a rigorous
"change management" system.

Another approach to development has developer and end users working closely
together throughout the process. The first step is identifying the most
critical and/or most vulnerable and/or highest impact functionality ("what's
the #1 show-stopper? -- if it can't do this, forget about it!). Development
under this approach works in "iterative cycles". After each short-scale
development, a testable/usable product is delivered. If everyone agrees,
the same (#1 show-stopper) approach gets used to identify the next most
critical/vulnerable/impactful functionality.

By the time the development is done, the users have had functionality during
the entire project, and only the least critical/important features/functions
are left.

The first (requirements first, design/develop second, test third, implement
last) approach is considered a "waterfall" approach. It requires that the
users understand and identify all of their needs up-front ... what are the
odds that nothing will be left out and nothing will change?!

The second (key requirements/testable functionality delivered) approach is
"iterative" or "agile". While it requires much closer interaction between
users and developers throughout the project, this closer interaction is more
likely to result in the final product meeting the actual needs.

Best of luck on your project!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[jpwgh via AccessMonster.com]

Thank you for taking the time to listen to me and for your feedback. I came
into this project 4-5 months after it started due to staff turnover. There
have been frequent meetings along the way to discuss our requirements,
workflow etc. My concerns aren't with design or concept issues. I expect
those type of issues. Like, he didn't really understand what we were saying
or we didn't understand what he was asking. Or when you actually test
something and see it, you find out it doesn't work exactly how you expected.
I expect that.

I also understand there are a variety of ways to accomplish the same task. I
also know getting a programmer to criticize another programmer may be like
getting a doctor to criticize another doctor. It is a touchy thing. Just
because a user didn't get what they wanted doesn't necessarily mean there was
a problem with the programmer. In this case I feel there are serious
problems with his programming skills. If I am wrong about this, please let
me know.

There are examples where it looks like he used the "I wonder what this does"
approach. The first two items in the code for the first form are:
Private Sub Command 31 Click()
End Sub
Private Sub Command 31 Enter()
End Sub

That's it. No actual code to go with it.

There is also code for a command button for a word application and one for
auto dialer. Both set up using the wizard. We have no use for either. And
none of these command buttons are actually on the form.

There is another form with 33 command buttons in the code. This particular
form only has two command buttons. Only one is controlled by code. The
other is controlled by a macro. It looks like this code was copied from
somewhere else since it references forms and queries that are not in our
database and we have no reason to use. Again, none of these command buttons
are even on the form. Looks like he broker a number of the commandments.

He actually had the following set up as a macro, but I converted it to code.
I have never used SendKeys and don't understand what they do. I never
bothered to learn about them since everyone says don't use them. What is he
trying to do here?

DoCmd.SetWarnings False
' % alt (t)ools (d)atabase utilities (c)ompact database
SendKeys "%tdc", False

Part of me thinks the reason it has been taking so long, is his lack of
skills. We do use alot of forms and collect alot of information, but other
than that, I don't think our requirements are that complicated. What we got
so far from him, should not have taken this long. Because of what I do know
about programming, I don't have any confidence about anything else he does,
especially things I don't know anything about.

Thank you. You have given me some good information. When I meet with
management I will suggest we look at the specs from this project. If I am
correct about his programming skills, I need to convince management that it
is a serious problem and not just me.

From what you report, you may have just cause for concern...

Anyone who programs using Access's "Macros" as opposed to VBA is
highly suspect as a professional Access developer. Professional
Access Developers implement their designs using VBA to code just about
everything (depending on the version of Access there may be
justification for a few, very few, Macros). They use VBA from the
start. They don't program using Macros and then convert to VBA later.
Complex programs can develop mysterious bugs. VBA provides a rich
debugging environment. Macros are difficult and sometimes impossible
to debug.

From February to January is a calendar year. If there has been full
time development effort going on then you folks mush have some pretty
hefty requirements! That must cover several separable applications or
chunks of functionality. There must be some prototypes out and
running by now; at least for evaluation. Maybe replacing the
applications that went before.

Has anyone been working diligently with your developer in the role of
analyst to help her or him really understand the requirements and
workflows? What Specifications have been developed between the
developer and yourselves that you have both signed as essentially the
"contract" for the work being done? No matter how far along you think
you are in the project you need the specifications: Problem Statement,
Product Specification and Functional Specification. Those documents
are required to know that management, developer and all interested
parties really know the goal and when it has been achieved. They are
required before a Preliminary Design and Project Plan and Estimate can
be completed.

One absolute test of basic competence in developing with Access is
familiarity and proficiency in splitting the application into Front
End and Back End components and knowing several reasons why that is a
good thing to do. A competent developer can perform the actual
splitting (with or without the supplied tools) in under 5 minutes.
Make sure that you have copied the current application to a safe
backup area before you brace the developer with actually doing it. If
s/he is not familiar with the process the application could get
screwed up. Backup first! If you're concerned with demonstrating
misrepresentation or even fraud on the part of the developer then that
whole test might be carried out in the presence of yourself and one or
more senior managers in the office of a senior manager.

Another hallmark of a good developer is knowledge of Relational
theory. It is absolutely, positively impossible to produce good
schema without understanding the theory and practice of relational
design. The rule of thumb is to normalize one's data unless there is
a good reason not to do so on a case by case basis. Just so that
you'll have some understanding, visit

www.mvps.org/access and look for the "10 Commandments of
Relational Databases".

Keep an open mind. With one client I was asked to come on site and
overhaul and enhance all of their Access applications. There were
quite a few. With one exception, they were all enhanced and delivered
to rave reviews. That one balky project never did get totally
finished. The primary customer for that application was insecure and
balky and would not communicate. The parts of that project that were
defined by others were well done and quickly done. Her part never got
defined and never got done. The difference in the outcomes was
directly related to the difference in the quality of communication
with the analysts. None were "Analysts"; they were all users of the
applications in question.

Be aware that all is not lost regarding Macros and VBA. There is a
facility within Access to convert Macros to VBA. Again, back up your
complete application first. If the design was good then the
conversion should bring you along the desired direction. My concern
is that someone using Macros reflexively probably doesn't understand
the capabilities of Access.

Some other things:
Your superior knowledge of the desired outcomes may have given you
a huge leg up in producing your results.
You may have taken a different path to achieve the results you
did. You may be comparing apples and oranges.

Good luck with your application,

HTH

I work for a non-profit organization. With the help of a grant, we hired a
consultant to come in and design a database incorporating 3 divisions of our

[quoted text clipped - 24 lines]

Any suggestions/recommendations would be greatly appreciated.
Thanks.

 

[jpwgh via AccessMonster.com]

Thank you for your feedback. As you can see from my other reply, I came into
this project late. The problem we have is that I think the person doing our
programming is not right for the job. Hopefully we can change that. Then we
can use your feedback to help us get back on track and ensure we are moving
in the right direction.

One approach to development is laying out an extensive list of requirements
before beginning. Then, as the situation changes, implementing a rigorous
"change management" system.

Another approach to development has developer and end users working closely
together throughout the process. The first step is identifying the most
critical and/or most vulnerable and/or highest impact functionality ("what's
the #1 show-stopper? -- if it can't do this, forget about it!). Development
under this approach works in "iterative cycles". After each short-scale
development, a testable/usable product is delivered. If everyone agrees,
the same (#1 show-stopper) approach gets used to identify the next most
critical/vulnerable/impactful functionality.

By the time the development is done, the users have had functionality during
the entire project, and only the least critical/important features/functions
are left.

The first (requirements first, design/develop second, test third, implement
last) approach is considered a "waterfall" approach. It requires that the
users understand and identify all of their needs up-front ... what are the
odds that nothing will be left out and nothing will change?!

The second (key requirements/testable functionality delivered) approach is
"iterative" or "agile". While it requires much closer interaction between
users and developers throughout the project, this closer interaction is more
likely to result in the final product meeting the actual needs.

Best of luck on your project!

I work for a non-profit organization. With the help of a grant, we hired a
consultant to come in and design a database incorporating 3 divisions of our

[quoted text clipped - 24 lines]

Any suggestions/recommendations would be greatly appreciated.
Thanks.

 

[G. Vaught]

I agree with the other experts here. If he is using macros I would dismiss
the need for his services. The only macro I use is the autoexec, which opens
the database to a particular starting form or switchboard. I generally
create my own switchboard and do not use the built-in wizard with Access.

If you can get a grasp of building relational tables and table
normalization, most likely you could step in for the hired consultant. This
is the biggest obstacle of learning Access. From here everything is a piece
of cake.

What version of Access did the consultant say he worked with last? If he
says Access 97, here lies the problem. Access has made enormous improvements
and a true Access developer will have worked the the most recent, such as
2007 or at least the previous release 2003. I say this as I just installed
2007 on my machine.

Using Access you can create a workable database without using or knowing
VBA. However, depending on your organizations needs, coding VBA is suitable
and expands on the functionality of Access. Good developers know how to use
VBA. The only time you want to go higher, such as SQL Server and/or .Net
languages is when your organization is doing extensive transactions and you
need a more robust program.

Some key questions you can ask the consultant:
1. Do you know how to program using VBA? Creating a macro and then
converting it to VBA does not count. They should be able to code some stuff
free handed. Using books is okay, as it is impossible to know everything
when coding. However, common basic VBA generally you can code as you go.
2. Do you always split your database? Good developers of Access always split
the database. Table only on a server type machine and a front-end that runs
on each users machine. If he does not know this concept, this should be
grounds for dismissal.
3. Do you know how to implement security in Access? If he says to use just a
database password, this should be grounds for dismissal.

 

[Tony Toews [MVP]]

Another approach to development has developer and end users working closely
together throughout the process.

That's pretty much how I've been working the last 15 years. Which is
also why I came up with the Auto FE Updater which helps me to
distribute new copies of the FE every few hours or few days.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I work for a non-profit organization. With the help of a grant, we hired a
consultant to come in and design a database incorporating 3 divisions of our
organization. He dazzeled and intimidated us with his technical jargon.

Fire his *ss. From your other comments this person is a useless tw*t.
(You can put an i or an a depending on your preference.) Macro's only?
Not a snowballs chance in he-double-l.

Dazzled? Well, yes my clients are dazzled by my knowledge <smile> and
my ability to explain issues in their language.

Intimidated? No. My clients don't think that of me. I talk in
thier language.

I need to be able to state my case to management.

That's my blunt answer. Feel free to use my answer as the "executive
summary" and the other responses as commentary. <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Beetle]

I am NOT an Expert/MVP like Tony, Jeff and the others that have responded,
but based on what you've posted so far, I know more than the person your
company is paying to screw up your database.

I also know my limitations, which he/she apparently does not.

IMO (for what it's worth) you should resolve the situation ASAP so you and
your company can get back on track with your db.

 

[jpwgh via AccessMonster.com]

Thank you for validating my opinion and making me laugh.

 

[jpwgh via AccessMonster.com]

Thank you for all your help. Giving us the appropriate questions to ask will
not only help management understand the problems, but also give us concrete
reasons to cut him loose.

I agree with the other experts here. If he is using macros I would dismiss
the need for his services. The only macro I use is the autoexec, which opens
the database to a particular starting form or switchboard. I generally
create my own switchboard and do not use the built-in wizard with Access.

If you can get a grasp of building relational tables and table
normalization, most likely you could step in for the hired consultant. This
is the biggest obstacle of learning Access. From here everything is a piece
of cake.

What version of Access did the consultant say he worked with last? If he
says Access 97, here lies the problem. Access has made enormous improvements
and a true Access developer will have worked the the most recent, such as
2007 or at least the previous release 2003. I say this as I just installed
2007 on my machine.

Using Access you can create a workable database without using or knowing
VBA. However, depending on your organizations needs, coding VBA is suitable
and expands on the functionality of Access. Good developers know how to use
VBA. The only time you want to go higher, such as SQL Server and/or .Net
languages is when your organization is doing extensive transactions and you
need a more robust program.

Some key questions you can ask the consultant:
1. Do you know how to program using VBA? Creating a macro and then
converting it to VBA does not count. They should be able to code some stuff
free handed. Using books is okay, as it is impossible to know everything
when coding. However, common basic VBA generally you can code as you go.
2. Do you always split your database? Good developers of Access always split
the database. Table only on a server type machine and a front-end that runs
on each users machine. If he does not know this concept, this should be
grounds for dismissal.
3. Do you know how to implement security in Access? If he says to use just a
database password, this should be grounds for dismissal.

I work for a non-profit organization. With the help of a grant, we hired a
consultant to come in and design a database incorporating 3 divisions of

[quoted text clipped - 39 lines]

Any suggestions/recommendations would be greatly appreciated.
Thanks.

 

[Armen Stein]

Some key questions you can ask the consultant:
1. Do you know how to program using VBA? Creating a macro and then
converting it to VBA does not count. They should be able to code some stuff
free handed. Using books is okay, as it is impossible to know everything
when coding. However, common basic VBA generally you can code as you go.
2. Do you always split your database? Good developers of Access always split
the database. Table only on a server type machine and a front-end that runs
on each users machine. If he does not know this concept, this should be
grounds for dismissal.
3. Do you know how to implement security in Access? If he says to use just a
database password, this should be grounds for dismissal.

Another quick check: any professional developer should use error
handling in his/her code. It's easy to tell, just look for a
statement beginning with On Error somewhere near the top of each
procedure. It should be in every one. If it isn't found anywhere,
you are not working with a pro.

I agree with what everyone else is saying - you should unload this
person immediately.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com