Free Access Help
Database Products
Shell Database
Missions
Software
Services
Office Automation
Services
Great Offer!
Huge Example Database for
MS Access Developers!
Syndicate Jon's articles on your site! Fast, Easy & Free!
Link to
us
You
are welcome to link to articles and tips on our website
Access Help Examples Ebook
Now Updated with Ten new examples!
Take a Look at this mini app that can be found only in our
Access Help Examples
Ebook. Our Access Ebook contains 15 mini applications that you can use to
enhance your databases. Take a look at this one to see what I mean.
Splash
Screen with Drop Down Menus
Get our Drop Down Menu Designer
Free!
Access Help - Free Drop Down Designer
Saves you hours every time you start a new project
Create a user interface by simply editing a table
Add 5 Drop down menus that are six selections deep
Build your forms and reports and just add the names
to the setup table for an instant GUI!
You may want to tell a friend about this site. Fill in the space below and click the button. We will send the url to the address you enter. Thanx!
Access Help
Articles
You need this powerful and almost unheard of feature of
Access:
Tired of that bloated and slow starting feeling? Need more speed?
If you are finding that you are getting unexplained errors with your
applications as you develop or you have noticed that it seems to be
running slower then it may be time to use the rarely mentioned decompile
option on your application. When you compile VBA code various pieces of old code often get left
behind. Decompile is a relatively unknown Access feature than can help drastically reduce
the size of front end databases and also reduce bloating and decrease
start-up times is the /decompile option. The /decompile command line
option cleans out these bits of code and allows for a fresh start.
Decompile when:
When you are making changes to an application and have compiled and tested
several times.
When the startup form seems to take a lot longer to load than in the
past
When errors start happening that really don't make sense
If you are not
getting those unexplainable errors above, then you don't need to run the
decompile more often than once every few weeks or so to clean things out.
Just take heed of the startup times and overall speed of the application.
If it starts to appear that it is running slower than before, try the
decompile and see what happens. You will notice a big difference if
you have never decompiled an
application you have written before. You should see a big improvement both
in space and speed.
I also find that you will most likely need to do a compact and repair
found in the database utilities menu. This most often greatly reduces the
size of the mdb file. The decompile will often make the original
file larger, thus the compact and repair.
You can accomplish this with a short cut or with a .bat file
Here is an example:
"C:\ProgramFiles\MicrosoftOffice\Office10\MSACCESS.EXE"
"C:\Customers\JIMSApp.mdb" /excl /decompile /nostartup
pause
Note: In the above example, put the whole statement on one line
including the /nostartup option in your batch file or on your shortcut.
Of course put your own path and file name to your applications.
You can remove the pause line from a batch file if you don’t need to pause
the dos screen to see the results.
Avoid these 7 Deadly Database Sins:
Problems Access developers can avoid by learning the techniques in
Team Database
1 Startup Frustration
You can avoid the frustration of a long learning curve with the ability to
see how the source code behind Team Database ties all of the tools of
Access together into one powerful and advanced application. An
application is not just a bunch of forms. You must know how all of the
tools of Access link together to create a professional application. A
first-class application requires the use of SQL, VBA, the Access Macro
language and relational database theory.
2 Finding Good Examples that Work Together
You can avoid the longs hours searching for good examples that work
together when you get Team Database. You will soon be able to create
applications with new ideas much faster. You will be able to secure more
work by building a decent reputation of rapidly delivering high quality
tools quicker than your competition.
3 Knowledge of how to create a User Interface
Your users have expectations of how an application will function because
they are already familiar with Windows and browsers. You should know how
to develop professional user interfaces that make your applications work
like your users are accustomed to with other windows products. Your users
will enjoy the benefit of an interface that works like they expect it to.
4 No splash or login screens
Learn to avoid the irritating Access startup screen by creating your own
splash screen technology and therefore your users will go straight in to
your application like a windows product.
5 Tedious steps needed to find information
Learn to avoid making your users trudge through several forms or screens
to get to important data. If you constantly require your users to back up
and retrace their steps to enter or find data they will get frustrated.
Learn techniques that allow them to get to data with the minimum of
screens. Team Database can show you how to generate reports or drill down
to exact data with dynamic queries that can drill through tons of data
with just a few mouse clicks.
6 Data Reentry
Learn to avoid data entry fatigue for your users by deploying any of the
seven user input techniques found in Team Database.
7 Data Security (users getting access to data they are not supposed to
see)
Learn to secure and segregate data with the multi user, multi department
data security model in Team Database. Data security is a must in just
about every corporate environment these days. You will have to know how
to secure your applications in order to secure business clients.
More Speed for Your Databases
You can reduce processing time when querying a large number of records for
report generation.
Reporting can consume a lot of network resources causing problems for your
clients if you are not careful how you compile the necessary data. Use
this technique to reduce processing time when querying a large number of
records in your database for reporting. You will eliminate wait time and
headaches that could be caused by your database.
When building queries within Access with a multiple number of linked
tables and a variety of criteria, it can take some time to process,
especially on a network. The more tables you have linked causes more calls
to the data tables and more network traffic if you are sharing data.
Unfortunately this contradicts the goal of having a "normalized" data
structure and sharing the data in your tables with links. A data structure
that is "normalized" in simple terms, means that you only have any data in
your table set one time and that you share that data to any form or report
that needs the data through links. This is the most efficient structure
for your database and your users. You don't want your users trying to keep
data in separate forms or tables synchronized. The goal for your database
is to enter data once and share it everywhere. That also means that your
users are updating the data and it updates everywhere. This is important
to ensure that at the time any reporting is done that the data is up to
date for all users.
Now we discovered that having a database “normalized” was causing a huge
amount of network traffic when generating daily reports. This activity was
slowing down the application and every other user on the network as well.
One particular report was spiking the network server to the max for over
fifteen minutes while it compiled the data. The data needed for the report
came from twelve different tables linked together. Upon investigation we
found that the time to create the report was largely due to data traffic.
It appeared that the multiple links were causing the database to query the
data over and over causing the traffic.
Our solution to reduce the traffic and the time, was to create a temporary
table with reduced data and subsequently query from that table for any
reporting needed. This technique works well with Access, MySQL and SQL
Server databases as well.
For an example, you may be generating a productivity report where as many
as 100,000 plus records for the year are kept of all tasks completed. A
query linked to the tables containing the employee, tasks, department and
other data is needed to generate the report. Your goal is to report on
productivity data for the month of March for all employees by department.
First build the query to append to a temporary table (reducing the number
of tables from many to one) with all required tables and fields, selecting
only the date range for the month of March. This reduces the data and
reduces the number of transactions on the network at the same time.
Subsequently query from this temporary table to build reports by employee
with much greater speed and ease.
It has been our experience that large report queries (100,000 records
plus) will process the data you need many times faster using this
approach. We have seen report speeds improve as much as 15X using MS
Access and 20X using SQL Server over the internet. That is something your
users will certainly be happy about.
Keywords: MS Access, SQL, My SQL, SQL Server, reports, queries, query,
Access, database, speed, faster
Shade every other detail line in a Report
This article shows you how to shade every other detail line on a Microsoft
Access report.
Displaying or printing your longer reports with alternating background
colors can make it much easier for your users to read.
This
example requires that you have a little experience creating event code
with the Access VBA editor within Access.
An example database
is
included in Zip format. Just copy it to any place on your system and unzip
it before trying to open it. When you open the Client Table report you
will see alternating back color lines. The following example uses an
event procedure to alternate the background color between white and
yellow. On black and white printers, yellow typically appears as light
gray.
Setup
1. Create a new database and build or import a table named clienttable
Build the typical client data fields. The following field names work with
this example
[ClientID]
[ClientFName]
[ClientLName]
[ClientAddress]
[ClientCity]
[ClientState]
[ClientZip]
[ClientCounty]
[ClientPhone]
Populate the table with some data for testing.
2. Create a report using the clienttable. I used the report wizard and
created a report with tabular format. Make sure you create a detail
section either way. Put the fields from the clienttbl in the detail
section
3. Add a text box to the detail section of the report, and then set the
following properties:
Name:
LineNum
ControlSource: = -1
RunningSum: Over All
Visible: No
Back Style: normal
Set the back style for every field you put in the detail section of the
report,
Note the equal sign in the Control Source property setting.
4. Set the OnPrint property of the detail section to the following event
procedure of your report.
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Const WHITE = 16777215
Const YELLOW = 65535
Debug.Print (Me![LineNum] Mod 2)
If (Me![LineNum] Mod 2) = 0 Then
Me![ClientID].BackColor = YELLOW
Me![ClientFName].BackColor = YELLOW
Me![ClientLName].BackColor = YELLOW
Me![ClientAddress].BackColor = YELLOW
Me![ClientCity].BackColor = YELLOW
Me![ClientState].BackColor = YELLOW
Me![ClientZip].BackColor = YELLOW
Me![ClientCounty].BackColor = YELLOW
Me![ClientPhone].BackColor = YELLOW
Else
Me![ClientID].BackColor = WHITE
Me![ClientFName].BackColor = WHITE
Me![ClientLName].BackColor = WHITE
Me![ClientAddress].BackColor = WHITE
Me![ClientCity].BackColor = WHITE
Me![ClientState].BackColor = WHITE
Me![ClientZip].BackColor = WHITE
Me![ClientCounty].BackColor = WHITE
Me![ClientPhone].BackColor = WHITE
End If
End Sub
5. Close the report module.
6. Preview or print the report by clicking Print Preview or Print on the
File menu. If the lines don't alternate make sure you set the back style
to the fields in the detail section to normal.
As always there may be a better way to accomplish this, but this is quick
and simple. Note that the colors work where there is a field space on the
report. You can separate the fields and have a color space or run the
fields together to have a continuous line.
Success Story - Finding Thousands in Lost Revenue
The implementation of a Billing Analysis module written with Microsoft
Access saves this client in the Home Healthcare Services industry
thousands in potential lost revenue every month.
With as many as three thousand employees calling in hours worked from
patient homes our client needed a better way to match the time data with
their employee schedule. So, we developed an interface that took care of
pulling the telephony data and matching it to what caregiver needed to be
at what patient's home at what time. From that data, the telephony
interface built the time sheets needed to pay the employees and bill the
clients. That worked well if there was no human error and everyone called
in right on time. But, as you could expect, errors did happen and times
easily slid several minutes either way of the scheduled arrival/departure
times.
To eliminate those issues, Systems developed a Billing Analysis
module that catches time and billing sheet errors before they go to
accounting and billing. The module interfaces to the telephony system then
verifies that the caregiver is at the correct site and automatically
updates the hours worked when the caregiver calls in again to leave the
patient's home. The interface evaluates the scheduled caregiver, time and
location and enters in the correct pay record for the caregiver. It
handles time round offs, billing time vs. scheduled time and provides
reporting to help the company evaluate their scheduling practices. Round
off becomes a major concern when you consider that you are tracking three
thousand employees time and billing sheets. There could easily be several
thousand dollars in lost revenue if the inaccuracies are not corrected
ahead of time.
Since implemented, our client has discovered several opportunities to
improve their cash flow by drastically reducing human error, round off
accumulation errors, and missed time sheets from the process. These two
modules eliminated the need for hand written time sheets to be mailed in
from the field. I am sure you can imagine the problems with hand written
time sheets going through the mail. They took several days to get to the
home office then someone had to manually interpret handwriting and enter
the data.
The module took just about two weeks to develop so there was some
investment for our client but they are delighted they had us do it. They
discovered enough billing corrections to pay for the development the first
time they used it. The new module gives the management team a quick and
easy way to find erroneous data and human error and get it corrected.
Avoiding these mistakes saves them thousands in potential lost revenue
every payroll period.
In summary the Billing Analysis module completed the loop needed to deploy
an automated time sheet system based upon a telephony interface and
practically eliminated human error while cutting days out of the payroll
process and recovering revenue that would have been lost forever.
What could Systems do for you?
Please give us your feedback. We need to know if these examples are meeting anyone's needs and if you are using them. We also want your suggestions for new tips. Also, if you want the latest new tips in your email box first then please provide the information below.
The Management.