0

This old app is a db front end to desktop Jet database, Access 2000 file format.

  • Delphi 2007
  • TADOConnection.Provider=Microsoft.Jet.OLEDB.4.0.
  • TADOCommand

Originally the database did not have a password. The client wanted the db password protected to keep out prying eyes (yes, it is false security).

Now I have this frustrating problem with the SELECT INTO SQL for copying data to a new table within a separate archive database.

This function worked previously, but after adding a password to the db it returns "EOleException", "Query input must contain at least one table or query".

The code below contains the original working Jet SQL.

var
   dbNameArch, dbNameInt, tblName, IntDataSource, cs1 : string;
const
  SProvider = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=';
  SPassword = 'Password=' + '''xxxxxxxxx''';
begin
  dbNameArch := PathToArchive + 'archive.mdb';
  dbNameInt := PathToDb + 'active.mdb';
  tblName := CreateTableName;

  IntDataSource :=SProvider + dbNameInt + ';Persist Security Info=False;Jet OLEDB:Database '+ SPassword;
  ADOConnection2.ConnectionString := IntDataSource;
  ADOConnection2.LoginPrompt := False;
  ADOCommand2.Connection := ADOConnection2;

  try
      // ORIGINAL working query before password added
      // cs1 := 'SELECT * INTO ' + tblName + ' IN "'+ 
      //         dbNameArch + '" FROM Interventions WHERE InterDate < #' + 
      //         cxDateEditStart.Text + '#';

      // NEW query with password throws error
      cs1 := 'SELECT * INTO ' +  tblName + ' IN "' + 
              dbNameArch + '"' + SPassword + ' FROM Interventions WHERE InterDate < #' + 
              cxDateEditStart.Text + '#';

      ADOCommand2.CommandText := cs1;
      ADOCommand2.Execute;
  except
      on E : Exception do
         begin
            strTemp := 'Unable to create archive table.' + #13 + #13 +
                       E.ClassName + ' error with message:' + #13 +
                       E.Message;
            MessageDlg('Archive error', strTemp, mtError, [mbOK], 0, dckActiveForm);
            ADOConnection2.Close;
            Exit;
         end;
  end;

... etc.

The length of the failed query string (cs1) is 197 characters, is it too long?

I'm at my wit's end, can anyone point out what I'm doing wrong?

RxRick
  • 41
  • 1
  • 3
  • 5
  • 1
    I doubt this due to Delphi Code, its the formatting of SQL Statement. The following question most likely answers your syntax prolem. http://stackoverflow.com/questions/3639086/select-into-table-in-another-database-that-is-password-protected – Robert Love Feb 21 '14 at 16:02
  • Yes Robert I agree that it's the formatting of the SQL (hence the sql tag). The problem I'm having is translating VB/VBA code into something acceptable to Delphi. – RxRick Feb 21 '14 at 16:36
  • Thanks a million, that worked! `cs1 := 'SELECT * INTO [";PWD=' + strPassword + ';DATABASE=' + dbNameArch + '"].' + tblName + ' FROM Interventions WHERE InterDate < #' + cxDateEditStart.Text + '#';` – RxRick Feb 21 '14 at 16:51
  • I feel like an idiot, I searched this web site for the past 2 days trying to find an answer. I guess I need lessons on how to search here. – RxRick Feb 21 '14 at 16:55

0 Answers0