So You Want To Upsize Your Access Databases?


1: Questions and other points to consider

2: Ways to upsize your back end databases

3: Ways to upsize your front end database applications

4: Reasons to upsize

1: Questions to ask about your current systems

o Does your company have many Microsoft access databases or just one or two critical ones

o Do you have a big budget or little one?

o Do you want a successful implementation?

o Do your business users want little changes or huge ones?

And finally …..

o How good is the data and the structure of the data?

There are many ways to upsize your access databases or migrating the data and or access application to another system. This article discusses all the different options plus shows how a combination of methods is often more successful and less expensive than the “let’s replace everything” approach.

Important considerationsIs the data in a separate database from the Application database? Firstly, if your access databases aren’t split so that you have one database for your data ( the back end) and one database for your application ( the front end system) then this should be done immediately.

Does your database structure need changing? Are there are separate tables for your customer details and your customer records? Often a system starts out with a simple table and details like addresses are repeated with each record. Normalising your data is a good practice if you are giving your system a good overhaul.

Is your data accurate? Often one starts investigating the database and finds that there is missing, duplicated and incorrect data. Fixing this is often a time consuming process and often the whole upgrade is avoided because of this. One can follow the 90 / 10 rule which states that only 10% of your data is used and needs fixing immediately. The other 90% can be done later…… That way you can implement a “better” system in a shorter space of time.

Are parts of your current access applications not used? Often many parts aren’t used because they weren’t “finished” or just weren’t what the business wanted. Firstly find out what isn’t used as these parts should be eliminated from the upgrade/upsize so they aren’t raised as issues later!

Are you running Microsoft SQL Server? You can upsize to Microsoft SQL Server 2005 Express the free but limited version of Microsoft SQL Server 2005, but if you have the workgroup, standard or enterprise version of Microsoft SQL Server 2000/2005 or 2008 then find out if you can migrate the backend databases to this.

Does your company require internet access to any of your data contained in these access databases? If you answered yes to this, why not look at using Microsoft Windows SharePoint Services and/or Microsoft SQL Server Reporting Services for part of your system. SharePoint can be used for commonly accessed “lists” of up to 2000 records. These lists can be edited depending on one’s permissions and is an easy way to upsize some of your tables in your backend system. Microsoft SQL Server Reporting Services allows secure reporting against your access databases, SharePoint Lists and SQL Server Databases.

Do you want activity-based workflows? Again SharePoint is an option for part of your system. Activity based workflow is stressed hear if you want out of the box stuff. When an item in a list is updated, then a workflow can be fired to do something like send and email or update another value.

Is Security and reliability of data important? Access databases aren’t as secure as Microsoft SQL Server Databases or as reliable.

2: To upsize/replace your Microsoft Access Backend: This involves placing your Microsoft Access Backend tables into Microsoft SQL Server 2000/2005/2008 or into Windows SharePoint Services Lists.

a) Microsoft Access to Microsoft SQL Server One might try importing all tables into SQL Server and then re-linking. This works but often with some performance loss. Use the Microsoft Access Upsizing Wizard from the tools menu in Microsoft Access as this will help to create your relationships, primary and default values.

Now is the time to re-think your Access Application. Often a form opens up all the records. Change to a search form which clicks to open one record for editing. Then you can use pass through queries in the search form. This means SQL Server does most of the work including sorting etc. Your users will be delighted with the difference in speed. You should also look at converting queries into stored procedures as well as functions used in your queries moved to User Defined Functions in SQL Server.

b) Microsoft Access to Windows SharePoint Services – If you are using Microsoft Access 2007, there is a neat little export to SharePoint feature. If you have tables that are lookups or contain less than 2,000 records, then this is a option. You then have linked lists inside your Microsoft Access 2007 application, similar to Linked Tables in SQL Server. If there are some tables with less than 2,000 you might want to migrate those and migrate the bigger tables into SQL Server

c) Microsoft Access to Microsoft SQL Server Express. SQL Server 2005 Express edition is very powerful. The old rule of thumb was to migrate to this if you had more than 20 users or millions of records in a table. Whilst this is free, you need a developer with the developer version of Microsoft SQL Server to create your database and upload your data. There are also lots of other features you will only find in the workgroup/standard and enterprise versions that you might want to consider like the business intelligence tools or scheduled maintenance plans to ensure ease of backup and restore. So as time = money you will often find this free version can cost you more than having one of the other editions of SQL Server. The database is limited to 4GB and the RAM to 1GB but this is quite ample for most applications but generally if your company is big enough for a bigger system, it will already have SQL Server Workgroup/Standard or Enterprise.

3: To upsize/replace your Microsoft Access front end: Please keep an open mind unless you have unlimited budget and time and don’t care about your end users! This might involve upgrading the version of Microsoft Access. Maybe even using the run-time version to puss out Microsoft Access 2007. One can also use SharePoint and/or Reporting Services for parts of this upgrade and still use Microsoft Access for the administration functions. You could go all the way to a .Net application or just do certain parts in .Net. This is where many die hard .net developers will just say drop access and go to .Net for the front end. But if something works fine then why change it? In other words, don’t go to .Net unless Access cannot do what you want. And if there is only a small part of extra functionality required like internet access, then do that part in .Net. The worst thing one can do is to replace everything. You don’t rebuild a house when you decide you need a new oven. Access is a very good rapid development application in the right hands, and is also a very good way to prototype .net applications. Say you want only a few internet accessible forms and reports, then just do that part in .net as Internet forms generally run a little slower and can really annoy users used to faster and more flexible forms in Access. Enough said, now down to the different pros and cons of the front ends you can upgrade or migrate to.

a) Microsoft Access to Microsoft SQL Server Reporting Services – Reports can be imported from Microsoft Access into Microsoft SQL Server Reporting Services. More often not you will want to re-create them as you create all sorts of parameter driven (e.g. Select Year and State) reports. You can also create drill down and drill through reports plus have report subscriptions which email or save reports on a scheduled time frame. e.g.Daily or at the beginning of the month. Scheduled Reports can be saved in Excel, Web or PDF format to either a directory or SharePoint Document Library. Often just adding this to your existing application provides enough internet accessibility for your company. E.g. For sales people to check their sales.

b) Microsoft Access to Windows SharePoint Services – By migrating some of your lists to SharePoint you can internet enable some of your company information. You can then access your information either via internet explorer, or Microsoft Access 2007. Often you will want to migrate only a few lists in order to add workflow and/or internet accessibility. You can also create web parts to access the larger tables ( over 2,000 records ) and therefore be able to edit records via these web parts. This is done with .net coding as discussed below.

c) Microsoft Access to .Net – .Net means many things, but normally it is used to internet enable an application by using techniques. One can then wrap these in Web Parts and show them on your intranet via SharePoint at a later stage. Internet enabling an application this way often means a total re-write of your application. This is why one should approach this carefully as it often means reduced functionality from access. E.g. in Access one can send queries straight to Excel which most business users prefer. This needs to be built at a cost into an Asp.Net application as it isn’t a standard feature.

4: Rationalizing whether to upsize your Microsoft Access Database to Microsoft SQL Server. Now you have read the above you might be asking how to justify the cost of upsizing Microsoft Access to SQL Server?

o More reliable data ( less corruption when you have multiple users)

o Better Security (the database file with the data cannot just be copied as easily)

o Ability to schedule backups

o Enhanced reporting features ( see the reporting services features )

o Better Performance

Summing this up –> increasing the productivity of your business users plus making your data more reliable and secure.


Source by Tom Bizannes

Close Menu