Access Database Systems

WelcomeBlog

Are your business objectives being met?

Do you need specialised Access database consulting or an Access database solution? Our software is dedicated to providing assistance toward increasing efficiency and productivity of the small or medium sized business.

If you do not have the resources or time to develop software for your business, you may find something of interest here.

Posts Tagged ‘Microsoft Access’

MS Access Macro – How To Open a Report

Wednesday, January 20th, 2010

An MS Access macro is a great tool for automating our databases. We can use Microsoft Access macro actions for many tasks.

In this demonstration I am going to open a report by using a macro.

The MS Access macro designer has three main columns
1. Macro Name
2. Action
3. Comment

Enter the name of the new MS Access macro in the ‘Macro Name’ column.
Now move to the ‘Action’ column and select the ‘OpenReport’ action.
Look towards the bottom of the screen and you will see the action arguments section. Enter the name of the report in the report name section. In the view section select the view you wish to see the report in. In this example I want to see it in print preview mode.

Now save the MS Access macro and close the designer window.

Create a brand new form in design view.
Now make sure the wizard button is clicked as you want to use a wizard for the next step.
From the toolbox control area you want to click the command button control.
Drop the button onto the form.
The wizard will now load and you will see two columns – categories and actions.
Select from the categories box the option called miscellaneous.
From the actions box select the run macro action.
Click the ‘Next’ button and on the next screen select the macro that you saved earlier.
Click the ‘Next’ button and on the next screen add text to the button.
Click the finish button.

Now run the form and click your command button.
The Microsoft Access macro will run and your report will open in print preview mode.

The MS Access macro makes our life so much easier. We don’t need to learn complex programming code to add power to the database.

To further your learning of the MS Access macro check out this great new MS Access macro ebook.

Tags: , , ,
Posted in The MS Access Macro | 3 Comments »

Working with Microsoft Access databases

Sunday, January 3rd, 2010

One should always use the right tool for the right job. If you want to store large volumes of data and have many users then think if Microsoft Access databases are the best way to go.

Microsoft Access databases tend to be better suited to small systems with around ten or less users. For anything more substantial you are better looking at something more robust such as SQL Server.

When working with Microsoft Access databases which have a small number of users you should aim to put one copy of the database on the network. This database should contain only the tables.

Each user should have a copy of the database on their PC with everything in it except the tables. Each of the users will have a connection to the database sitting on the network. This will speed things up a little and also can help to prevent corruption or system crashes.

Microsoft Access databases have always suffered from stability problems, but over the years with each new release there has been an improvement.

Another option is to have Microsoft Access databases connect to SQL Server. The database table will come from SQL Server and the forms, queries etc will all be in MS Access.

Not only does SQL Server offer better stability, it can also handle larger volumes of data and has better security.

Whatever solution you choose to implement it is worth planning out your system and taking into account expected data volume. Also consider how expandable the system is and whether the number of users is likely to grow by much in the future.

Tags: , , , ,
Posted in Microsoft Access General Stuff | No Comments »

Advantages of using a Microsoft Access Database

Sunday, January 3rd, 2010

In today’s information age we are becoming less reliant on paper. When keeping records, be it household or business, it is a good idea to keep our information using some kind of computer solution. A Microsoft Access Database provides the perfect solution to our problems.

There are different approaches we can take. We could look at entering our information into a spreadsheet such as Microsoft Excel. Many people keep their household bills and receipts in a spreadsheet.

Although there is nothing wrong with using a spreadsheet for record keeping, this is not always the best solution. A spreadsheet is mainly used for accounting purposes where we need to perform calculations on data.

By far a better solution would be to keep our information in a database. There are many database solutions on the market, but by far the most popular is a Microsoft Access Database.

Microsoft Access comes as part of the Microsoft Office suite of programs and globally there are literally millions of businesses and other types of users who make good use of it.

How is our data stored? Microsoft Access tables are used to hold information and these tables can be related together. For instance a customer table can be related to a sales table. Likewise there is a relationship between a student table and a courses table.

A Microsoft Access Database can also pull information out in seconds. This of course will depend on the amount of information held in the database, but generally information retrieval is fast.

To retrieve information from a Microsoft Access Database you would use what is known as a query. Think how long it would take if you were to shift through pieces of paper to find the information you were looking for.

Look at what a Microsoft Access Database can do for you or your business. You may be pleasantly surprised at the benefits.

Tags: , ,
Posted in Microsoft Access General Stuff | 1 Comment »

Discovering the Microsoft Office Access database

Sunday, January 3rd, 2010

Microsoft Office has been around for many years and includes at its core Access, Word, Excel, PowerPoint and Outlook. Microsoft Office is the world’s most popular office suite and is used on millions of PC desktops.

