Podcasts are a great way to get educated and entertained. As a developer we are lucky to have a choice of some fine podcasts from industry leaders. If you commute, jog, travel, you can easily use that time away from the computer to get better informed and reflect about our field.
Posts filed under 'Programming'
(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 application and suddenly, even when not instantiating any .Net objects, Access would crash, usually on startup, but sometimes when opening the VBE.
Decompiling and re-compacting the database would be OK, usually once, but the problem would reappear the next time I would restart the application.
- In the Runtime, I would get strange errors, such as The setting your entered isn’t valid for this property, or Action Failed Error Number: 2950, or Runtime Error 3270 Property not found.
The strange thing about these errors is that they would occur in places that had not been modified for many releases of our application, parts that have been running without problem until now.
- Another weird issue was the systematic reset of our custom ribbon to its first tab. this could happen randomly, but most it could also be reproduced by simply opening a report as a tab page (that fill-in the whole MDI window). When closing that form, the first tab of the ribbon would select itself automatically. This wasn’t happening when closing pop-up windows.
After removing the Office and Runtime Service Pack 1, everything went back to normal.
A fix, finally!
12 comments July 13th, 2011
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:
- For an
Objecttype, the variable can be
- For a
Stringtype, the string can have no content at all:
- For a
Varianttype, the string can have any of the following attributes or values:
- it can be
Missingif the variable is an unused optional parameter,
- it can be
Emptyif it was never assigned,
- it can be
Nullif, for instance it’s bound to a nullable field or unbound with no value,
- it can be an empty string
- it can be
When having to check these variables in code, it can be tiresome to have to go through testing some of these possibilities just to find out that your variable does or not not contains something useful, regardless of the type of variable you are using.
To avoid having to do all these tests, make the code a bit more tidy and allow me to move on to more important things, I use this small utility function quite often:
So now I don’t have to worry so much about the type of the variable I’m testing when I want to know if it contains useful data:
IsBlank() doesn’t replace the other tests but I found it to be more straightforward to use in most cases.
2 comments September 9th, 2009
I have been spending a lot of time trying to find out why some of the code used to insert new records into a linked SQL Server table would systematically fail with an error:
Run-time Error '3155' ODBC--insert on a linked table failed
It was driving me mad.
I could insert a simple record using SQL Server Management Studio, I could add new records to the table in datasheet mode within Access, but as soon as I tried to insert a record from code, whether using DAO recordset or executing the same SQL INSERT, it would miserably fail.
After a fair bit of investigation and tests, of which you can read the full account on the question I asked on StackOverflow, it turns out that this is a long-standing bug in the ODBC Driver (or Access).
Memo fields in Access are usually translated into
nvarchar(MAX) in SQL Server by tools like SSMA.
Unfortunately, when you link tables having these fields using the SQL Server Client driver, these fields get incorrectly interpreted as
string, even though they appear ok from the table design view.
It’s only if you try to insert something into the field, either text larger than 255 chars or NULL, that you get the error message.
So, the solution, at least in this case, is to revert to the older SQL Server ODBC driver instead, or use
varchar() instead of
nvarchar(), but if you’re dealing with Unicode, you have to stick with
- My question Inserting NULL in an nvarchar fails in MSAccess on StackOverflow.
- A reference I found on this issue: Ms Access linking table with nvarchar(max).
2 comments June 11th, 2009
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 multiple remote sites, including occasionally disconnected clients, I can’t use the current autoincrement PK and will need to change to GUID.
To see for myself what could be the impact, I made a series of benchmarks.
This first part is fairly simple:
- Populate a Product table that contains 3 fields:
Designationwith 1,000,000 records.
- Test natively on SQL Server and Access 2007.
- The records are inserted in transactions batches of 1000 records.
- I collect the time taken for each of these transactions and plot it.
Nothing much to say about that:
All tests are performed on a dedicated Windows Server 2008 x64 rack running Access 2007 and SQL Server 2008 Standard (SP1) x64.
In SQL Server, we created a database with two tables
For the table using a GUID, we use the
NewSequentialID() instead of
NewID() to create new keys. This is supposed to offer much better performance as the generated GUIDs are guaranteed to be sequential rather than random, resulting in better index performance on insertion.
For the Access version of the tables, we basically use the same definition, except that we used 4 tables:
- ProductINT: let Jet/ACE autonumbering create the sequential integer Primary Key.
- ProductINTRandom: let Jet/ACE autonumbering create the random integer Primary Key.
- ProductGUIDRandom: let Jet/ACE use its own internal
GenGUID()for the key which generates random GUIDs instead of sequential ones.
- ProdcutGUIDSequential: call the Windows API (UuidCreateSequential) to create sequential ID instead.
SQL Server Test code
Using the SQL Server Management Studio, we performed the following test once for each table (resetting the database in-between):
Basically, we perform 1000 transactions each inserting 1000 records into the table
Access 2007 Test code
To duplicate the same conditions, the following VBA code will perform 1000 transactions each inserting 1000 records.
Note that the recordset is opened in Append mode only.
The importance of this will be discussed in another article.
We call this code to perform inserts on each of our Access tables:
- ProductINT table: we just insert data in the
ProductINTtable, letting Access create autonumber IDs.
- ProductINTRandom table: we just insert data in the
ProductINTRandomtable, letting Access create random autonumber IDs.
- ProductGUIDRandom table: we let Access create the Random GUID for the primary key.
- ProductGUIDSequential: we use the Windows API to create a sequential ID that we insert ourselves.
Without further ado, here are the raw results, showing the number of inserted record per second that we achieve for each test over the growing size of the database (here are only shown tests comapring Sequantial GUID and Autoincrement on SQL Server and Access, see next sections for the other results):
What we clearly see here is that performance when using autoincrement and Sequential GUID stays pretty much constant over the whole test.
That’s good new as it means that using Sequential GUIDs do not degrade performance over time.
As a side note, in this particular test, Access offers much better raw performance than SQL Server. In more complex scenarios it’s very likely that Access’ performance would degrade more than SQL Server, but it’s nice to see that Access isn’t a sloth.
Using Sequential GUID vs Autoincrement in Access
The results show that we do take a performance hit of about 30% when inserting Sequential GUID vs just using autonumbers.
We’re still getting good results, but that’s something to keep in mind.
In terms of CPU consumption, here is what we get:
Random PK, whether they are simple integer or GUID do consume substantially more CPU resources.
Using Sequential GUID vs Identity in SQL Server
Out-of-the box, SQL Server performs quite well and there is not much difference whether you’re using Sequential GUIDs or autoincrement PK.
There is however a surprising result: using Sequential GUIDs is actually slightly faster than using autoincrement!
There is obviously an explanation for this but I’m not sure what it is so please enlighten me :-)
Using Random GUID vs Sequential GUID vs Random Autonumber in Access
So, what is the impact of choosing a Sequential GUID as opposed to letting Access create its own random GUIDs?
It’s clear that random GUIDs have a substantial performance impact: their randomness basically messes up indexing, resulting in the database engine having to do a lot more work to re-index the data on each insertion.
The good thing is that this degradation is pretty logarithmic so while it degrades over time, the overall performance remains pretty decent.
While GUIDs are larger than Random Integers (16 bytes vs 4 bytes) the actual performance of inserting records whose PK is a random integrer is actually worse than random GUID…
Here we’ve check the baseline for our performance tests. In the next article, we’ll look exclusively at the performance of inserting data from a remote Access 2007 front end using our VBA code.
Having this baseline will allow us to check the performance overhead of using ODBC and letting Jet/ACE manage the dialogue with the SQL Server backend.
Feel free to leave your comments below, especially if you have any resources or information that would be useful.
- 16APR2009: added test of random autonumber as PK in Access.
- 13APR2009: Original Article
15 comments April 13th, 2009