General upsizing questions

[Petr Danes]

I hope someone can give me a bit of general advice on some fundamental
approaches here.

I have a normal Access database of paleontological records that started out
as a single-user app, grew to a multiple-user, but one-at-a-time app that
lives on a network machine (the users just telephoned each other when there
were use conflicts) and now threatens to become a full-blown network app,
with around a dozen users (not all simultaneous, probably no more than two
or three at a time). The first step, from single use to shared one-at-a-time
use did not require much, the network is insanely under-utilized and
in-house traffic just isn't an issue. Opening the Access database over the
network in its original form was marginally slower than local use, but once
open, no speed difference was noticeable.

This next step is clearly going to be considerably more work. The DB engine
will most likely be MySQL, largely for financial reasons (although I'm
considering SQL Server as well - thoughts on that are also welcome). I have
downloaded and installed it on my development machine, split the app and am
already having speed problems with my more complicated queries.

The main user interface is a linked form-subform. The main table has around
60,000 records, the subtable around 50,000, and about twenty associated
tables feeding comboboxes, some with a couple dozen records, most with
several hundred and a few into the thousands. Some main records have over a
hundred subrecords, others, obviously, have none. The subform shows an
abbreviated view of the records associated with the currently displayed main
record, with some fields from the associated tables concatenated into
single, non-editable text fields. The query for the subform is fairly
complicated, joining most of the associated tables in the database, for the
concatenated fields. (It's included at the end of this post, just for show.
Built with the QBE, but works fine.)

There is a button on the subform that opens a more detailed form in dialog
mode, first saving any current changes on the abbreviated subform, which
allows the user to modify subrecords. It jumps to the record/subrecord
currently displayed on the main form/subform, allows the user to move
forward and backward through the entire database, and when closed, changes
the display on the main form/subform to the record/subrecord it was on when
closed.

All this works fine for a single person using the Access mdb. I don't even
bother compiling it, occasionally a user needs to build a custom query for
something I hadn't anticipated, so I've left it open for them. But for a
real network approach, I'll have to consider the traffic in and out of the
DB engine. So here are a few questions:

1. There are posts in the NG talking about not having a recordsource on a
form until the user does something. Would that be appropriate here? If so,
how is it done? Would I have an unbound form, and use code to fill in the
recordsource property on the form? What would the recordsource be? A query
returning only one record? How about the subform? Does it stay linked,
returning only subrecords associated with the one main record?

2. My main form currently scrolls through the main table in single-view
mode, with the linked subform scrolling the associated records in continuous
view. Would manually modifying the recordsource preclude using the Access
navigation buttons? If so, how does the user get to a record? Ask them each
time for a record identifier? That seems clumsy. Also, what if the user does
not know the proper ID for a record? The ID for main records is a
combination of year and number within year, but the numbers have a variety
of forms, not necessarily consistent. Scrolling allows the user to find a
record easily, even without knowing the exact ID. (The data is a hash of
record-keeping, collected from many people over many years, much of it
pre-dating any sort of modern technology, as far back as the 1800s. A
subrecord may have an ID of 134, 134/a, a/134, BR-134, BR - 134, BR/134 and
so on. It's a zoo in that regard, but is not subject to being changed,
period.) How would the user locate a record? Read the table and load up a
combobox full of IDs? Is that different from simply having the table as a
recordsource?

3. What actually happens in the client-server dialog? When I open a form
with a table as recordsource, will Access request the ENTIRE table at once?
Is the entire table then locked? Or does it request only the record
currently displayed? What about the subform? Are all the associated
subrecords requested at once? All locked? If not, how does the server know
which of the subrecords is being modified and which are just being examined?

4. I do not anticipate much conflict between users. They each have their own
set of records which they maintain and rarely will they be interested in
anyone else's. The associated tables may have info added by various users,
although not often. When such is added, however, other users should have it
available, to keep from adding it again, or in a slightly different form
(e.g., Korycany layer, Korycany formation). When one user adds a record to
such a table, how do other users find out? Try to add it themselves and get
an error? Allow only a special user to add records? That would be too
restrictive here. Now I do it by simply trapping the NotInList event and
popping up a dialog box to allow an addition. Does that work over a network
connection?

