🚀 go-pugleaf

Hi Ted,
thank you for interesting letter.
Well, I began to study programming from Fortran IV and it was 
IBM360, PDP11 etc. And I liked Fortran and especially 
assembler but it was in those years. Now I don't like 
Fortran and especially assembler (but sometimes use it).
Now I like C++, C#, .Net and especially IDispatch and 
IMoniker interfaces... :)

O'k, so let us come back to your code. Really, there are some 
errors there and it explains why it works incorrectly.

1. These strings - 
    
    Connection=Variant::CreateObject("ADODB.Connection");
    Connection.Exec(Function("Open") << ConnectionString);

No need to open new connection when you used already 

 RecipeCatalog.Exec(Function("Create") << ConnectionString);

Connection is establish and you only need to get reference to 
this connection. It is ActiveConnection property:

Variant Connection=RecipeCatalog.Exec(PropertyGet("ActiveConnection"));

2. 
    Variant Fields=ADORecordSet.Exec(PropertyGet("Fields"));
    Variant NDX=Fields.Exec(PropertyGet("NDX"));
    int itmp = NDX;

Fields - it is collection and it has not NDX property.

3. If you want to open connection again you need to close 
connection in the end of code (independently delete you or not 
delete database).

   Connection.Exec(Function("Close"));

So, in my opinion, code must be similar with follow:

if(OpenDialog1->Execute())
       {
        if(FileExists(Form1->OpenDialog1->FileName))
         {
          ShowMessage("File exist and will be recreated");
          DeleteFile(Form1->OpenDialog1->FileName);
         }

// Ah, some string from my previous code
//        Form4->ListBox1->Clear();
        Form1->Catalog.Exec(Function("Create") <<
              ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Form1->OpenDialog1->FileName));

        Variant RecipeCatalog=Form1->Catalog;

/*  No need Connection was created via Catalog Create method
 Connection=Variant::CreateObject("ADODB.Connection");
    Connection.Exec(Function("Open") << ConnectionString);
*/

     Variant Connection=Catalog.Exec(PropertyGet("ActiveConnection"));


    AnsiString SQLstatement1("CREATE TABLE JUNK (NDX COUNTER(1, 1) NOT NULL CONSTRAINT PRIMKEY PRIMARY KEY, NAMEVALUE VARCHAR(60) WITH COMPRESSION NOT NULL UNIQUE)");
    AnsiString SQLstatement1A("CREATE UNIQUE INDEX FOODNAMENDX ON JUNK (NDX)");
    AnsiString SQLstatement2("CREATE TABLE TABLE2 (NDX INTEGER NOT NULL, COL2 VARCHAR(20) WITH COMPRESSION, CONSTRAINT FORKEY FOREIGN KEY (NDX) REFERENCES JUNK(NDX) ON UPDATE CASCADE ON DELETE SET NULL)");
    Connection.Exec(Function("Execute") << SQLstatement1);
    Connection.Exec(Function("Execute") << SQLstatement1A);
    Connection.Exec(Function("Execute") << SQLstatement2);
    AnsiString SQLstatement3("INSERT INTO JUNK (NAMEVALUE) VALUES ('TESTNAMEDATA')");
    Connection.Exec(Function("Execute") << SQLstatement3);
    AnsiString SQLstatement4("SELECT NDX FROM JUNK WHERE NAMEVALUE='TESTNAMEDATA'");
    //Variant RecordSet=Connection.Exec(Function("Execute") << SQLstatement4);
    Variant ADORecordSet;
    try {
      ADORecordSet=Variant::CreateObject("ADODB.Recordset");
    } catch (EOleSysError &err) {
      Application->MessageBox("Could not create Recordset object",err.Message.c_str(),MB_OK);
      Application->Terminate();
      return;
    }

    if(ADORecordSet.IsNull()) {
      ShowMessage("Could not connect to ADO");
      Application->Terminate();
      return;
    }
    ADORecordSet.Exec(PropertySet("CacheSize") << 50);
    ADORecordSet.Exec(PropertySet("Source") << SQLstatement4);
    ADORecordSet.Exec(PropertySet("ActiveConnection") << RecipeCatalog.Exec(PropertyGet("ActiveConnection")));
    ADORecordSet.Exec(PropertySet("CursorType") << adOpenKeyset);
    ADORecordSet.Exec(PropertySet("LockType") << adLockOptimistic);
    try {
      ADORecordSet.Exec(Function("Open"));
    } catch(EOleSysError &err) {
      Application->MessageBox("Could not open - ",err.Message.c_str(),MB_OK);
      return;
    }
    Variant RC=ADORecordSet.Exec(Function("RecordCount"));
    unsigned int RecordCount=RC;
    if (RC != 1) {
      Connection.Exec(Function("Execute") << SQLstatement3);
      try {
        ADORecordSet.Exec(Function("Open"));
      } catch(EOleSysError &err) {
        Application->MessageBox("Could not open - ",err.Message.c_str(),MB_OK);
        return;
      }
    };
    Variant Fields=ADORecordSet.Exec(PropertyGet("Fields"));

//get field by name
    Variant Field=Fields.Exec(PropertyGet("Item") << "NDX");
// the same possible by index of field

    Variant NDX=Field.Exec(Function("Name"));
    Variant NDXValue=Field.Exec(Function("Value"));
    int itmp = NDXValue;
    AnsiString tmp = IntToStr(itmp);
    ShowMessage(tmp);
    //AnsiString SQLstatement5
//  }

   Connection.Exec(Function("Close"));


As for me it works.
And in my upload/download I inaccurately use try - catch.
May be right way it is to put closing connection in __finally.

Best regards,
Vladimir.