The application used for storing and retrieving large volumes of data is the Microsoft Office Access database. Previously it was called Access, but this changed with Access 2007 and it now has the name ‘Office Access’. There are good alternatives on the market now, but Microsoft Office Access is still the leader.

The great thing about applications in the MS Office suite is that they can all talk to each other and this is no different with the Microsoft Office Access database. You may find you want to transfer records from your Microsoft Office Access database into Microsoft Excel. Because these packages can communicate this task is easily achievable.

You may also find you want to create mailing labels in Microsoft Word for example. You could use the Microsoft Office Access database to transfer its records into MS Word which would then use them to merge into mailing labels.

Some considerable changes have been made to the Microsoft Office Access database. It now has a new look interface for instance. Also the ability to set up users and groups is no longer there. Security is now mainly done via a password.

Creating forms in the new Microsoft Office Access database has become easier and there are now different form options. One example is the split form.

The Microsoft Office Access database is a great addition to the MS Office suite and delivers on both power and user friendliness.

Tags: , ,
Posted in Microsoft Access 2007 | 1 Comment »

What is a database?

Saturday, February 7th, 2009

A database is a collection of information. A Microsoft Access database provides a mechanism for storing, retrieving and manipulating that information.

Tags: , , , ,
Posted in Microsoft Access General Stuff | No Comments »

How To Add A Spellchecker To An Access Database Form

Friday, January 30th, 2009

When entering data one can occasionally misspell a word or two. We are all used to having a spell checker at hand when typing a Word document for instance.

Why should a spell check be limited to Word documents? Surely it would be of benefit if we could incorporate something similar in an Access database.

Well, I have some good news. We can do exactly that!

Create a form with a text box or memo field.

Enter some text into either of these controls.

Create a command button on your form.

Go into the properties of the button and set the caption to be ‘Spell Check’.

Right click the command button and select ‘Build event’.

Between the Private Sub and the End Sub enter the following code

You can copy and paste it if you like

With Me![My control name]
If Len(.Value) > 0 Then
DoCmd.SetWarnings False
.SetFocus
.SelStart = 1
.SelLength = Len(.Value)
DoCmd.RunCommand acCmdSpelling
.SelLength = 0
DoCmd.SetWarnings True
End If
End With

Replace the words inside the square brackets [My control name] with the name of your own control that you want to spell check.

Save the code and close the code window.

Now run your form and click the button.

Enter some misspelled text in your control and then click the spell check button.

You will find you get a similar spelling suggestion box that you have seen many times before when writing Word documents.

Let’s look at what’s actually happening in this code.

Don’t worry too much about understanding it – I will cover programming in further tutorials. This will give you a general idea though.

I want you to spell check this control

With Me![My control name]

If there is something in that control then go ahead and spell check it

If Len(.Value) > 0  Then

Turn all warning messages off for now

DoCmd.SetWarnings False

Focus to the control I want to spell check

.SetFocus

Start at the first character

.SelStart = 1

And continue to the last character of the word

.SelLength = Len(.Value)

Now run the spell check

DoCmd.RunCommand acCmdSpelling

Clear the variable used for the length of the word ready for the next spell check

.SelLength = 0

Turn all warning messages back on

DoCmd.SetWarnings True

Tags: , , ,
Posted in Microsoft Access How To | No Comments »

Why Use An Access Database?

Friday, January 30th, 2009

In today’s ever changing world it is important a business has the correct tools to improve efficiency. If you are a small to medium sized business you may find you are not being as productive in some areas as you would like. There are many aspects of a business that can be automated…..it could be that you do not have the time, know how or resources to do this.

It’s possible you could go to a local computer company and get this work done or buy an off the shelf package. However you may find the product you got is stuffed full of features you did not ask for or does not fully meet the needs of the initial requirement. Can you correct the mistakes yourself?…..not if you do not have access to the source code. This may be locked down by the person who created the product and the only way you can get an amendment done is by going back to him/her and possibly even paying a fee.

What type of software should you be using? There are no hard and fast rules here, but it is safe to say that Microsoft Office is running on millions of computers in business’s world-wide. Why can this be a good thing? Well for one all the applications in the Office suite can communicate with each other…for example you can have a Microsoft Access database talk to a Microsoft Word application to product an invoice. The data will be pulled from the Access database and displayed on the Word invoice. This is just one example – there are countless others, but as you can gather the possibilities are interesting. If you are using tools from different companies it can be difficult for applications to communicate with each other. With the Microsoft Office suite of programs you have a set of related components which can certainly improve business efficiency.