I've certainly got a bit of studying to do, but most of the literature I
find deals with specifics of how to do something, which isn't appropriate
for me yet. First I need to decide what, in general, I need to do, and I
haven't found any high-level, overview material dealing with the subject.
The nuts and bolts come later, at the moment I don't even really have a
plan.

Sorry about all the kindergarten-level questions, but the last time I worked
on a real network DB application was decades ago, on a Dec PDP 11/70 running
RSTS/E and programming in Basic+2. Things have changed a bit since then.

Pete
--
This e-mail address is fake, to keep spammers and their auto-harvesters out
of my hair. If you want to get in touch personally, I am 'pdanes' and I use
yahoo mail. But please use the newsgroup when possible, so that all may
benefit from the exchange of ideas.



Query for subform:

Akces - main table - 60,000 records
AkcesPodrobnosti - subtable - 50,000 records
TableOfxxxxxxx - associated tables for comboboxes - several dozen to several
thousand records each

SELECT Akces.Rok, Akces.Akcesit, AkcesPodrobnosti.*, [system]+" " &
[series]+" " & [stage]+" " & [substage]+" " & [LithographicUnit]+" " &
[LithographicSubUnit]+" " & [zone] AS Stratigrafie, [Group]+" " & [Order]+"
" & [family]+" " & [genus]+" " & [subgenus]+" " & [species]+" " &
[subspecies]+" " & [Authors] AS Taxonomie, TableOfCountrys.Cesky+" - " &
TableOfCountrys.Country AS Kountry, [EvidenceLetter]+" " &
Trim(Str([EvidenceNumber]))+" " & "("+[EvidenceExtra]+")" AS 2Evidence,
[Rok]+"-" & [akcesit] AS Akces, [Building]+" " & [Room]+" " & [Cabinet]+" "
& [Drawer] AS Lokace
FROM Akces INNER JOIN (((((((((((((((AkcesPodrobnosti LEFT JOIN
TableOfSystems ON AkcesPodrobnosti.SystemAutoID =
TableOfSystems.SystemAutoID) LEFT JOIN TableOfSeriess ON
AkcesPodrobnosti.SeriesAutoID = TableOfSeriess.SeriesAutoID) LEFT JOIN
TableOfStages ON AkcesPodrobnosti.StageAutoID = TableOfStages.StageAutoID)
LEFT JOIN TableOfSubStages ON AkcesPodrobnosti.SubStageAutoID =
TableOfSubStages.SubStageAutoID) LEFT JOIN TableOfLithographicUnits ON
AkcesPodrobnosti.LithographicUnitAutoID =
TableOfLithographicUnits.LithographicUnitAutoID) LEFT JOIN
TableOfLithographicSubUnits ON AkcesPodrobnosti.LithographicSubUnitAutoID =
TableOfLithographicSubUnits.LithographicSubUnitAutoID) LEFT JOIN
TableOfZones ON AkcesPodrobnosti.ZoneAutoID = TableOfZones.ZoneAutoID) LEFT
JOIN TableOfGroups ON AkcesPodrobnosti.GroupAutoID =
TableOfGroups.GroupAutoID) LEFT JOIN TableOfOrders ON
AkcesPodrobnosti.OrderAutoID = TableOfOrders.OrderAutoID) LEFT JOIN
TableOfFamilys ON AkcesPodrobnosti.FamilyAutoID =
TableOfFamilys.FamilyAutoID) LEFT JOIN TableOfGenuss ON
AkcesPodrobnosti.GenusAutoID = TableOfGenuss.GenusAutoID) LEFT JOIN
TableOfSubGenuss ON AkcesPodrobnosti.SubGenusAutoID =
TableOfSubGenuss.SubGenusAutoID) LEFT JOIN TableOfSpeciess ON
AkcesPodrobnosti.SpeciesAutoID = TableOfSpeciess.SpeciesAutoID) LEFT JOIN
TableOfSubSpeciess ON AkcesPodrobnosti.SubSpeciesAutoID =
TableOfSubSpeciess.SubSpeciesAutoID) LEFT JOIN TableOfCountrys ON
AkcesPodrobnosti.CountryAutoID = TableOfCountrys.CountryAutoID) ON
Akces.AkcesAutoID = AkcesPodrobnosti.AkcesAutoID
ORDER BY Akces.Rok, Akces.Akcesit, AkcesPodrobnosti.EvidenceLetter,
AkcesPodrobnosti.EvidenceNumber, AkcesPodrobnosti.EvidenceExtra;

