We want to hear about your experiecne with Access templates

[Dany Hoter]

Hi
The Access team wants to hear your experience with Access templates,
- Did you ever download an Access template from office.microsoft.com
(Office Online) ?
- Did you download Access templates for any other sources?
- When you downloaded a template did you find it useful as is?
- Did you enhance the template functionality with VBA code?
- Can we contact you and understand better your use of templates?


Thanks in Advance

Dany Hoter
Access product planner
mailto[email protected]

 

[Eric]

I downloaded and used the "Issues" template. I made a few modifications to it
but it is truly an elegant piece of work. Most of the mods are within the
realm of a beginner or intermediate user....added a second email button,
changed the names of reports, added a completion date....couple others.

I am stuck on how to add a button/macro combination to allow the user to
browse and select a file. I've added a hyperlink field (Access 2003) and will
use if for a path to pdfs, photos, maps, etc. When the user wishes to give a
picture instead of using a 1000 words, he could fill in the hyperlink and the
boss could click on it to see what was referenced. I'm stuck on
browsing/selecting the files. The link itself works but I'm trying to avoid
making the user remember/type the path and file name.

Eric

 

[FL]

Will "issues database" template work with Access 2000? This DB looks
interesting for tracking issues at work.

 

[Dirk Goldgar]

In

I downloaded and used the "Issues" template. I made a few
modifications to it but it is truly an elegant piece of work. Most of
the mods are within the realm of a beginner or intermediate
user....added a second email button, changed the names of reports,
added a completion date....couple others.

I am stuck on how to add a button/macro combination to allow the user
to browse and select a file. I've added a hyperlink field (Access
2003) and will use if for a path to pdfs, photos, maps, etc. When the
user wishes to give a picture instead of using a 1000 words, he could
fill in the hyperlink and the boss could click on it to see what was
referenced. I'm stuck on browsing/selecting the files. The link
itself works but I'm trying to avoid making the user remember/type
the path and file name.

I don't know if this will help you or not, but you can see this link for
how to call the Windows File Open/Save dialog:

www.mvps.org/access/api/api0001.htm

 

[Eric]

I'll look at it further but the first blush is that this is way more
complicated than I would like. I'm a beg/int kind of user. Isn't there a
magic button that when you press it, it goes >poof< and there it is?

Eric

 

[Sascha Trowitzsch]

Hi Dany,

Hi
The Access team wants to hear your experience with Access templates,
- Did you ever download an Access template from office.microsoft.com
(Office Online) ?
- Did you download Access templates for any other sources?
- When you downloaded a template did you find it useful as is?
- Did you enhance the template functionality with VBA code?
- Can we contact you and understand better your use of templates?

I downloaded all A2007 templates. But just for interest.
- For a beginner they might be helpful in understanding how to design databases.
But for them simple ACCDBs would be a better solution. Why use templates? Who
will produce "Issues" databases over and over again?
- For developers - and Access is a developers tool! - all theese templates are
not fitting the needs of a concrete project. You had to modify too much as if
this would be a very effective way to start the development of a database.

The only way I use templates is to create them myself using the developer
extensions.
Such a template I need has:
- Several predefined options. (alternate row colors, key behaviour, ...)
- A standard ribbon (USysRibbonXML)
- Some default forms for dialogs, popups, ... with some generally useful code in
them. The same fpor reports.
- Some always needed modules with general functions.
- ...
- But no tables, no forms etc. that are useless for other projects.

In other words: Defaults for my corporate identiy.

For me the MS templates are too custom-built.

Ciao, Sascha

 

[Dirk Goldgar]

In

I'll look at it further but the first blush is that this is way more
complicated than I would like. I'm a beg/int kind of user. Isn't
there a magic button that when you press it, it goes >poof< and there
it is?

It's possible that there is, in Access 2007, but I don't have that
version and can't check. The code I pointed to doesn't need you to
understand it, though. You would create a new standard module, paste
all the code -- everything between the "*** Code Start ***" and "***
Code End ***" lines -- into that module, and then follow the example for
calling it that appears at the top of the web page.

In your case, you'd probably want to allow selection of any type of
file, so your code to call the function would be something like this:

Dim strFilter As String
Dim strFileName as string

strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select a file...", _
Flags:=ahtOFN_HIDEREADONLY)

You'd probably execute the above code in the Click event procedure for a
command button, and you'd probably finish up, after getting the selected
file name, with some code to assign the selected file to your hyperlink
field; maybe something like this:

If Len(strFileName) > 0 Then
Me!MyHyperlinkField = "#" & strFileName & "#"
End If

