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 ‘Access Database’

« Older Entries

Creating a VBA Access database

Wednesday, February 10th, 2010

You can create powerful Access databases with no programming code at all. There may be a situation though when you do need some specialized functionality and then you will have to create a VBA Access database.

The programming language used in all the MS Office products is VBA which stands for Visual Basic for Applications.

How do we go about this? It really depends on the type of functionality you require, but chances are you will find yourself creating sub procedures and functions. This will hold the main code of your program.  Code can be put behind the forms itself or in code modules.

There is a bit of a learning curve however when it comes to creating a VBA Access database. It takes time to become confident and proficient in a programming language.

You can also get proficient in the MS Access macro language which will give you a start in how logical statements flow. MS Access macros are essentially VBA Access statements, but are much easier to use.

If you feel confident to attempt a VBA Access database then you should, otherwise enrol in a good training course. There are also plenty of good books on the MS Access VBA language.

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

MS Access 2007 help

Saturday, February 6th, 2010

MS Access 2007 is the version of Access that changed everything. The interface is like no previous version and takes some getting used to. Instead of the classic toolbars and menus we now have what is known as ‘The Ribbon.

It is easier to now create basic forms and reports. Unfortunately there is not a lot of MS Access 2007 help available at this stage and it is important to get the correct training as soon as possible.

How much MS Access 2007 help needed will vary from person to person. Some need more training than others. We all learn differently and there are aspects of MS Access which are complicated.

Take for example database relationships – these are the backbone of any good MS Access database and it is useful to have a good understanding.

On this site I have created a great MS Access 2007 help product. Students have got great value from it and now it is available online or on DVD.

Tags: , , , ,
Posted in Microsoft Access 2007, Microsoft Access General Stuff, Microsoft Access How To | 3 Comments »

How to use the autoexec macro

Monday, February 1st, 2010

The autoexec macro is used to run actions when the database is opening. For example, you may wish to perform a data update before the system becomes active. You can do a similar thing with the start up options, but the autoexec macro will allow you to run more than one action.

Lets look at how we can use the autoexec Access macro to open a form and move to a blank record for entering data.

Load up a database.

Go into the macro designer.

In Access 2007 at the Ribbon you would click on the create tab.

Then click on the macro icon at the far right hand side.

In the action column select the ‘OpenForm’ action.

MS Access Autoexec Macro

At the bottom of the screen you will see an area for action arguments.

Select the name of the form to open

Make sure the view says form.

Now select another action of ‘GoToRecord’.

At the bottom of the screen you will see an area for action arguments.

Select an object type of form.

The object name will be the form you are opening.

Set record to be new.

That’s it…..

Now just save the macro with the name of ‘Autoexec’.

Close the macro designer window.

Exit the database.

Now run the database.

The autoexec Access macro will immediately kick in and you will be taken to a brand new record in the form you specified.

The autoexec macro enables us to control what we want to take place when the database is opening.

Click here MS Access macro if you want to learn more about macros.

Tags: , ,
Posted in The MS Access Macro | 1 Comment »

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 »

Using Microsoft Access database queries

Sunday, January 3rd, 2010

Entering data in a database is done through tables or forms, but when we want to get our data out we should use Microsoft Access database queries.

A Microsoft Access database query can produce a complete or a subset of data. You can also choose to restrict the data returned based on criteria you feed in. For instance you could tell the Microsoft Access database query to return all sales figures in Australia for the final quarter.

A Microsoft Access database query can return data quickly depending on the volume of data it has to crawl through. When you just want to return information you would use what is known as a select query.

Besides a select query there are also action queries. Examples of action queries are update, delete, append and make table queries.

A Microsoft Access database query can include calculation columns. You can create a temporary column that holds a calculation as part of the results set. You may have a column with a quantity and another that holds a unit price. Your temporary column could contain a calculation to multiply unit price by the quantity.

Another great thing about a Microsoft Access database query is that it can be used to clean up data. Say for instance you have thousands of records and you wish to update all or certain records to start with a capital letter. You could do this instantly with a query.

Microsoft Access database queries are an important part of any good database system. Look at how you can put them to good use in your system.

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

Microsoft Access 2007 for dummies

Sunday, January 3rd, 2010

The world’s most popular desktop database, Microsoft Access has seen many versions. The latest is known as Office Access 2007 and is the most radical release yet. It offers a wide range of features for beginners and seasoned users alike. There are many books on the market including “Microsoft Access 2007 for dummies” which will aid understanding of this powerful database software.

Some may take offence at being referred to as a dummy however and may seek out alternative means to learn. I believe this is a wrong attitude to take. It’s just a title after all and a catchy one at that.

Although MS Access is easy enough for beginners to start creating databases, there is more to consider if you want to build powerful systems. Sometimes a simple form is not going to cut it and you may soon find you want to add more features. How will a book help? It will present database theory and will certainly aid understanding, but is no substitute to having a tutor talking to and guiding you.

I have to admit to using dummies books myself in the past and have found them to be very informative. I recall starting off with both the MS Dos and Windows dummies books and learning a great deal this way. The books are fun to read and make the subject entertaining.

Lets face it, many of us dread having to learn new things as we get older and technology for some proves difficult. I have not used Microsoft Access 2007 for dummies, but if it is as good as other books in the series then it will certainly benefit the reader.

We always learn by doing. Sure it is possible to read endless text on a subject, but it all comes down to taking action and putting into practice what we have learnt. Mistakes will be made, but that is always a big part of the learning process. An Access database is no different to any other subject. It takes time and effort to build good systems.

The best way to learn is to pick a tutor that breaks the subject down into its easiest format. It should start at the begininng and should not overwhelm the pupil with computer jargon and theory. Whether you use a course or a book, seeing your Access database results take shape can be a pleasure in itself.

Tags: , , ,
Posted in Microsoft Access 2007 | 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 »

« Older Entries