[Armen Stein]

Hi Petr,

There are a lot of questions here. I can't answer them all, but I'll
take a shot at some of them.

Post by Petr Danes
and now threatens to become a full-blown network app,
with around a dozen users (not all simultaneous, probably no more than two
or three at a time).

This happens all the time in the evolution of Access database
applications. The useful ones collect more users until they need to
be upsized, improved and/or rewritten. So congratulations on having
the "problem" of a useful application!

Post by Petr Danes
The DB engine
will most likely be MySQL, largely for financial reasons (although I'm
considering SQL Server as well - thoughts on that are also welcome). I have
downloaded and installed it on my development machine, split the app and am
already having speed problems with my more complicated queries.

Which version did you install? Express is free, but is limited by
memory (1GB), CPU (1) and disk space. Developer is $49 and is
equivalent to Enterprise but with a development-only licensing
restriction.

Our experience is that even the Express version can significantly
outperform JET if the application and database are designed correctly,
but even Enterprise can bog down if there are problems with the
design.

Post by Petr Danes
1. There are posts in the NG talking about not having a recordsource on a
form until the user does something. Would that be appropriate here? If so,
how is it done? Would I have an unbound form, and use code to fill in the
recordsource property on the form? What would the recordsource be? A query
returning only one record? How about the subform? Does it stay linked,
returning only subrecords associated with the one main record?

Yes, some developers suggest having a blank recordsource, and only
filling it in at runtime. We don't usually use that approach, as
there are other ways to get better performance and it's a hassle not
to have the recordsource when designing. One quick way is to set the
Where clause to WHERE 1=0 whenever the form or report closes, then
replace it with one that actually returns records when appropriate.

Post by Petr Danes
2. My main form currently scrolls through the main table in single-view
mode, with the linked subform scrolling the associated records in continuous
view. Would manually modifying the recordsource preclude using the Access
navigation buttons? If so, how does the user get to a record?

We use a read-only continuous form to allow the user to find the
record they want, then drill down to a detail form to edit that
record. The read-only form (we call it an "index" form) uses a
passthrough query, which is very fast. We also have sort & selection
options on that form. We modify the passthrough query object itself
to apply the WHERE clauses.

The detail form is opened to only the ONE selected record. We built
our own "DVD" navigation buttons. They navigate the records on the
hidden index form, and change the recordsource on the detail form to
each new record's key.

Post by Petr Danes
3. What actually happens in the client-server dialog? When I open a form
with a table as recordsource, will Access request the ENTIRE table at once?
Is the entire table then locked? Or does it request only the record
currently displayed?

If you just use a linked table with optimistic locking (the default),
Access will fetch a few hundred records at a time. That's why it's
important to use a passthrough (with the NOLOCK hint) to display many
records, and a detail form with ONE record for editing.

Post by Petr Danes
What about the subform? Are all the associated
subrecords requested at once? All locked? If not, how does the server know
which of the subrecords is being modified and which are just being examined?

Again, Access manages them a few hundred at a time. However, if you
solve the performance problem of the main record, we haven't found the
child records to be as much of a problem.

Post by Petr Danes
Now I do it by simply trapping the NotInList event and
popping up a dialog box to allow an addition. Does that work over a network
connection?

Yes, "add on the fly" works fine with a SQL Server back-end database,
as does all the normal record locking you would expect.

Post by Petr Danes
I've certainly got a bit of studying to do, but most of the literature I
find deals with specifics of how to do something, which isn't appropriate
for me yet. First I need to decide what, in general, I need to do, and I
haven't found any high-level, overview material dealing with the subject.
The nuts and bolts come later, at the moment I don't even really have a
plan.

Here's some high-level material we've developed:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

See the TechEd DAT303 session, and "Best of Both Worlds" slide deck.

Post by Petr Danes
Sorry about all the kindergarten-level questions

No problem! There are some tricky techniques in this area, and you're
right that they're aren't a lot of overview guides. But I want to
encourage you. We've built very complex systems with many more users
and records than you are describing, with excellent results. You'll
just have to change your thinking to always consider the amount of
data that is moving over the wire.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Petr Danes]

Hello Armen,

thank you for your response. I've some comments and a few more questions
inline, if you would be so kind.

