🚀 go-pugleaf

Ted Byers <r.ted.byers@sympatico.ca> writes
>> How do you create your new databases? 

[snip code]

Thanks for this. Exactly what I need! Do you know which versions of MS
Access are covered by "Microsoft.Jet.OLEDB.4.0"? 

>But the key here is the statement "RecipeCatalog.Exec(Function("Create") <<
>ConnectionString);" when creating a new database while using the statement
>"RecipeCatalogTMP.Exec(PropertySet("ActiveConnection") << tmpCS);" when
>merely opening an existing one.  And, of course, when creating a new
>database, I must (or prefer to) use a series of SQL statements to create the
>entire structure of the database, from tables and indices to stored
>procedures 

I have a quite complex database which I'd like to be able to create. Do
I have to spend the time writing all the SQL statements to create the
tables, or is there some function of MS Access which allows me to export
the structure?

>(when I find a need for these - I am beginnng to wonder if I may
>find a performance improvement for one of my functions that has to do a
>large numer of lookups from one of my tables if I create a stored procedure
>or parameterized query for it).

I found prepared, parameterized queries to be a lot faster than using
the Append, set values, Post route. I haven't tried stored procedures
yet so don't know how performance would compare. 

One odd wrinkle with TADOQuery SQL queries: if you have a database field
called "Language", SQL insert queries will fail with a parse error. But
the MS Access SQL reference doesn't show "Language" as a reserved
word...

>It seems to me that if I can do everything with the database using a
>catalog, a connection, or a record set, submitting the tasks to the database
>as SQL statements through one of these three kinds of objects, I should
>become less vulnerable to changes made in ADO/ADOX, and it should become
>easier to adapt my code to other means of connecting to databases (such as
>via SQLAPI++).  Plus I feel a need to learn much more about SQL.  I try to
>make my code as independant of proprietary stuff as possible (witness the
>controversy that has raged in the CBX and cppbuilder nontechnical newsgroups
> for the past few months regarding FUD about the fate of VCL).  I figure
>that my code will have a longer usable life if I use the proprietary stuff
>only when absolutely necessary (e.g. to quickly develop a given application)
>and try to use languages for which there is an ANSI/ISO standard whenever
>possible.  In this context, for example, I don't see a significant advantage
>in using "Table=Variant::CreateObject("ADOX.Table");" when I can use a
>simple SQL statement instead.  Similarly, I don't see an advantage to using
>"TADOQuery".  

I don't know how to construct a parameterized query directly, without
using TADOQuery.

>I am not sure I believe that using it will reduce the amount
>of code I will have to write.  In contrast, using the VCL forms and edit
>boxes and comboboxes, &c., does provide a significant advantage since I can
>get a functional form in a matter of hours where, if I had to develop the
>same thing using the WinAPI or even MFC, it would take me days, weeks or
>even months (depending on the complexity of what I was doing).  If I am not
>mistaken, I can execute any SQL statement, that doesn't return a record set,
>using a connection object, and a recordset object otherwise.  And this
>requires only to write the SQL statements and to create the catalog and
>connection objects (and record set object when needed).

This seems like the safest way to proceed. When developing BCB
applications, I'm usually fairly careful to separate the VCL parts from
the rest, which I try to code in standard C/C++ for reuse elsewhere. But
I don't yet know enough about ADO to be able to stop using TADOQuery.

Getting back to ADO, I'm currently developing a multi-threaded
application, each thread of which must save data to the database. A
small test application showed me that as soon as you start to multi-
thread ADO calls, all sorts of mayhem ensues. If you serialise the ADO
calls with critical sections, the SQL queries take two or three times as
long to run. To overcome this, I run the ADO database code in its own
thread (with calls to CoInitializeEx and CoUninitialize) and have a
thread-safe queue to send data to this thread. Every now and again, it
seems to take ages (up to ten minutes) for the final database close and
CoUninitialize to run. Are you aware of any issues with ADO running in a
non VCL thread?
-- 
Simon Elliott
http://www.ctsn.co.uk/