Note that the validity of the file path you hyperlink will depend on
what PC this is run from. If you link to "C:\MyFiles\MyFile.pdf" on
your computer, that path and file may not exist on your boss's computer.
So you should link using a valid network path or a mapped drive that
exists on all your company's computers.

 

[Eric]

I can't quite connect all the dots conceptually.

1. I moved the code you cited to a module and called the module "GetFileName".
2. I went into the Issues macros and created a macro called cmdGetFileName
and said it was to run a command On Click.
3. I created a button on the form and assigned it the property of
IssueFormMacros:cmdGetFileName.

The I clicked and...nada. Then I realized that I had not passed anything to
the module. So....how do I do that? Do I type it into the "Condition" column
of the macro screen?

Eric

 

[Gen]

I downloaded the inventory management database to use as a template for a
purchase orders database. I had to make a few modifications to the
relationships and customize it, but it was easy to understand and even gave
me (a beginner to Access) a few lessons on programming!

 

[Sascha Trowitzsch]

Hi,

I downloaded the inventory management database to use as a template for a
purchase orders database. I had to make a few modifications to the
relationships and customize it, but it was easy to understand and even gave
me (a beginner to Access) a few lessons on programming!

Nice that it was helpful for you.
What even a beginner should not do but MS did in their templates:
- Use blanks in table names, query names and field names. ("Product ID" <>
"ProductID")
- Not to use prefixs for database objects (table "Products should be named
"tblProducts")

Ciao, Sascha

 

[John W. Vinson]

I can't quite connect all the dots conceptually.

1. I moved the code you cited to a module and called the module "GetFileName".
2. I went into the Issues macros and created a macro called cmdGetFileName
and said it was to run a command On Click.
3. I created a button on the form and assigned it the property of
IssueFormMacros:cmdGetFileName.

The I clicked and...nada. Then I realized that I had not passed anything to
the module. So....how do I do that? Do I type it into the "Condition" column
of the macro screen?

Dirk's code is VBA, a Module, not a Macro. (I realize that if you are familiar
with Excel or Word code, VBA is called "macros" there - but in Access, Macros
are a different beast).


The button should have [Event Procedure] on its click event and you should use
the ... icon by that event, invoke the Code Builder, and put the code there.

John W. Vinson [MVP]

 

[Tony Toews [MVP]]

What even a beginner should not do but MS did in their templates:
- Use blanks in table names, query names and field names. ("Product ID" <>
"ProductID")
- Not to use prefixs for database objects (table "Products should be named
"tblProducts")

I respectfully disagree with much of what you said.

I do agree with no spaces in table or field names. However spaces in
other object names are just fine.

I totally disagree with the idea of prefixing objects with the type of
object, such as tbl, qry, frm and report. There is no reason
whatsoever do this other than following "standards". And I see no
reason to follow "standards" in this case. Indeed the ease of use far
out weigh "standards"

For more details and relational see Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tonysobjectnamingconventions.htm

I should also add that most of the newsgroup denizens and my fellow
MVPs disagree with me. <smile> But that's fine because I know I'm
right.

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/

 

[John W. Vinson]

I should also add that most of the newsgroup denizens and my fellow
MVPs disagree with me. <smile> But that's fine because I know I'm
right.

Hey Tony, shouldn't that have a IMH(bc)O after it?

(In My Humble (but correct) Opinion)?

John W. Vinson [MVP]

 

[Tony Toews [MVP]]

Hey Tony, shouldn't that have a IMH(bc)O after it?

(In My Humble (but correct) Opinion)?

I've never been humble in my life so why should I start now?
<chuckle>

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/

 

[Sascha Trowitzsch]

I respectfully disagree with much of what you said.

I do agree with no spaces in table or field names. However spaces in
other object names are just fine.

I totally disagree with the idea of prefixing objects with the type of
object, such as tbl, qry, frm and report. There is no reason
whatsoever do this other than following "standards". And I see no
reason to follow "standards" in this case. Indeed the ease of use far
out weigh "standards"

It's just my experience, that prefixing really IS usefull.
Years ago it didn't prefix as well. I'm happy that I do now. And not at all for
following standards.
VBA code or SQL statements are much more transparent and faster to read, when I
use prefixes.
Queries like
"SELECT MAX([price]) As MAXPRICE FROM qryProductsFiltered"
"SELECT MAX([price]) As MAXPRICE FROM tblProducts"
are more understandable in design than
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a query)
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a table)

Do you think of the fact that most book authors indicate to use prefixes is just
due to follow standards?

Ciao, Sascha

 

[Pieter Wijnen]