Post by Armen Stein
This happens all the time in the evolution of Access
database applications. The useful ones collect more
users until they need to be upsized, improved and/or
rewritten. So congratulations on having the
"problem" of a useful application!

Thanks. This actually started out as an Excel spreadsheet, I came onboard
when it outgrew that and needed to be ported to Access. It was just a quick
fix for the spreadsheet, at first. I had no idea that it would ever grow to
this extent, and it may be in for future expansion as well.

Post by Armen Stein

The DB engine will most likely be MySQL, largely
for financial reasons (although I'm considering
SQL Server as well - thoughts on that are also
welcome). I have downloaded and installed it on my
development machine, split the app and am already
having speed problems with my more complicated
queries.

Which version did you install? Express is free, but
is limited by memory (1GB), CPU (1) and disk space.
Developer is $49 and is equivalent to Enterprise but
with a development-only licensing restriction.

MySQL 5.0 and SQL Server Express. Playing with both, have zero experience
with both, but they both look neat. The data here is well within the Express
size limits. I'm looking at MySQL for another DB that is getting too big for
its britches, and the DB size there is an issue. Here it's the multi-user
aspect that's forcing me away from the Jet engine, there it's data volume
and retrieval speed, but I want to retain the Access front end for both. I
don't think I can persuade the end user of the big one to shell out for the
Enterprise version of SQL Server, especially since there is a free
alternative.

Post by Armen Stein
Our experience is that even the Express version can
significantly outperform JET if the application and
database are designed correctly, but even Enterprise
can bog down if there are problems with the design.

So I suspected, which is why I'm trying to make sure I have a good design
before I get too deep into details, then discover I'm in the wrong track.

Post by Armen Stein

1. There are posts in the NG talking about not
having a recordsource on a form until the user
does something. Would that be appropriate here?

We don't usually use that approach, as there are
other ways to get better performance and it's a
hassle not to have the recordsource when designing.
One quick way is to set the Where clause to WHERE
1=0 whenever the form or report closes, then replace
it with one that actually returns records when
appropriate.

I recall seeing mention of that somewhere. But doesn't having a recordsource
with a condition like that result in Access retrieving the entire dataset
from the server in an attempt to find something that matches? Or is Access
smart enough to recognize 1=0 as an impossible condition up front and not
retrieve anything? Or is something else going on that I'm not understanding?

Post by Armen Stein
We use a read-only continuous form to allow the user
to find the record they want, then drill down to a
detail form to edit that record. The read-only form
(we call it an "index" form) uses a passthrough
query, which is very fast. We also have sort &
selection options on that form. We modify the
passthrough query object itself to apply the WHERE
clauses.

Don't you then re-request the entire sub-dataset for a main record on every
record change of the main form? That seems to sort of preclude the user
'browsing' the main form's dataset. At the moment I have the network pretty
much to myself, but this is a fairly large institution and I can't count on
the management's cranio-rectal inversion lasting forever. Sooner or later,
there will be some traffic and I don't want to paint myself into a corner. I
understand how the pass-through query gets executed quickly, but it seems to
me that I might still be returning a lot of data from the server.

