How to set "SQL Server Compatible Syntax" property on MDB file via Delphi?

Access Database has a property called "SQL Server Compatible Syntax" (in Ms Access 2003-, it's in Tools -> Tables/Views -> SQL Server Compatible Syntax; in Access 2007+, it's in Office Logo -> Access Options -> OBJECT DESIGNERS -> QUERY DESIGN).

How can I set this option via Delphi? (ADO, ADOX or any other way...)

Thanks.

Answers


To modify these property you must use the Microsoft Office Access database engine Object Library. From Delphi you can import this library using the option Components ->Import Component -> Import Type Library .

Try this sample.

uses
  ActiveX,
  ComObj,
  Variants,
  SysUtils,
  DAO_TLB in '..\12.0\Imports\DAO_TLB.pas';


procedure test;
var
  LEngine : _DBEngine;
  LDataBase : Database;
begin
  LEngine:=CoDbEngine.Create as _DBEngine;
  //Open the access database
  LEngine.OpenDatabase('C:\Foo\Bar.accdb', dbDriverNoPrompt, false, '');
  LDataBase:=LEngine.Workspaces.Item[0].Databases.Item[0];
  LDataBase.Properties.Item['ANSI Query Mode'].Value := 1;
  Writeln('Done');
  LDataBase.Close();
end;

begin
 try
    CoInitialize(nil);
    try
      Test;
    finally
      CoUninitialize;
    end;
 except
    on E:EOleException do
        Writeln(Format('EOleException %s %x', [E.Message,E.ErrorCode]));
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.

You will almost certainly need to use DAO to make the change. The following C# code does it, but unfortunately I can't offer actual Delphi code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Access.Dao;

namespace daoTest
{
    class Program
    {
        static void Main(string[] args)
        {
            // COM reference required:
            //     Microsoft Office 14.0 Access Database Engine Object Library
            var dbe = new DBEngine();
            dbe.OpenDatabase(@"C:\Users\Gord\Desktop\test.mdb");
            Database db = dbe.Workspaces[0].Databases[0];
            try
            {
                db.Properties["ANSI Query Mode"].Value = 1;
                Console.WriteLine("ANSI Query Mode has been enabled for this database.");
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                db.Close();
            }
        }
    }
}

Need Your Help

Image of a file

winapi memory-management paging virtual-memory

What does Image of a file mean when we say something like "the system allocates enough storage from the paging file to hold the file's image"?

How to improve runtime performance of reading file program

python file io

I'm currently trying to read 150 million lines (from a data file with bio-sequencing information) using Python. Currently, it's reading at 20,000 lines per second which would take about an hour and a