InsertDatabase, Office 2007 and Columns with spaces
Wednesday, 05 March 2008 18:35

The InsertDatabase method (member of the Range object) in Word VBA is poorly documented. This leads to a lot of frustration while trying to use it, more so when you are fetching data from an Access database with spaces in the names of the columns you want to fetch. However, when moving things over to Office 2007, it gets even more frustrating!

Allow me to explain by example.

Suppose you have the following code in your VBA project:

Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=True, DataSource:="c:\somemdb.mdb", SQLStatement:="SELECT [random column] from randomtable where randomid = 1", From:=-1, To:=-1, IncludeFields:=True

This would select a random column, aptly named [random column], from randomtable where randomid = 1.

Using spaces in column names is a bad practice, however, in Access it is widely accepted (mostly because it's end-users who make this kind of databases).

When moving this code to Office 2007, you will get an error stating something is wrong with your syntax. However, as you would expect, if you paste the statement in your Access 2007 database, it'll work.

The solution is very simple, but one just has to bump into it because there is no information whatsoever.

The solution is to use aliases.

Which means we will change our statement:
SELECT [random column] from randomtable where randomid = 1
SELECT t1.[random column] from randomtable t1 where randomid = 1

See what we did there ? Never have a problem anymore, and of course, this solution is backwards compatible!

Last Updated on Monday, 28 April 2014 08:59

Copyright © DYRA IT CONSULT, 2008-2023 All Rights Reserved