Truly, though, I'm not sure that such browsing is a necessary function
anyway. I tend to do it a lot myself when I'm stuck on something and
thumbing through the data gives me something to do while thinking, but I
don't really know as much as I should about how the users are actually
operating this thing. It's hard to get a good idea on that, because when I
visit a user, they want to chat, or get help, because their IT department
(I'm a contractor) is rather a joke. I've no objection to helping, but it's
hard to play the part of a fly on the wall and simply watch what they are
trying to do.

Post by Armen Stein
The detail form is opened to only the ONE selected
record. We built our own "DVD" navigation buttons.
They navigate the records on the hidden index form,
and change the recordsource on the detail form to
each new record's key.

That means the hidden index form is cached, does it not? How do you deal
with other users adding records? Caching is done to keep traffic down, but
if you need to constantly re-request the index to be sure it is up to date,
it seems to rather defeat the benefits of caching.

Post by Armen Stein

3. What actually happens in the client-server
dialog? When I open a form with a table as
recordsource, will Access request the ENTIRE table
at once? Is the entire table then locked? Or does
it request only the record currently displayed?

If you just use a linked table with optimistic
locking (the default), Access will fetch a few
hundred records at a time. That's why it's
important to use a passthrough (with the NOLOCK
hint) to display many records, and a detail form
with ONE record for editing.

Okay, I think I see that. I currently have a similar setup, except the
initial detail subform allows some limited editing, and the comprehensive
detail subform has the entire detail dataset as a recordsource. But it will
not be difficult to make it match what you have described. I'll have to find
out if the comprehensive subform even needs any navigation functions at all,
and if so, code it to get only one record at a time.

Post by Armen Stein
Again, Access manages them a few hundred at a time.
However, if you solve the performance problem of the
main record, we haven't found the child records to
be as much of a problem.

That's good then. My worst case is 5685 subrecords, although that is an
aberration. The next highest is 1607, then 1016 and rapidly down further.
Only 70 records have more than 100 subrecords, the vast majority have less
than 10, and many have none at all. And even when they exist, the subrecords
typically do not have much data. There are 42 fields, over half are simply
foreign keys to autonumber fields in auxiliary tables and a great number of
them are null. It will change, slowly. Much of the reason for the existence
of this DB is to fill in this missing data.

Post by Armen Stein
Yes, "add on the fly" works fine with a SQL Server
back-end database, as does all the normal record
locking you would expect.

Good, that's a relief. But if I have comboboxes loaded from auxiliary
tables, Access will read the entire table for every combobox whenever the
form is opened, will it not? That would be several thousand records every
time the comprehensive detail subform is opened. Would it make sense to copy
those into a local table upon opening the DB and use code to keep checking
the date/time last modified? Or does Access have its own internal cache with
automatic updating to handle that? I've not seen mention of such in any of
my Access literature.

Post by Armen Stein
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

Thanks, I ran across mention of your site previously while digging around in
the archives. All the presentations on that page are already in my computer.

Post by Armen Stein
We've built very complex systems with many more
users and records than you are describing, with
excellent results. You'll just have to change your
thinking to always consider the amount of data that
is moving over the wire.

Yes, that has become clear. What I'm floundering on is reliably determining
when and how much data is actually going to be making that journey, and
under what circumstances. But I'm slowly starting to get a handle on it.

Many thanks, I've got several approaches and tests planned now based on your
help.

Petr

[Armen Stein]

Hi Petr,

Some follow-ups inline...

I don't think I can persuade the end user of the big one to shell out for the
Enterprise version of SQL Server, especially since there is a free
alternative.

There are also two versions between Express (free) and Enterprise
(very expensive): Workgroup and Standard. See:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

But doesn't having a recordsource
with a condition like that result in Access retrieving the entire dataset
from the server in an attempt to find something that matches? Or is Access
smart enough to recognize 1=0 as an impossible condition up front and not
retrieve anything? Or is something else going on that I'm not understanding?

Actually, it isn't Access in this case - it's SQL Server that is smart
enough to see WHERE 1=0 and immediately conclude that it is
mathematically impossible to return any records, so it doesn't. By
the way, I've seen developers do something like WHERE CustomerKey =
-1, which also will return zero records but does cause SQL Server to
look for a -1 just in case.

Post by Armen Stein
We use a read-only continuous form to allow the user
to find the record they want, then drill down to a
detail form to edit that record. The read-only form
(we call it an "index" form) uses a passthrough
query, which is very fast. We also have sort &
selection options on that form. We modify the
passthrough query object itself to apply the WHERE
clauses.

Don't you then re-request the entire sub-dataset for a main record on every
record change of the main form? That seems to sort of preclude the user
'browsing' the main form's dataset.

Yes, but since it's only the child records for one main record, it's
usually okay. Also, again Access will only retrieve a hundred or so
at a time, not all of them.

Truly, though, I'm not sure that such browsing is a necessary function
anyway. I tend to do it a lot myself when I'm stuck on something and
thumbing through the data gives me something to do while thinking, but I
don't really know as much as I should about how the users are actually
operating this thing.

We find that users really like browsing through a set of detail
records, especially when they have useful criteria on their index
form.

Post by Armen Stein
The detail form is opened to only the ONE selected
record. We built our own "DVD" navigation buttons.
They navigate the records on the hidden index form,
and change the recordsource on the detail form to
each new record's key.

That means the hidden index form is cached, does it not? How do you deal
with other users adding records? Caching is done to keep traffic down, but
if you need to constantly re-request the index to be sure it is up to date,
it seems to rather defeat the benefits of caching.

Well, the hidden form is still open, so yes it is cached in a sense.
This would be true regardless - new records aren't displayed on a form
until it is requeried. That's why we put a button on each list form
so that the user can requery it when they like.

But if I have comboboxes loaded from auxiliary
tables, Access will read the entire table for every combobox whenever the
form is opened, will it not? That would be several thousand records every
time the comprehensive detail subform is opened. Would it make sense to copy
those into a local table upon opening the DB and use code to keep checking
the date/time last modified? Or does Access have its own internal cache with
automatic updating to handle that? I've not seen mention of such in any of
my Access literature.

Access will also manage the combobox records a few hundred at a time,
so it may be okay. Depends on how many active combos you have at
once. For even faster performance on comboboxes with many records or
complex joins, we'll sometimes base them on passthrough queries also.

Remember that if you have a tabbed detail form, all the comboboxes on
all the tabs are loaded when the form loads - this can be a heavy hit.
There are coding techniques to avoid this, such as loading the subform
only when then that particular tab is clicked.

Many thanks, I've got several approaches and tests planned now based on your
help.

Good luck!

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Petr Danes]

