Hi,

I use the code above to create new a MS Access 2000 file.

a) I would like to establish a user name and password to protect this
database and I'm not sure how to do it. Can I do it as I create the
file? It is done after creating the file? And how?

b) I would like a table field to allow zero lenght strings and also I'm
not sure how to do it. Can I do it as I create the file? It is done
after creating the file? And how?

c) The last question I have is how to compact and repair a MS Access
file.

Thanks
Nuno Mendes
procedure TMainForm.ButtonBDCreateClick(Sender: TObject);
var
  Catalog : _Catalog;
  BaseName : String;
  DS : String;
  TablesList: TStringList;
begin
  // Name of the new database file
  BaseName := ExtractFilePath(Application.ExeName) + 'Data.mdb';
  // Create a Catalog Object
  Catalog := CreateCOMObject(StringToGUID('ADOX.Catalog')) as _Catalog;
  // Set the Connection String
  DS := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+ BaseName;
  // Create new Access database
  Catalog.Create(DS);
  // Read tables names from database
  FormData.ADOConnection.ConnectionString :=
'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + BaseName + ';Persist
Security Info=False';
  // Create variable to get table names
  TablesList := TStringList.Create;
  // Get table names to variable
  FormData.ADOConnection.GetTableNames(TablesList);
  // Check if items table already exists
  if TablesList.IndexOf('items') < 0 then
  begin
    // Create items table
    FormData.ADOCommand.CommandText := 'create table items (items _id
COUNTER, name TEXT(75));';
    FormData.ADOCommand.Execute;
  end;
  // Release variable
  TablesList.Free;
  // Reset AdoCommand
  FormData.ADOCommand.ConnectionString := '';
end;

Here's what I use to do the repair / compact:

// David Reed
function  RepairAndCompact(Connection: TADOConnection): integer;
var
  FromDb,ToDb: string;
  x: integer;

const
  ADO_PROVIDER = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=';

begin

  // Compacts the database to a NEW database; this is the only way to do
this
  Result := 0;

  if Connection.Connected then
    Connection.Close;

  FromDb := Connection.ConnectionString;
  x := Pos('Data Source=',FromDb);

  if x < 1 then
    Exit;

  if x > 0 then begin
    FromDb := Copy(FromDb,x + 12,255);
    Delete(FromDb,Pos(';',FromDb),Length(FromDb));
  end;

  if FromDb = '' then
    Exit;

  ToDb := StringReplace(FromDb,'.','_.',[rfReplaceAll]);
  while (FileExists(ToDb)) and not (DeleteFile(ToDb)) do
    ToDb := StringReplace(FromDb,'.','_.',[rfReplaceAll]);

  with TJetEngine.Create(nil) do
  try
    try
      CompactDatabase(ADO_PROVIDER + FromDb,ADO_PROVIDER + ToDb);
      if not FileExists(ToDb) then
        Exit;
      // delete the old file and rename the new one..
      if (FileExists(FromDb)) and  (DeleteFile(FromDb)) and
        (RenameFile(ToDb,FromDb)) then
        Result := 1;
    except
      on E: Exception do
        ShowMessage('Message = ' + e.Message);
      end;
  finally
    Free;
  end;
end;

--
David Reed
White Plains, Maryland USA
www.diamondsg.com
www.oasisrep.com
*To reply, remove the no_spam from my email address
*To view file attachment(s), remove underscore from file extension