🚀 go-pugleaf

Ted Byers <r.ted.byers@sympatico.ca> writes
>If I understand you correctly, in a nutshell you have a lookup table in one
>table that you must use in another.  Is that right?

Yes.

>If I am right, here is what I did.

[snip]

Thanks for this detailed and comprehensive description. I've implemented
a solution along these lines, and it seems to work well. There doesn't
seem to be too much of a performance hit from the extra SELECT query.

>The biggest difference between what you're doing and what I'm doing is that
>I am doing everything in code using ADO/ADOX, and I have avoided using any
>of the database related VCL components.  

Me too, except that my TADOConnection and TADOQuery objects are all
created by a data module. All the rest is done in code. 

>I haven't looked into using
>parameterized SQL statements.  Do you have any idea how to create and use
>these in code?  

TADOQuery *adoqFoo1;
TADOQuery *adoqFoo2;

...

adoqFoo1->Active = false;
adoqFoo1->SQL->Clear();
adoqFoo1->SQL->Add("INSERT INTO tblFoo (FooSerial,TimeIn)");
adoqFoo1->SQL->Add("VALUES (:FooSerial,:TimeIn)");
adoqFoo1->Prepared = true;

adoqFoo2->Active = false;
adoqFoo2->SQL->Clear();
adoqFoo2->SQL->Add("UPDATE tblFoo SET tblFoo.TimeIn = :TimeIn");
adoqFoo2->SQL->Add("WHERE tblFoo.FooSerial=:FooSerial");
adoqFoo2->Prepared = true;

...

adoqFoo1->Parameters->ParamByName("FooSerial")->Value = ulSern;
adoqFoo1->Parameters->ParamByName("TimeIn")->Value =
TimeToDateTime(timeIn);
adoqFoo1->ExecSQL();

adoqFoo2->Parameters->ParamByName("FooSerial")->Value = ulSern;
adoqFoo2->Parameters->ParamByName("TimeIn")->Value =
TimeToDateTime(timeLog);
adoqFoo2->ExecSQL();

>In my case, it MUST be done in code because the user has the
>liberty to create as many database files as he likes, and I have to create
>the correct structure for each database he creates.  (My application comes
>with one database that is read only, and can create two kinds of database
>for user data, one of which is unique and the other can be represented in as
>many files as the user wishes, which will depend on how the user conceives
>of relationships in his data - imagine a folder full of such files as
>representing a bookshelf full of recipe books).

How do you create your new databases? Are you doing

Catalog=Variant::CreateObject("ADOX.Catalog");
Table=Variant::CreateObject("ADOX.Table");

or have you discovered another method?
-- 
Simon Elliott
http://www.ctsn.co.uk/