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;

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

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.