Access: building ‘upsizable’ applications.

Wednesday, April 1, 2009

Microsoft Access When you start building an Access application, it’s tempting to just think about today’s problem and not worry at all about the future.
If your application is successful, people will want more out of it and, over time, you’ll be faced with the task of moving the back-end database to a more robust system like SQL Server.

While there are tools like SSMA that can help you move an Access database to SQL Server, a lot of the problems you’ll encounter can be solved before you even have to think about upsizing.
Abiding by a few simple rules will cost you nothing when creating your Access application but will save you a lot of headache if -when- the time comes to upsize.

So here are a few things to keep in mind.

Naming conventions

Access is pretty liberal about naming conventions and it will let you freely name your tables, columns indexes and queries. When these get moved to another database you’ll most probably be faced with having to rename them.
In some cases, you could actually create subtle bugs because something that used to work fine in Access may be tolerated in the new database but be interpreted differently.

  • Do not use spaces or special characters in your data object names.
    Stick to characters in the range A through Z, 0 to 9 with maybe underscores _ somewhere in between (but not at the start or the end).
    Also try to respect casing wherever you reference this name (especially for databases like MySQL which are case-sensitive if the hosted on a Linux platform for instance).
    eg:
    Customer Order Lines (archive) should be CustomerOrderLines_Archive.
    Query for last Year's Turnover should be QueryLastYearTurnover.
    Index ID+OrderDate should become instead ID_OrderDate.

  • Do not use keywords that are reserved or might mean something else whether they are SQL keywords or functions names:
    A column called Date could be renamed PurchaseDate for instance.
    Similarly, OrderBy could be renamed SortBy or PurchaseBy instead, depending on the context of Order.
    Failing to do so may not generate errors but could result in weird and difficult to debug behaviour.

  • Do not prefix tables with Sys, USys, MSys or a tilde ~.
    Access has its own internal system tables starting with these prefixes and it’s best to stay away from these.
    When a table is deleted, Access will often keep it around temporarily and it will have a tilde as its prefix.

  • Do not prefix Queries with a tilde ~.
    Access use the tilde to prefix the hidden queries kept internally as recordsource for controls and forms.

Database design

  • Always use Primary keys.
    Always have a non-null primary key column in every table.
    All my tables have an autonumber column called ID. Using an automatically generated column ID guarantees that each record in a table can be uniquely identified.
    It’s a painless way to ensure a minimum level of data integrity.

  • Do not use complex multivalue columns.
    Access 2007 introduced complex columns that can record multiple values.
    They are in fact fields that return whole recordset objects instead of simple scalar values. Of course, this being an Access 2007 only feature, it’s not compatible with any other database. Just don’t use it, however tempting and convenient it might be.
    Instead use a table to record Many-To-Many relationships between 2 tables or use a simple lookup to record lists of choices in a text field itself if you’re only dealing with a very limited range of multivalues that do not change.

  • Do not use the Hyperlink data type.
    Another Access exclusive that isn’t available in other databases.

  • Be careful about field lookups.
    When you create Table columns, Access allows you to define lookup values from other tables or lists of values.
    If you manually input a list of values to be presented to the user, these won’t get transferred when upsizing to SQL Server.
    To avoid having to maintain these lookup lists all over your app, you could create small tables for them and use them as lookup instead; that way you only need to maintain a single list of lookup values.

  • Be careful about your dates.
    Access date range is much larger than SQL Server.
    This has 2 side-effects:
    1) if your software has to deal with dates outside the range, you’ll end-up with errors.
    2) if your users are entering dates manually, they could have made mistakes when entering the year (like 09 instead of 2009).
    Ensure that user-entered dates are valid for your application.

VBA

While most of your code will work fine, there are a few traps that will bomb your application or result in weird errors:

  • Always explicitly specify options when opening recordsets or executing SQL.
    With SQL Server, the dbSeeChange is mandatory whenever you open a recordset for update.
    I recommend using dbFailOnError as well as it will ensure that the changes are rolled back if an error occurs.

    Dim rs as DAO.RecordSet
    ' Open for read/write
    set rs = db.OpenRecordSet("Stock", dbOpenDynaset, dbSeechanges + dbFailOnError)
    ' Open for read only
    set rs = db.OpenRecordSet("Stock", dbOpenSnapshot)
    ' Direct SQL execution
    CurrentDB.Execute "INSERT INTO ...",  dbSeeChanges + dbFailOnError
    
  • Get the new autonumbered ID after updating the record.
    In Access, autonumbered fields are set as soon as the record is added even if it hasn’t been saved yet.
    That doesn’t work for SQL Server as autonumbered IDs are only visible after the records have been saved.

    ' Works for Access tables only
    ' We can get the new autonumber ID as soon as the record is inserted
    rs.AddNew
    mynewid = rs!ID
    ...
    rs.Update
    ' Works for ODBC and Access tables alike
    ' We get the new autonumber ID after the record has been updated
    rs.AddNew
    ...
    rs.Update
    rs.Move 0, rs.LastModified
    mynewid = rs!ID
    
  • Never rely on the type of your primary key.
    This is more of a recommendation but if you use an autonumbered ID as your primary key, don’t rely in your code or you queries on the fact that it is a long integer.
    This can become important if you ever need to upsize to a replicated database and need to transform your number IDs into GUID.
    Just use a Variant instead.

Parting thoughts

These simple rules will not solve all your problems but they will certainly reduce the number of issues you’ll be faced with when upsizing you Access application.
Using a tool like SSMA to upsize will then be fairly painless.

If you have other recommendations, please don’t hesitate to leave them in the comments, I’ll regularly update this article to included them.

References

Entry Filed under  :  Database,MSAccess,Programming,SQL Server

Leave a Comment

(Will not be shown)
Notify me of follow-up comments via e-mail

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed


about

Renaud This is a simple technical weblog where I dump thoughts and experiences from my computer-related world.
It is mostly focused on software development but I also have wider interests and dabble in architecture, business and system administration.
More About me…

My StackOverflow Profile
My (sporadically active) StackOVerflow account

Most Recent Posts

Categories

Links