I didn’t end up working on anything Access today, but I thought I’d throw some thoughts at you on programming in Access VBA:

1. Always set the Option Explicit statement at the top of every module. There’s an Access setting to always include it when creating new modules, I recommend turning this on. This makes variable declarations required and has saved me from making many easily avoidable errors! Use this in conjunction with the Compile option in the VBA Debug menu to automatically find any variable misspellings or undeclared variables.
2. Save your project often. Make backups often (I do this using versioning: OASIS-SVN, and by committing each feature I add).
3. Replicate your customer’s environment to the best of your ability. You don’t want to be developing using an Access file back end while the customer actually uses a SQL Server back end. The more similar your environment is, the less likely you are to have problems that are only in one version.
4. Whatever you need to do to change your dev database in order to make it ready to deploy live, create a VBA routine to automate the steps. This will make it easy to deploy and prevent you from making mistakes during deployment resulting in customer problems when using the new db.