Posts filed under 'Database'
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.
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.
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
April 1st, 2009
I’m currently researching ways to move my main MS Access application from a simple local network client/backend setup to a global, multiple remote sites configuration using SQL Server.
One of the challenges is to upsize the current MS Access 2007 backend database to SQL Server 2008.
If you try it from Access itself using the Upsizing Wizard, you may end up getting this error message:
The Upsizing Wizard only works with Microsoft SQL Server (Versions 6.50 SP5 or higher). Please log in to a SQL Server data source.
After spending some time fiddling around with SQL Server settings I couldn’t understand why I was still getting this error.
Turns out that the upsizing wizard is apparently sensitive to the version of SQL Server you’re using and it doesn’t consider SQL Server v10 (2008) as being later than v6.50…
This issue is in fact a blessing.
Microsoft provides a migration tool for upsizing MS Access database to SQL Server 2008 that’s orders of magnitude better than anything the basic wizard can do: the SQL Migration Assistant for Access, or SSMA.
SSMA lets you take a bunch of Access databases and move the tables and queries you choose to SQL Server, automatically linking them in your original database if you want.
It’s not just a one-off thing either: SSMA keeps track of the objects that where transferred and allows you to synchronise both schema and data as often as you need.
So here you are: do not use the basic MS Access Upsizing Wizard, download and use SSMA instead.
Strange COM Error
While SSMA works perfectly fine on my Windows 2008 x64 laptop, on my main Windows XP desktop it throws an exception when trying to load an Access database:
Unable to cast COM object of type ‘Microsoft.Office.Interop.Access.Dao.DBEngineClass’ to interface type ‘Microsoft.Office.Interop.Access.Dao._DBEngine’
… {00000021-0000-0010-8000-00AA006D2EA4}…
It was a COM error saying that the library for DAO couldn’t be loaded.
I couldn’t find any relevant information on the web.
After a while, I had a look at the DAO driver in
C:\Program Files\Common Files\Microsoft Shared\DAO\dao360.dll
and I noticed that the filename was in blue: that reminded me that I had set compression on the filesystem.
I disabled compression for the file and, magically, SSMA worked again…
Moral of the story: be careful about compressing your filesystem, some registered libraries and system files may work in unpredictable ways…
References
Updates
- 17MAR2009: Added section on the strange COM error.
- 14MAR2009 : Added links to Tony’s Access MVP website.
- 05JAN2009 : Original publication.
March 17th, 2009
There are times when the coexistence of 64 and 32 bit code on the same machine can cause all sorts of seemingly strange issues.
One of them just occurred to me while trying to run the ASPx demos from Developer Express, my main provider of .Net components (the best supplier I’ve ever been able to find).
I was getting the following error:
The ‘Microsoft.Jet.OLEDB.4.0′ provider is not registered on the local machine:
It may look otherwise, but this error is generally due to either of two thing:
- you don’t have Office 2007/2010 Jet drivers installed
- or you are running a 32 bit application in a default x64 environment.
The first issue is easy to solve, just download the Access 2010 Database Engine from Microsoft (works with Access 2007 databases as well).
For the second one, the fix is also easy enough:
- For Windows 2008: Navigate to Server Manager > Roles > Web Server (IIS) > Internet Information Services (IIS) Manager, then look under your machine name > Application Pool.
- For Windows 7: Navigate to Programs > Administrative Tools > Internet Information Services (IIS) Manager, then look under your machine name > Application Pool.
Under there you can call the DefaultAppPool’s advanced settings to change Enable 32-Bits Applications to True:
You may have to restart the service for it to take effect but it should work.
References
Updates
- 10DEC2011: Updated driver link to use the Access 2010 engine.
- 03APR2010: Added instructions for Windows 7
- 12FEB2009: Added reference to Scott’s article.
- 28OCT2008: Original version
October 28th, 2008
My trusty old gigantic Sony Vaio is about 4 years old. It served me well and still works but it’s about to become my main development machine for the next couple of months and I can’t afford to have it die on me during that time.
It was time to get something as gigantic and more up-to-date in terms of technology.
I use VMware on my main desktop to keep multiple OS setups that match typical configurations of my customer’s machines.
This allows me to test my software before deployment and make sure everything works as expected.
It saved me many times from strange bugs and I would consider these final tests to be a mandatory step before deployment.
My old trusty vaio would be hard pressed to run any of these without slowing down to a crawl.
I looked at some possible replacements. Initially I checked Lenovo’s offerings but they don’t seem to offer anything in large screen size (WUXGA 1920×1200) (Note, actually, they have, but not really for me).
Dito for Dell, not counting their humongous XPS M1730 luggable gaming machine that was wayyy over the top as a work computer, not to mention probably heavier than its volume in pure gold.
On a hint from a friend I checked out Apple’s online store and saw they had a nice Macbook Pro configuration. I went to check it out in the retail store close to my office and they had that exact specification in stock, so, in what must have been the highest rated expense/time-to-think ratio of any decision I ever took, well, I bought it…
The spec, some bragging rights:
- Macbook Pro 17″
- Core Duo T9500 2.6GHz processor
- nVidia 8600M GT 512MB graphics card
- 200GB 7200rpm drive
- Kingston 4GB DDR2 667MHz RAM
- Hi Resolution 17″ 1920×1200 glossy screen
It’s a very nice machine, Apple knows how to make nice hardware, there is no question there.
OSX has some cool features, some of them still a bit foreign to me and some minor annoyances are creeping up, like Thunderbird’s not picking up my system date and time settings and displaying the date in the wrong format (a pet peeve of me), probably not Apple’s fault but annoying nonetheless.
So far so good and while I don’t mind using OSX for my browsing, email and creative stuff, that machine is meant to be running Windows Server 2008 x64 as a development platform.
Why Windows Server 2008 x64?
Well, it has some excellent features, a smaller footprint than Vista, all the aero eye candy, is apparently noticeably faster than Vista and has none of the nagging security prompt (you are considered administrator though, so keeping safe is entirely up to you).
The 64 bit version can also address the full 4GB of RAM without limitation and all server features are optionally installable.
By default, the installation is actually pretty minimal and you have to set services and options to get Windows configured as a proper workstation. It is after all, meant to be a server.
Oh, I almost forgot that there is also support for HyperV, although you must make sure you download the right version (if you list all available downloads in your MSDN subscription, you’ll see some that are explicitly without that technology).
Installing Windows Server 2008 x64 is remarkably easy.
- Get your hands on the ISO from your MSDN subscription or an install DVD from somewhere else (like a MS event, or even as a free 240 days download from Microsoft).
- You’ll need to repackage the ISO as it won’t work properly (something to do with non-standard file naming options).
It’s fairly easy if you follow the instructions from Jowie’s website (cached version): you can get the ImgBurn software for free as well, which is a good find in itself. It should’t take more than 30 minutes to repackage the DVD.
- In OSX, go to Applications > Utilities > Boot camp and follow the instructions on screen.
You will be able to resize the default partition by just moving the slider. I left 60GB for OSX and allocated the rest to Windows. The good thing is that OSX can read Windows partitions, so you can always store data there. Windows however, can’t read the HFS+ mac file system, although there are some third-party tools that can do it [1] [2] [3].
- Insert your repackaged DVD and Bootcamp will have rebooted the machine.
After a few minutes of blank screen (and no HDD activity light to let you know something is happening), windows setup launches.
- You will be then prompted with the choice of partition to install to.
Select the one named BOOTCAMP, then click the advanced options button and click format.
From there one, windows will install everything, then reboot, then carry on installing, then reboot one last time.
- Now, insert your OSX recovery CD 1. It should automatically launch the driver installation.
Once done, you’ll reboot to a nice, full-resolution windows prompt.
- All drivers will have been installed correctly except the one for Bluetooth. To easily solve that issue, just go to Spencer Harbar’s website and read how to install the Bluetooth drivers. Takes 5 minutes tops.
The final touches
A few notes to quickly get things running as expected.
- Get the most of your configuration by following the list of tweaks from Vijayshinva Karnure from Microsoft India.
- There are more tweaks, and even more tweaks available as well (don’t forget to enable Superfetch).
- Microsoft has a whole KB entry on enabling user experience.
- In the Control Panel > System > Advanced System Settings > Advanced > Settings > Advanced > Processor scheduling, set to Programs instead of Background services.
- Activate your copy of Windows using Control Panel > System.
I was getting an error code 0x8007232B DNS name does not exist error. To force activation, just click on the Change Product Key button and re-enter the same key you used during install.
Windows will activate straight away.
- When booting your Macbook, press the Option key and you will be presented a list of boot choices.
- You can check on Apple’s Bootcamp webpage other information about how to use the track pad, keyboard layouts etc,
References
August 31st, 2008
Access programming is inherently single-threaded. That’s usually OK as most operations are sequential anyway and it keeps things simple at the programming level.
There are times though where the lack of ability to run code on another thread is sorely missing: anything that takes a long time to run will just freeze the application, making it unresponsive and appearing to be locked and about to crash to the user.
Checking for the existence of network paths
Checking for the existence of network paths (directories or files) is one of these issues that can freeze an application for 30 seconds or more if the folder is not accessible.
This is a type of problem that benefits greatly from running in a separate thread: it can take such a long time that the best way to check for these remote paths is to launch the verification for their existence outside of Access and somehow get the result back and cache it for the current session so we don’t have to suffer these delays again every time we check for that path’s existence.
One easy way to do achieve that goal is to create plain DOS batch files that execute hidden from view, create a result file when they complete their task and delete themselves automatically when they are finished.
How to use it
Download the sample database below then just add the FileUtilities, HashTable and MD5 modules to your project and you can use the code as such:
The status variable will return either of the following values:
AsyncDirectoryStatus.OK if the path was found.AsyncDirectoryStatus.NotFound if the path was not found (either because it doesn’t exist or you don’t have the rights to access it).AsyncDirectoryStatus.Checking if the verification is in progress and we haven’t received a definite answer yet.
It’s up to you to decide how you want to handle that case. You could periodically check it, like I did in the example database, or you could disable the controls until you’re getting a confirmed result (by checking every time the user performs some action, like moving from record to record in a datasheet for instance).
You can call PathExistAsync as often as you want to check the status: it will not slow down your application (read the optional arguments section below though).
The result of the verification is cached, so querying the existence of the path is actually only done once; the result of subsequent queries for the same path is just instantly retrieved from memory.
Optional arguments
If you want to force the actual re-checking of a path without using the cached value, you can simply pass the ForceCheck optional parameter:
The first time you query for a path (or force it to be rechecked) there will be a short 150ms delay to give a chance to the function to return its result straight away (in case the path can be resolved quickly).
This may not be desirable if you’re checking a bunch of directories at a time. For instance, this is what I do when my application launches:
By querying the existence of all these paths as soon as my application launches, I am starting the verification process without introducing delays in the application itself: each verification will start in its own process, in parallel to the main application.
Later in the application, when I need to actually use these paths, their result is likely to be known.
How it works
The FileUtilities module contains the main code.
In it, the PathExistAsync function works in slightly different ways depending on whether it’s the first time it is being called for a particular path or not.
The first time
The first time the function is called for a given path, we create in the user’s temporary folder the small batch file whose name is simply a MD5 hash (see below) of the path with .bat appended to it.
This batch file simply checks for the existence of the path and will create a small file (whose name is the MD5 hash of the path) with either 0 or 1 in it depending on the result of the verification.
We initially cache the status of the verification for the Path into the AsyncDirectories hashtable (see below) as Checking.
Example of batch file automatically created to verify a path:
The Batch file name is 463C7367D8329BD6209A65A70A7DA08C.bat where the long number is actually the MD5 hash of the path we’re checking \\123.56.78.9\going nowhere.
Getting back the result
Whenever the PathExistAsync function is called, we check the currently cached result from the AsyncDirectories hastable.
If it is still Checking then we try to verify if we the result file has been created from the running batch. If not, we just return the same status, if yes, we read the result from the file, save it in the hashtable and delete the result file.
Useful libraries
The code makes use of 2 extremely useful libraries that I end up using quite often:
a HashTable implementation.
It makes it easy to create hashtable objects (otherwise known as Associative Arrays) to store and retrieve key/value pairs quickly.
Hashtables are often used to cache data and can be thought of arrays where the index is a string value instead of an number.
Here I use a hashtable to keep track of the paths we’ve checked and their result.
a MD5 hash implementation.
MD5 is a way to get a somewhat unique fixed-length value from a chunk of data.
It’s a mathematical function that guarantees that a small change in input (say a single bit in the input data) has a large effect on the output value (a totally different number will be generated) and that you can’t reverse the function (you can’t obtain the input just by looking at the output).
It is often used in security applications to transform sensitive data like passwords into unique values that can be (somewhat) safely stored because you can’t easily reverse a md5.
Well, MD5 are not that secure any longer but here we just use their ability to transform our path into a unique number that we can easily use as a filename and a key for our hash to retrieve the current status of the path being checked.
Sample database
Download the PathExistAsync01.zip (67KB) containing the Access 2007 ACCDB database.
Download the PathExistAsync02b.zip (121KB) containing the MDB database1 (untested as I only have Access 2007).
License
Please refer to the source code in the database for the exact licensing terms.
Note that the license only refers to code by me. When code from other sources is used, you will have to conform to their own licensing terms.
This work is licensed under a Creative Commons Attribution 3.0 Unported License.
References
June 20th, 2008
Next Posts
Previous Posts