Hi Armen,

Post by Armen Stein
There are also two versions between Express (free)
and Enterprise (very expensive): Workgroup and
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Got it, thank you.

Post by Armen Stein
Actually, it isn't Access in this case - it's SQL
Server that is smart enough to see WHERE 1=0 and
immediately conclude that it is mathematically
impossible to return any records, so it doesn't. By
the way, I've seen developers do something like
WHERE CustomerKey = -1, which also will return zero
records but does cause SQL Server to look for a -1
just in case.

I see. That must mean you have the form based directly on a pass-through
query, then. It didn't even occur to me that you could do that. Another case
of being blind to the obvious, I suppose.

Post by Armen Stein
Yes, but since it's only the child records for one
main record, it's usually okay. Also, again Access
will only retrieve a hundred or so at a time, not
all of them.

Okay, that should be fine.

Post by Armen Stein
We find that users really like browsing through a
set of detail records, especially when they have
useful criteria on their index form.

Detail, yes. That's necessary, in fact. But maybe not browsing on the main
form. I'll have to see about that. If so, they may just have to put up with
some delays. Alternatively, I could probably code something to only get all
the detail records if the user presses a 'Show detail' button, or possibly
if they pause on a main record longer than maybe one second.

Post by Armen Stein
Well, the hidden form is still open, so yes it is
cached in a sense. This would be true regardless -
new records aren't displayed on a form until it is
requeried. That's why we put a button on each list
form so that the user can requery it when they like.

All right, I'll try that, although I'm not sure the user would know when to
requery. But it may not be necessary; users don't overlap much in their
datasets.

Post by Armen Stein
Access will also manage the combobox records a few
hundred at a time, so it may be okay. Depends on
how many active combos you have at once. For even
faster performance on comboboxes with many records
or complex joins, we'll sometimes base them on
passthrough queries also.

I'll try that as well. But for disallowing duplicates, doesn't Access need
to read in the entire combobox source table? Or does it simply send the
attempted add to the server and get back an error? I guess I don't know what
it did with Jet, either. I assumed the NotInList event was based on Access
looking through the contents of its combobox, since the dropdown allows you
to examine the whole thing. But maybe even in Jet it attempted an add and
got an error. Do you know?

Post by Armen Stein
Remember that if you have a tabbed detail form, all
the comboboxes on all the tabs are loaded when the
form loads - this can be a heavy hit. There are
coding techniques to avoid this, such as loading the
subform only when then that particular tab is
clicked.

No tabs on my subform, everything is on one page, although there are 21
comboboxes. If it loads too slowly, I'll try disabling some of them to see
if it helps.


Thank you again, the fog is gradually lifting.

Petr

[Armen Stein]

Post by Petr Danes
I see. That must mean you have the form based directly on a pass-through
query, then. It didn't even occur to me that you could do that. Another case
of being blind to the obvious, I suppose.

Yes, it can be based directly on a passthrough.

But you can set the Recordsource property to Select * from
qsptMyPassthroughQuery for more flexibility. Then we change the WHERE
clause in the underlying passthrough query using the querydef object
in code. This is smokin' fast.

