VBA: Unicode Strings and the Windows API

In this post, we talk about Unicode, about Strings, about how they sometimes become unexpectedly mangled when you pass them around the system and what we can do about it. We’ll see some Chinese ideograms, peek into memory and display some nonsense on screen in the process. VBA likes Unicode Internally, VBA handles strings in... » read more

Access: using .Net strings in VBA for fun and profit

Well, maybe the profit part is a bit of an exaggeration, but it will be fun, I promise, unless you’re not a geek in which case I really wonder why you are reading this blog. I’m not going to shock you if I say that strings manipulations and formatting are really ugly in VBA. Who... » read more

VBA for 32 and 64 bit systems

I’ve put off writing about x64 MS Office because I was a bit lazy and hadn’t build the necessary environment to test it properly. In my day-to-day job -as far as Access is concerned-, I have so far targeted the 32 bit (x86) version of Office 2010 but the 64 bit version is bound to... » read more

Office 2010 SP1 and Access 2010 Runtime SP1 are broken

(__Updated Saturday 10DEC2011.__) On 28th of June 2011, Microsoft Office 2010 Service Pack 1 and the Access 2010 Runtime Service Pack 1 were issued. After upgrading my development machine (Win7 x64) and a few clients (Windows 2008R2 x64) to SP1 (x86), I started to get strange issues: * I use .Net libraries from my Access... » read more

Access: checking blank variables

I often have to test String, Variant or Object variables that have no content and could be considered ‘blank’. The problem is that testing for “blankness” [can mean many different things to different types][1]: * For an `Object` type, the variable can be `Nothing`. * For a `String` type, the string can have no content... » read more

Access vs SQL Server: some stats (part 1)

In the perspective of upsizing my current Access 2007 application, I have been trying to understand a bit more about the possible performance impact of various choices of Primary Keys. My problem is that currently, the Access application uses autoincrement numbers as surrogate Primary Keys (PK). Since I will need to synchronise the data over... » read more

Sysadmin: SQL server performance madness

I’ve just lost 2 days going completely bananas over a performance issue that I could not explain. I’ve got this [Dell R300][1] rack server that runs Windows Server 2008 that I dedicate to running IIS and SQL Server 2008, mostly for development purposes. ![Dell PowerEdge R300 Rack servers][dellr300] In my [previous blog entry][2], I was... » read more

Access: building ‘upsizable’ applications.

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... » read more

MS Access: upsizing to SQL Server 2008

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 accdb backend database to SQL Server 2008. If you try it from Access itself using the... » read more