How to run a query on a table that has colon in its name?

Question

I'm reading data from an .mdb file (MSAccess 2000 format). There are some tables that have colons in their names. I'm getting an exception when I try to open queries on these tables:

EOleException with message 'Parameter object is improperly defined. Inconsistent or incomplete information was provided'.

this is my code:

procedure TForm1.Button1Click(Sender: TObject);
var
  Query: TADOQuery;
begin
  Query := TADOQuery.Create(nil);
  Query.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;'+
    'Data Source=DB.mdb;Persist Security Info=False';
  Query.SQL.Text := 'select * from [Table1:1]';
  try
    Query.Open;
  finally
    Query.Free;
  end;
end;

Johan · Accepted Answer · 2016-04-30T16:54:56.310

9

TQuery will interpret the : if ParamCheck = true.
Set ParamCheck:= false and then set the SQL.Text.

procedure TForm1.Button1Click(Sender: TObject);
var
  Query: TADOQuery;
begin
  Query := TADOQuery.Create(nil);
  Query.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;'+
    'Data Source=DB.mdb;Persist Security Info=False';
  Query.ParamCheck:= false;
  Query.SQL.Text := 'select * from [Table1:1]';
  try
    Query.Open;
  finally
    Query.Free;
  end;
end;

Now it works.

Combining coloned table/column names and parameters
If you absolutely must go down the path of using table/column names with colons in them and still want to use params elsewhere in your query then you can use a macro to fill in the table/column name.
This requires FireDac though. I do recommend you be very careful with this because unlike parameters macro's are not safe against SQL injection!

See: http://docwiki.embarcadero.com/CodeExamples/Seattle/en/FireDAC.TFDQuery.Macros_Sample

edited Apr 30 '16 at 16:54

answered Apr 30 '16 at 16:29

Johan

74,508
24
191
319

Don't forget to remove any parameters that might have been added before. Check the `Parameters` property. – GolezTrol Apr 30 '16 at 16:32
@GolezTrol: Doesn't setting `SQL.Text` automatically clear `Parameters`? – Ken White Apr 30 '16 at 16:34
1

If you set ParamCheck to false, I think it doesn't. It just leaves the parameters alone so you can manage them yourself. 99% sure. ;) – GolezTrol Apr 30 '16 at 17:53

score 4 · Answer 2 · edited May 23 '17 at 12:31

4

I was checking solutions in other languages. However Johan's answer seems to be the right one, but the one posted here also works in Delphi/Access case:

Using backticks ` around your tablename should allow it to be ignored by the param check.

Query.SQL.Text := 'select * from `Table1:1`';

This one also can be combined with using parameters in query string.

edited May 23 '17 at 12:31

Community

1
1

answered Apr 30 '16 at 17:23

saastn

5,717
8
47
78

I don't know about Delphi, but MS Access *never* uses backticks. Table/field names with e.g. spaces always need [square brackets]. – Andre May 01 '16 at 09:00
@Andre I just created a query inside MS Access 2007 and typed this query string in SQL view. It shows the result with no problem. – saastn May 01 '16 at 14:57
You're right (and I was wrong) - backticks can be used in place of square brackets. Surprising. :) `SELECT [Tabelle1:1].ID, \`Tabelle1:1\`.\`nix:dings\` FROM [Tabelle1:1];` works, without the backticks it doesn't. – Andre May 01 '16 at 16:15
Nice one. I guess TQuery special cases the backticks, but does not special case the `[`. – Johan May 03 '16 at 00:16

How to run a query on a table that has colon in its name?

2 Answers2