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;

Lotus

Answer 1

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

Obstacle

Don't forget to remove any parameters that might have been added before. Check the Parameters property. – Northerner

@GolezTrol: Doesn't setting SQL.Text automatically clear Parameters? – Postdate

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. ;) – Northerner

Answer 2

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.

Lotus

I don't know about Delphi, but MS Access never uses backticks. Table/field names with e.g. spaces always need [square brackets]. – Fineman

@Fineman I just created a query inside MS Access 2007 and typed this query string in SQL view. It shows the result with no problem. – Lotus

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. – Fineman

Nice one. I guess TQuery special cases the backticks, but does not special case the [. – Obstacle