But we have standard code that switches sorting based on the user
clicking column headings (Outlook-style), and for this we actually
change the Order By clause in the form's recordsource property, not
the underlying query. We haven't seen a significant performance
problem from doing this on the front-end. When we do, we'll figure
out a standardized way to change it in the passthrough instead.

Post by Petr Danes
All right, I'll try that, although I'm not sure the user would know when to
requery. But it may not be necessary; users don't overlap much in their
datasets.

We've found that except for certain situations (e.g. dynamic
reservation or dispatching systems), keeping the recordset requeried
automatically is not necessary. Users do tend to work with their own
data, and they seem to understand that if they want to see the very
latest (usually after conferring with a colleague), they just need to
click the Refresh button we provide. (Note we don't call it Requery -
too technical).

Post by Petr Danes
I'll try that as well. But for disallowing duplicates, doesn't Access need
to read in the entire combobox source table? Or does it simply send the
attempted add to the server and get back an error? I guess I don't know what
it did with Jet, either. I assumed the NotInList event was based on Access
looking through the contents of its combobox, since the dropdown allows you
to examine the whole thing. But maybe even in Jet it attempted an add and
got an error. Do you know?

I imagine that NotInList works because whatever you've typed so far
has caused Access to scroll to that point in the list. So it already
has the relevant records loaded. Regardless, it works reliably when
the back-end is in SQL Server.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Petr Danes]