Well, I for one side with you as always

Pieter

 

[Pieter Wijnen]

I don't really see why the reader of the SQL should be interested in wether
the data comes from a table, a view or a query (or a synonym in an oracle
database). That said I usually pre- or postfix my queries according to it's
usage in order to 1) find them easier 2) because you can't have tables &
queries with the same name

ie
Table: CUSTOMER
Form: Customer
Query: frmCustomer

Pieter

I respectfully disagree with much of what you said.

I do agree with no spaces in table or field names. However spaces in
other object names are just fine.

I totally disagree with the idea of prefixing objects with the type of
object, such as tbl, qry, frm and report. There is no reason
whatsoever do this other than following "standards". And I see no
reason to follow "standards" in this case. Indeed the ease of use far
out weigh "standards"

It's just my experience, that prefixing really IS usefull.
Years ago it didn't prefix as well. I'm happy that I do now. And not at
all for following standards.
VBA code or SQL statements are much more transparent and faster to read,
when I use prefixes.
Queries like
"SELECT MAX([price]) As MAXPRICE FROM qryProductsFiltered"
"SELECT MAX([price]) As MAXPRICE FROM tblProducts"
are more understandable in design than
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a query)
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a table)

Do you think of the fact that most book authors indicate to use prefixes
is just due to follow standards?

Ciao, Sascha

 

[André Minhorst]

Hi Tony,

I respectfully disagree with much of what you said.

I do agree with no spaces in table or field names. However spaces in
other object names are just fine.

I totally disagree with the idea of prefixing objects with the type of
object, such as tbl, qry, frm and report. There is no reason
whatsoever do this other than following "standards". And I see no
reason to follow "standards" in this case. Indeed the ease of use far
out weigh "standards"

As we are discussing about Access 2007 features I would like to tell you
a reason why to use prefixes for Access objects: In contrast to the
database window the navigation pane allows you to open any object
without using the mouse. You just click F11 to active or show the
navigation pane, click Strg + f, then type a part of the object's name.
So if you are looking for a table, you just type "tbl" and all tables
will appear. Scroll through the tables until you find the requested one
and click on enter to open it. That won't work without any prefixes. ;-)

Another reason to follow this standard is that it might be easier for
other developers to read and understand your databases and the objects
and code inside. But I'm sure you already know that. ;-)

For more details and relational see Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tonysobjectnamingconventions.htm

In this article you write:
"But still this isn't too bad. This would still be manageable. The
real pain point is the various query, form and report wizards. Where
you can only see 8 or 12 objects. The above database has 1200 queries,
450 forms and 350 reports. See the below screen."

If I had a database with so much objects, I would just drag the objects
(tables/queries) from the database window into the query instead of
using the wizards' dialogs.

In "Tony's Table and Field Naming Conventions" you say:
"All field names are prefixed with the initials of the table name, e.g.
cName, ihInvoiceNbr and dQuantity"

That sounds somehow like redundant information. We don't give our first
names the initial of our last names, do we? Altough "mAndré" and "tTony"
looks funny somehow ... ;-)
What when you have tables with the same initial?
What if you have to change a table name after creating it or to add a
customer table from one database to another one that already contains a
table named categories? Seems to be a lot of work ... even if other
objects and code already refer to this object.

I should also add that most of the newsgroup denizens and my fellow
MVPs disagree with me. <smile> But that's fine because I know I'm
right.

*g*

Ciao
André

 

[Pieter Wijnen]

As oposed to know the table structure before you even consider altering one?

Pieter

 

[Tony Toews [MVP]]

It's just my experience, that prefixing really IS usefull.

And my experience is that prefixing is an utter waste of time. Read
my web page on my reasons..

VBA code or SQL statements are much more transparent and faster to read, when I
use prefixes.
Queries like
"SELECT MAX([price]) As MAXPRICE FROM qryProductsFiltered"
"SELECT MAX([price]) As MAXPRICE FROM tblProducts"
are more understandable in design than
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a query)
"SELECT MAX([price]) As MAXPRICE FROM Products" (Products is a table)

And just how does it matter that Products is a query or a table in
your example? Queries should be much more descriptive of what they do
compared to tables, as in your first example ProductsFiltered vs
Products.

Do you think of the fact that most book authors indicate to use prefixes is just
due to follow standards?

I could easily be one of those book authors if I chose to be. My
website as about 200 pages of text and some of those pages are quite
long. If I threw in screen shots everywhere my website would be the
equivalent of a book.

And I don't care about book authors opinions. Maybe they're just
blindly following along without any particular thought as to why.

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/