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.

Archive for the ‘Microsoft Access General Stuff’ Category

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 »

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 »

How should we be using Microsoft Access?

Monday, November 30th, 2009

Microsoft Access is a desktop database which is best suited to small applications with a small number of users. Using Microsoft Access for anything more and it may outgrow our database needs.

If the system is to be used by an individual or a small office then Access is an ideal tool. Try not to use it over a wide area network. Security and stability issues can occur if this is attempted. Also data retrieval will be slow over such a wide network.

We can certainly create some powerful small systems. MS Access is also a great tool for prototyping larger systems. We can put together screens and tables very quickly in Access and this allows us to get an idea of how a system will function. A kind of blueprint is sketched out by prototyping.

When using Microsoft Access we should also be thinking about how we can make use of the other components within the Microsoft Office suite. The great thing about the MS Office suite is that all components can communicate with each other. We could use MS Access to send an email via MS Outlook. We could also be using Microsoft Access to output sales figures to MS Excel or vice versa.

There are certainly many benefits of using Microsoft Access and it is worth thinking about what the best way forward is.

Tags:
Posted in Microsoft Access General Stuff | Comments Off

Access To SQL Server

Tuesday, November 17th, 2009

While Access is great for small databases it is not so good when you have many users.
Say 50 or more. It may be worth thinking about moving Access To SQL Server.

Some companies may use MS Office an create small databases, but they may also have
the need for a more robust, stable database that can handle hundreds of users.
In this case they may use what is known as SQL Server. Like MS Access it is a
relational database.

Imagine a database system that does not include forms. Well, that gives you an idea of
what SQL Server is. You cannot use it to design fancy screens as it does not include a
form generator. This is a consideration when moving from Access To SQL Server.

The main SQL Server screen.

access to sql server1

Comparison with Access…

access to sql server2

SQL Server allows you to create database servers and store hundreds of databases on
it. It uses a language called SQL (pronounced sequel) which essentially pulls data from
the databases based on your criteria. SQL stands for structured query language and is
also used in Access, although not as much as SQL Server.

access to sql server3

Stored procedures are sets of SQL commands that have been compiled and stored
within SQL Server. The Access equivalent are queries.

SQL Server has a query designer which looks very much like the one in Access. It is
used to create what are known as ‘Views’.

access to sql server4

SQL Server also has much stronger security than Access. Permissions can be set on
tables, stored procedures and on the database in general. In this example it shows the
different permissions allowed on objects such as stored procedures (queries).

access to sql server5

SQL Server tables look similar to their Access equivalent.

access to sql server6

In SQL Server you can schedule jobs – for example you may wish to have sales data
loaded automatically into a table each morning.

Unlike Access, SQL Server can store a lot more data in it’s tables. The banking industry
are known to use SQL Server and you can imagine the volume of data they must be
dealing with. No way could a desktop database like Access cope with such demands.

Data demand presents a good reason to move Access To SQL Server.

Access is more of a rapid development tool, great at putting together databases quickly.
It is also good for prototyping and getting an idea of what a larger SQL Server system
could look like. Access can connect to a SQL Server database and manipulate data in
it’s tables. It is common to develop screens in Access and then connect to the tables in
SQL Server.

There is a big gulf in price between SQL Server and Access, although there is a cut
down free version also available now. Think about whether you are ready to move Access To SQL Server.

It is always a case of thinking “What is the right tool for this job?”……if you are not
dealing with many users and don’t require heavy volumes of data or tight security, then a
desktop database like Access is ideal.

Posted in Microsoft Access General Stuff | No Comments »

MS Access SQL Server

Tuesday, November 17th, 2009

Access is great for desktop databases with five to ten users. Anything more and it
can become unstable. Stability is also affected by the volume of data held in tables.
Corruption can occur resulting in the database having to be repaired or in some
cases lost altogether. You may wish to compare MS Access SQL Server.

SQL Server is another Microsoft product and offers better security. It also has the
ability to handle massive volumes of data, hundreds of users and has all round better
stability.

SQL Server is not a development tool. It cannot be used to create data entry
screens.

Fortunately there is a way of getting the best of both worlds here. We can use
Access for creating the nice screens and SQL Server to hold our data.

I will show you how.

In Access we need to create an Access Project (ADP).

Go to the ‘File’ menu and select ‘New’.

ms access sql server1

You will see a list of options.

Select ‘Project using new data’.

ms access sql server2

Now select a location to save and give the project a name.

ms access sql server3

The SQL Server connection wizard will be shown.

Select your SQL Server from the dropdown box.

If your SQL Server is stored on your local hard drive then enter (local).

ms access sql server4

If you want to login using your Windows credentials then tick the box ‘Use Trusted
Connection’.

You can also give the database a name. I like to use the same name as my Access
project.

Click ‘Next’.

Excellent so far!

Now if you switch to SQL Server you will see your database.

ms access sql server5

Note: You may need to refresh the server list before it shows.

Now switch back to Access and create a new table.

Notice the data types look different. Well they are because Access is using the SQL
Server engine.

ms access sql server6

Create some new fields and save the table.

ms access sql server7

Again switch back to SQL Server and refresh the table list of the database.

You will see the table you just created in Access.

We now have a situation of MS Access SQL Server.

If you switch back to Access and add data to the table you will find that also shows
up in your SQL Server table.

In Access you can go ahead and create forms in the normal way. You can also
create the equivalent of queries – views or stored procedures.

SQL Server is a robust database, but is not as easy as Access to use. It does however offer far better stability than Access. Thankfully Access users can now connect to SQL Server and offer scalable solutions.

With the change of MS Access SQL Server, you get the robustness of SQL Server with the rapid design development of Access.

Posted in Microsoft Access General Stuff | No Comments »

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 »

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