Great, many thanks for all the tips. Hi ho, hi ho, it's off to code I go...
(sorry, couldn't resist)

Pete

[Tony Toews [MVP]]

Post by Petr Danes
I have a normal Access database of paleontological records that started out
as a single-user app, grew to a multiple-user, but one-at-a-time app that
lives on a network machine (the users just telephoned each other when there
were use conflicts) and now threatens to become a full-blown network app,
with around a dozen users (not all simultaneous, probably no more than two
or three at a time).
This next step is clearly going to be considerably more work. The DB engine
will most likely be MySQL, largely for financial reasons (although I'm
considering SQL Server as well - thoughts on that are also welcome). I have
downloaded and installed it on my development machine, split the app and am
already having speed problems with my more complicated queries.
The main user interface is a linked form-subform. The main table has around
60,000 records, the subtable around 50,000, and about twenty associated
tables feeding comboboxes, some with a couple dozen records, most with
several hundred and a few into the thousands.

For the volume of users and data you have I see no reason to upsize to
another product at this time. Unless you're going to a WAN
environment.

I suspect your speed problems with more complex queries might be due
to lack of proper indexes on the sorting or criteria fields. For
example if you are sorting or searching by date have you added an
index for date in the table?

Tony

[Petr Danes]

Hello Tony,

Thank you for your thoughts on my project. This is a fairly large step, and
unfortunately, the pool of DB experts in this organization consists of me.
Much of what I do falls in my lap because there simply isn't anyone else,
not because I'm such a brilliant analyst/designer/coder. I've gotten fairly
good with standard single-user Access stuff over the years, but several of
my projects either have, or soon will outgrow Access' capabilities. This
particular one isn't there yet, but it's headed that way.

Post by Tony Toews [MVP]
For the volume of users and data you have I see no reason to upsize to
another product at this time. Unless you're going to a WAN
environment.

No, it's going to be all LAN, with possibly a copy of the data periodically
written over to a web server, for read-only access to the data via PHP
forms.

I've read posts in the archives from people using Jet as a network DB
engine. There seems little doubt that it can work, but it seems equally
certain that it is not the most bullet-proof strategy available. However, my
reason for going to SQL Server or MySQL is several-fold:
1. I'm going to need some experience with the current technology of real
network DB servers sooner or later, this seems like as good a time as any to
get started.
2. The DB is being merged from many individuals' single-machine, single-user
Access databases. The data will gain in importance when everyone's
information is gathered in one place, so I think a more robust DB engine is
appropriate.
3. There is good reason to expect considerable growth in volume over the
next few years, I want to design now for expansion, rather than risk hitting
size limits later, at possibly an inopportune time.

Post by Tony Toews [MVP]
I suspect your speed problems with more complex queries might be due
to lack of proper indexes on the sorting or criteria fields.

You're right, the miserable performance was caused by the recordsource of a
form being a query that worked fine in local mode, but choked when talking
to an external DB engine. It wasn't an index problem, specifically, but an
issue with the volume of retrieved data. Armen gave me some pointers on what
sort of problems can crop up with that and I'm working on a redesign of the
workflow right now.

Post by Tony Toews [MVP]
For example if you are sorting or searching by
date have you added an index for date in the table?

Yes, I have indexes everywhere I expect to need them. The queries work just
fine when I pay attention to limiting the amount of data flowing from the DB
engine. My problem was that I wasn't clear on when and how much data moved
under various circumstances.

Pete

[Tony Toews [MVP]]

Post by Petr Danes
I've read posts in the archives from people using Jet as a network DB
engine. There seems little doubt that it can work, but it seems equally
certain that it is not the most bullet-proof strategy available. However, my
1. I'm going to need some experience with the current technology of real
network DB servers sooner or later, this seems like as good a time as any to
get started.
2. The DB is being merged from many individuals' single-machine, single-user
Access databases. The data will gain in importance when everyone's
information is gathered in one place, so I think a more robust DB engine is
appropriate.
3. There is good reason to expect considerable growth in volume over the
next few years, I want to design now for expansion, rather than risk hitting
size limits later, at possibly an inopportune time.

Then those are excellent reasons to consider going to a SQL Server or
MySQL database.

Unless your IT department has a strong preference I'd sure consider
using SQL Server Express. It's free, should handle your volumes quite
nicely and you'll likely have fewer problems as Access and SQL Server
should integrate reasonably well.

Tony

[David W. Fenton]

Post by Tony Toews [MVP]
Unless your IT department has a strong preference I'd sure
consider using SQL Server Express. It's free, should handle your
volumes quite nicely and you'll likely have fewer problems as
Access and SQL Server should integrate reasonably well.

I would second this, as MySQL has severe limitations in its SQL
dialect in comparison to what we're accustomed to with Access/Jet.

[Tony Toews [MVP]]

Post by David W. Fenton

Post by Tony Toews [MVP]
Unless your IT department has a strong preference I'd sure
consider using SQL Server Express. It's free, should handle your
volumes quite nicely and you'll likely have fewer problems as
Access and SQL Server should integrate reasonably well.

I would second this, as MySQL has severe limitations in its SQL
dialect in comparison to what we're accustomed to with Access/Jet.

Ah, interesting. IMNSHO and IIRC there were enough "quirks" between
Jet and SQL Server that I was rather irritated with SQL Server.
<smile>

Tony

[Petr Danes]

Thank you both for your input. My primary reason for considering MySQL is
data volume - SQL Server Express has what I consider a rather restrictive
size limitation. But this DB is nowhere near that limit yet, so I'll start
there. If I need to convert to MySQL later to escape the size limit, it
should be easier from SQL Server than right from Access. And maybe I can
talk someone into coughing up for a stronger version by then.

Unless your IT department has a strong preference...

Not an issue. There is only one guy in the department that knows -anything-
about the subject. He's put together a few PHP-MySQL network apps (which is
another reason I was looking at MySQL). The department head makes Dilbert's
PHB look normal and well-educated. The IT department will not even be
involved in the deployment of my DB, that's how useful and knowledgeable
they are. Mostly what they do is lock their doors, ignore the phones and
play computer games all day.

Pete

[Tony Toews [MVP]]

Post by Petr Danes
Thank you both for your input. My primary reason for considering MySQL is
data volume - SQL Server Express has what I consider a rather restrictive
size limitation.

4 Gb is likely something like 4 to 40 million records. A long ways
from your `120K or so records previously mentioned.

Post by Petr Danes

Unless your IT department has a strong preference...

Not an issue. There is only one guy in the department that knows -anything-
about the subject. He's put together a few PHP-MySQL network apps (which is
another reason I was looking at MySQL). The department head makes Dilbert's
PHB look normal and well-educated. The IT department will not even be
involved in the deployment of my DB, that's how useful and knowledgeable
they are. Mostly what they do is lock their doors, ignore the phones and
play computer games all day.

<giggle> <chuckle> <guffaw> As I reread this last paragraph I
started laughing harder and harder. Yes, it sure sounds like the IT
departments energies are best directed at playing computer games.

Tony