Connecting to a SQL Compact file (.sdf) using an ADO connection in Delphi

Question

I'm attempting to use a local .sdf file as a means of temporary storage should the main database be unreachable. I have the .sdf file, but when I try to set it to the file it seems to not at all know if the .sdf exists. The current connection string I have currently is:

 Driver={SQL Native Client};Data Source=C::\users\username\desktop\file\MyData.sdf;Persist Security Info=False

and for the Provider it generated for me:

 Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5

When I try to use the connection, I get a "Provider cannot be found. It may not be properly installed." The .sdf is most definitely in the folder. I also had/have a problem with it wanting a username and/or password, neither of which I had to specify when creating the database.

The Question: Is there something wrong with my connection string? Is it reasonable to use ADO connections to access SQL Compact Databases? Might there be an easier way to query/retrieve data from a temporary storage (I would prefer doing it with SQL though)? Most documentation seems to be from 2003/2005, which is unhelpful.

I used "connectionstrings.com" for help making the string. Any advice would be helpful, thanks

Answer 1

First to open the sdf file you must use a provider compatible with the version of the sdf file. since you mention in your comments the version 3.5 you must use this provider Microsoft.SQLSERVER.CE.OLEDB.3.5

Then you must ensure which the provider is installed

Try this code to list the OLEDB providers installed in your system

{$APPTYPE CONSOLE}

{$R *.res}

uses
  Windows,
  Registry,
  Classes,
  SysUtils;

procedure ListOLEDBProviders;
var
  LRegistry: TRegistry;
  LIndex: Integer;
  SubKeys,Values: TStrings;
  CurKey, CurSubKey: string;
begin
  LRegistry := TRegistry.Create;
  try
    LRegistry.RootKey := HKEY_CLASSES_ROOT;
    if LRegistry.OpenKeyReadOnly('CLSID') then
    begin
      SubKeys := TStringList.Create;
      try
        LRegistry.GetKeyNames(SubKeys);
        LRegistry.CloseKey;
        for LIndex := 0 to SubKeys.Count - 1 do
        begin
          CurKey := 'CLSID\' + SubKeys[LIndex];
          if LRegistry.KeyExists(CurKey)  then
          begin
            if LRegistry.OpenKeyReadOnly(CurKey) then
            begin
              Values:=TStringList.Create;
              try
                LRegistry.GetValueNames(Values);
                LRegistry.CloseKey;
                for CurSubKey in Values do
                 if SameText(CurSubKey, 'OLEDB_SERVICES') then
                   if LRegistry.OpenKeyReadOnly(CurKey+'\ProgID') then
                   begin
                     Writeln(LRegistry.ReadString(''));
                     LRegistry.CloseKey;
                     if LRegistry.OpenKeyReadOnly(CurKey+'\OLE DB Provider') then
                     begin
                      Writeln('    '+LRegistry.ReadString(''));
                      LRegistry.CloseKey;
                     end;
                   end;
              finally
                Values.Free;
              end;
            end;
          end;
        end;
      finally
        SubKeys.Free;
      end;
      LRegistry.CloseKey;
    end;
  finally
    LRegistry.Free;
  end;
end;


begin
 try
    ListOLEDBProviders;
 except
    on E:Exception do
        Writeln(E.Classname, ':', E.Message);
 end;
 Writeln('Press Enter to exit');
 Readln;
end.

Now this is a basic sample to connect to a Sql Server compact file.

{$APPTYPE CONSOLE}

{$R *.res}

uses
  ActiveX,
  ComObj,
  AdoDb,
  SysUtils;

procedure Test;
Var
  AdoQuery : TADOQuery;
begin
  AdoQuery:=TADOQuery.Create(nil);
  try
    AdoQuery.ConnectionString:='Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Datos\Northwind.sdf';
    AdoQuery.SQL.Text:='Select * from Customers';
    AdoQuery.Open;
    While not AdoQuery.eof do
    begin
      Writeln(Format('%s %s',[AdoQuery.FieldByName('Customer ID').AsString,AdoQuery.FieldByName('Company Name').AsString]));
      AdoQuery.Next;
    end;
  finally
    AdoQuery.Free;
  end;
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.