There is nothing wrong with a stand alone database which does nothing other than store and display records. However, it would be so much better if that database could communicate with an Excel spreadsheet. The data could be scheduled to output to the spreadsheet in the early hours of the morning and the information would be available to managers first thing in the morning. How about the spreadsheet then being automatically emailed via MS Outlook to other people inside and outside the organisation…..the possibilities are endless with this approach.

It is not always necessary to keep up to date with the latest trends. If you find the version of your software meets your current needs then it is better to stick with it, while keeping an eye on the latest developments in the software world.

Tags: , ,
Posted in Microsoft Access General Stuff | No Comments »

Access Database Versions

Monday, December 15th, 2008

Over the years MS Access has not changed much in terms of it’s interface and the basic building blocks it contains. I started off using Access version 2. This was a good version in many respects, but was prone to being unstable when handling large amounts of data or users. The Jet database engine has always suffered from these problems. You could still accomplish a lot and build rather nice systems.

Access version 2 was the version that really established Access as the new great tool on the block. Competitors around at the time were Paradox, DBase, Dataease, Lotus Approach etc. MS Access had the big name of Microsoft behind, was part of the MS Office suite and quickly established itself in the marketplace. Many companies advertised for Access developers to develop systems.

Access 95 was a version that seemed to come and go. It was part of Office 95 and was not seen as being much of an improvement on Access 2.

Access 97 came next and this was a vast improvement on all previous versions. In fact this was my favourite version so far. Even though I have Access 2000 I feel more comfortable using Access 97. I find it less bloated and contains only what I need. It was certainly more stable than Access 2.

Access 2000 is a good robust version that carries on and enhances the great Access legacy. I found it to be faster than previous versions. It introduced the ability to write native SQL queries and pass them through to a more professional database such as SQL Server or Oracle.

Access 2003 – again not much has changed. It seems slightly buggy, but no doubt the service packs have overcome any issues.

Access 2007 – I have only started to look at this and my initial impressions are not favorable. The file format is no longer MDB and the interface is a complete overhaul. I believe it is too much too soon. For anyone new to an Access Database it will pose few problems, but for others who have used previous versions it will take some getting used to.

There is no more database window  – it has been replaced by what is known as the ‘Navigation Pane’.

The menu bars are now replaced by the new MS Office standard of a Ribbon. You will either love or hate this at first.

There are some nice touches though. I particularly like the fact that you no longer have to worry about creating or implementing a calendar. Simply create a date field in your table and Access will create a small calendar icon next to it on your form. When you click this you will get a pop up calendar. No coding needed.

Security is no more. This may or may not be an issue. Security was always a problem in previous versions and an Access Database could never really be defined as being secure. You could connect to a secured Access Database from another database system. Not only that, but implementing what security there was could never be described as straightforward.

If you want a secure Access 2007 database now it is recommended you move to SQL Server Express or higher.

The Access Database has come a long way since it first took the world by storm in 1992.

1992 Access 1.1
1993 Access 2.0
1995 Access 95
1997 Access 97
1999 Access 2000
2001 Access 2002
2003 Access 2003
2007 Access 2007

Tags: , , ,
Posted in Microsoft Access General Stuff | Comments Off

Microsoft Access 2007

Tuesday, December 9th, 2008

Microsoft Access is alive and kicking and is available in it’s latest incarnation “Access 2007″.
It is unclear just how many companies have switched to it or Office 2007 in general, but I suspect the take up is slow.

Personally I am not keen on Access 2007 because it seems too much of a radical change from what we have been used to in the past. No doubt the introduction of the ribbon type toolbar is the way forward for MS Office products, but it can be frustrating to use.

It is not clear where the save button is for instance. Where is the undo command? Others have told me they don’t like the new security model or lack of one. I probably need to explore Access 2007 more and get used to it all though.

HOW ARE YOU USING ACCESS IN YOUR BUSINESS?

I find it amazing that some companies are still using or supporting application written in Access 2.0. If budgets allow you should aim to upgrade to a newer version as Access 2.0 was never the most reliable of versions.

USE THE RIGHT TOOL FOR THE RIGHT JOB

If you need to perform calculations and do forecasting, use Excel not Access. If your database needs to cater for more than 5 or so users or even if it will be handling large volumes of information, consider using something such as SQL Server. Don’t try to make Access do too many things and stuff it with form after form. Keep your Access databases light and simple.

PLAN OUT WHAT YOU WANT YOUR SYSTEM TO DO

If you get it right or near enough from the beginning then it will pay dividends for you in the long run.
Here is how I plan out a system

Tags: , , , ,
Posted in Microsoft Access 2007 | No Comments »