Access 2007 Macro Fun

Okay, don't ask me how or why, but an issue with the Access 2007 Transfer Text macro came accross my desk. Macros... I thought these things would die out and go away, but apparently they are holding steady, if not increasing in popularity. But when macros break or don't work, who gets to fix them???? Developers... Developers, who would much rather be writing code and trying to figure out how to work the latest new design pattern into their current project. I digress....

Anyway, here are some tips for all you macrophiles wishing to use this particular macro.

• Use UNC paths
• Unless you are using the drives on your PC or ar 100% certain that mapped drives will remain the same in your organizaion for all users that will be using your app, you are only asking for trouble. Bone up on what a UNC path is here: http://compnetworking.about.com/od/windowsnetworking/g/unc-name.htm.
• Make sure the file you're importing is allowed by Access.
• The allowed extensions are: txt, csv, tab, asc, tmp, htm, html, but if you are so insistent that you need to allow some other extension, you can hack the registry using this MS article: http://support.microsoft.com/kb/306144. Please note that if you are using a 64 bit OS with 32 bit Office, you may need to also look in the HKLM\Software\Wow6432Node\ for the setting.
• Make sure you have enabled macros.
• This one is kind of a gimmie, but can get overlooked.

• If you cannot see the macro in the dropdown list, make sure you are showing all macro actions.
• The Transfer Text macro is not trusted or regarded as "safe" so Access hides it by default (that should tell you macrophiles something right there).

• Double check your import specification.
• You can get more info on this from Microsoft's documentation of the TransferText Macro here: http://office.microsoft.com/en-us/access-help/transfertext-macro-action-HA001226310.aspx.

So, if you are going to tread into the land of macros, make sure to play by the rules as much as possible. Utilize the microsoft documentation when setting them up, as it is rather thorough. If you run into trouble, retrace your steps, and double check everything. If all else fails and you do need to to go your resident developer, come with your hat in your hand and a shaker of salt (because he or she will probably make you eat it). Offer him or her a pizza, case of Mt. Dew, or 12 pack of their favorite beer for their trouble. After all, they are doing you a favor. :)