🚀 go-pugleaf

"Simon Elliott" <simon@nospam.demon.co.uk> wrote in message
news:0x0o5DAm3lz$Iww1@courtlands.demon.co.uk...
> 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"?
>
I have no idea.  But I am using MS Access 2K on W2K, with all the latest
updates to both Office and Windows.  I am hoping that backward compatibility
will mean that my program works OK with more recent versions of Windows and
Office.

I know I have a reference book on OLEDB, but I can't find it.  :-(  It seems
some resources are never there when you need them, and in your way when you
don't!  :-(  I suppose that's something they have in common with spouses and
kids and various other family members.  ;-)

> >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?
>
I have no idea if Access has a function that allows you to export the
structure.  You might find the quickest route, though, is to bite the bullet
and just develop the SQL statements to create the tables.  You are, after
all, producing only a few SQL statements per table.  A simple database might
take only a couple hours, and a complex one might take a few days to a few
weeks.  I'd expect, though, that it would take much longer to understand
what the appropriate data model is than it would to figure out the
appropriate SQL to implement it.

> >(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.
>
Hmmmmmmmmm some more testing to do.

> 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...
>
I have found similar issues using SQL statements.  The error message
generated is less than informative.  I had forgotten that MS has an
inclination to make certain words, that are natural names for variables and
fields, reserved.  A while ago, I ran into problems in a C++ program in
which I used a variable called Yield (in an agricultural application).  It
turned out the MS had used in very early in the history of Windows and their
version was visible at the pointer where I was using my own "Yield".
Changing the name of my variable fixed the problem, but it was annoying that
such a word would, in effect, be made off limits by MS' arcane decisions.

> [snip]
> 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?
> --
No.  Whether the code runs in a VCL or non-VCL thread should not matter.
After all, you can even use ADO in VB or Javascript.  It might be worth a
visit to the MSDN website to search the knowledge base there to see if there
are known issues involving using ADO in multithreaded programs, and what the
recommended best practices are.

I do have some performance issues to examine.  On start up, I need to
process some large tables in the only read-only database I use, and that
takes about 30 seconds on my machine.  However, there is one form that needs
to repeatedly access the data and analyze some of it, and this takes quite a
while.  It is inherently slow because I need to retrieve the values from one
field given different combinations of the values of two other fields.
Actually, I THINK I just had an idea that may improve things a bit, but I'll
have to do some testing in the morning.

One thing I'd look at (which may apply to your performance issues also) is
how the SQL statements are structured.  For example, suppose one had to
obtain a series of values of field X given a variety of values of Y and a
number of values of Z.  One COULD get the data using something like:

SELECT X FROM MyTable WHERE Y=1 AND Z=1

But doing that for every combination of Y and Z would likely be slower than
using something like:

SELECT X,Y FROM MyTable WHERE Z = 1

(NB: I haven't double checked this syntax - I don't recall off hand if I
need parentheses around "X,Y")

And now I'm thinking that even better performance may be obtained from
something like:

SELECT X,Y,Z FROM MyTable

What I'm thinking of here is the sort of consideration that enters into how
you handle master-detail relationships and how the way you get and store
data involving such relationships affects network performance.  I'd expect
these issues could potentially have as much impact on getting data to and
from Access on a given machine as it has on network performance when getting
data from Oracle or any other DB server.

Cheers,

Ted