473,320 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Jet or MSDE

NB
Hi

I have been developing on MS Access / Jet / VBA platform for about 2
years.
The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.

The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.

I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.

Thanks
NB
Nov 12 '05 #1
41 2404
On 10 Dec 2003 19:18:19 -0800, ni******@lycos.com (NB) wrote:

If it ain't broke, don't fix it.
-Tom.

Hi

I have been developing on MS Access / Jet / VBA platform for about 2
years.
The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.

The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.

I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.

Thanks
NB


Nov 12 '05 #2
ni******@lycos.com (NB) wrote:
The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.
Puny. <smile> A client has 150 tables, 1200 queries, 450 forms, 350 reports last
time I looked. Backend is about 300-350 Mb in size. 15-25 users including five via
Terminal Server. Some running A97 others A2000.
The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.
Like Tom said "Ain't broke, don't fix"
I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?


It's SQL Server but restricted to five processes. It'll be a lot more work to get
it going. Especially if you have functions in queries.

However if your client has mission critical data where they can't go to last nights
backup, ie telephone call sales, hotel reservations, whatever, then that is how you
should go.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #3

I maintain an mdb with a large number of objects myself and was asking the
same question - to MSDE or not to MSDE? Because the size limitation is the
same (2Gb) there's not much to gain. From what I've read, MSDE gives you
better security and is better at accommodating a larger number of users.
But you might as well go to a full SQL server backend if those are critical
needs.
"NB" <ni******@lycos.com> wrote in message
news:5c**************************@posting.google.c om...
Hi

I have been developing on MS Access / Jet / VBA platform for about 2
years.
The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.

The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.

I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.

Thanks
NB

Nov 12 '05 #4
For "a larger number of users", you may need the full MS-SQL Server (license
required) rather than the MSDE. MSDE is restricted to 5 processes so it
won't be suitable for a large number of users.

--
HTH
Van T. Dinh

"deko" <dj****@hotmail.com> wrote in message
news:4o*******************@newssvr25.news.prodigy. com...

I maintain an mdb with a large number of objects myself and was asking the
same question - to MSDE or not to MSDE? Because the size limitation is the same (2Gb) there's not much to gain. From what I've read, MSDE gives you
better security and is better at accommodating a larger number of users.
But you might as well go to a full SQL server backend if those are critical needs.
"NB" <ni******@lycos.com> wrote in message
news:5c**************************@posting.google.c om...
Hi

I have been developing on MS Access / Jet / VBA platform for about 2
years.
The current project I am working on for a small business is built on
Access 2002. It has about 53 tables in 2 backend MDB files, 125
queries, 109 forms & subforms, 43 reports & subreports, 20 code
modules and some macros.

The size of the backend is now at a modest size of about 4MB (down
from an original size of 270MB when the previous folk kept images
inside it). But it's growing, though I predict not at a very high
speed.

The backend files are hosted on a Win 2000 PC running Access 2002.
Current number of users is 6. The app has appeared robust up to now.

I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.

Thanks
NB


Nov 12 '05 #5
"Van T. Dinh" <Va***********@PlseUseNewsGroup.bigpond.com> wrote:
For "a larger number of users", you may need the full MS-SQL Server (license
required) rather than the MSDE. MSDE is restricted to 5 processes so it
won't be suitable for a large number of users.


But there are reports of up to 100 users using an MSDE backend.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #6
Yes, it a varying "larger number of users" depending on the how dynamic the
data is.

Personally, I would never recommend MSDE for even 25-30 users.

--
Cheers
Van
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:ur********************************@4ax.com...

But there are reports of up to 100 users using an MSDE backend.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Nov 12 '05 #7
users should not even enter the equation except possibly as a
coefficient. the number of concurrent processes are what's important.
concurrent users doesn't necessarily mean concurrent connections and
it certainly doesn't mean concurrent processes. i've yet to see solid
testing data around MSDE but i have used in successfully in several
situations including one or two production environments but admittedly
they were small.

having the front end sql tools is manditory, however. don't even
think about trying to create, maintain, and benchmark your db using
sql statements. u need QA, EM and Profiler. it takes a lot of work
(and somewhat defeats the purpose) to create a completely disconnected
front-end in access. i'm of the opinion that if you're going to put
in that kind of effort you should think about .NET where u have much
more power at your disposal.

u lose the reporting in access which is great. let me stop right
there. u didn't ask about the front end and i don't want to start
another VB vs Access war. to answer your question, YES, msde is worth
investigating but don't underestimate the work involved. if you're
not having problems, i'd wait a bit.

"Van T. Dinh" <Va***********@discussions.microsoft.com> wrote in message news:<s8*****************@news-server.bigpond.net.au>...
Yes, it a varying "larger number of users" depending on the how dynamic the
data is.

Personally, I would never recommend MSDE for even 25-30 users.

--
Cheers
Van
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:ur********************************@4ax.com...

But there are reports of up to 100 users using an MSDE backend.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Nov 12 '05 #8
Ted,

And, why should the number of users _not_ be considered, since it is almost
impossible to determine or predict the number of concurrent processes? The
largest number of concurrent users I've seen reliably reported was in early
releases of MSDE when it was just _limited_ to 5 concurrent processes, not,
as now, just had delays inserted after 5 -- that was 25 users, reported by
Roger Jennings in one of his "Special Edition - Using Access..." books.

I have never seen anyone else, whethere I'd consider them a reliable source
or not, claim any more MSDE users. I have seen reliable reports (Michael
Kaplan, Stephen Forte, Mike Groh, and others) of 100+ concurrent users with
Access - Jet multiuser, and have routinely seen reports of 30 - 70
concurrent users.

And, without grabbing the box for my Developer Edition, I believe it
includes the _Developer_ Edition of SQL Server, which does include all the
excellent adminstrative tools. You can develop with that, then deploy with
the Desktop Edition of SQL Server (aka MSDE). And, if the audience grows,
it's a slam-dunk to move to full SQL Server.

But, frankly, all my clients who understood the need to have a server
database weren't put off by the cost of MS SQL Server for moderate size
audiences (or other "true" server databases; some, like the old version of
Sybase SQL Anywhere, were modestly priced and easily handled 25 - 50 users).

If I understand correctly, .NET is overkill for a client-server database
configuration; certainly, it is marketed not for the "small stuff" but for
enterprise-level distributed applications. Looks, from reports on the beta
of the next release that Microsoft's "third time's the charm" history is
repeating itself. That one is, reportedly, much easier for the developer and
better implemented, too, for response and stability.

Larry Linson
Microsoft Access MVP

"Ted Theodoropoulos" <te********@yahoo.com> wrote in message
news:f5**************************@posting.google.c om...
users should not even enter the equation except possibly as a
coefficient. the number of concurrent processes are what's important.
concurrent users doesn't necessarily mean concurrent connections and
it certainly doesn't mean concurrent processes. i've yet to see solid
testing data around MSDE but i have used in successfully in several
situations including one or two production environments but admittedly
they were small.

having the front end sql tools is manditory, however. don't even
think about trying to create, maintain, and benchmark your db using
sql statements. u need QA, EM and Profiler. it takes a lot of work
(and somewhat defeats the purpose) to create a completely disconnected
front-end in access. i'm of the opinion that if you're going to put
in that kind of effort you should think about .NET where u have much
more power at your disposal.

u lose the reporting in access which is great. let me stop right
there. u didn't ask about the front end and i don't want to start
another VB vs Access war. to answer your question, YES, msde is worth
investigating but don't underestimate the work involved. if you're
not having problems, i'd wait a bit.

"Van T. Dinh" <Va***********@discussions.microsoft.com> wrote in message

news:<s8*****************@news-server.bigpond.net.au>...
Yes, it a varying "larger number of users" depending on the how dynamic the data is.

Personally, I would never recommend MSDE for even 25-30 users.

--
Cheers
Van
"Tony Toews" <tt****@telusplanet.net> wrote in message
news:ur********************************@4ax.com...

But there are reports of up to 100 users using an MSDE backend.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Nov 12 '05 #9
RE/
I heard about the MSDE engine. Can someone give me some pointer if I
should consider using it instead of the Jet engine that comes default
with Access?
BTW the code in my app uses mainly DAO library.


Others have said that they've developed apps with C/S back ends successfully
using 100% DAO. Certainly thats the fastest and least-expensive way to get an
app up and running.

I've only done one major app with a SQL Server back end. For that one, I bit
the bullet and tried to push as much processing as I could back to the server
via stored procedures. I've still got some DAO in the "System"/"Table
Maintainence" screens because it just didn't seem right to burn up a lot of
billable hours on something that only one or two people would ever use...but the
part of the app that's exposed to Joe User is 100% stored procedure driven.

So far, I've found development of stored procedures to be a real time sink. My
take is that the development manhours increase by a good 30-50% when stored
procedures are used instead of DAO QueryDefs. Maybe I'll discover some tool
to speed it up and certainly my expertise will improve....but developing one of
those things will always take much, much, much longer than an MS Access
QueryDef.

I'm hoping for another opportunity to so an SQL Server or MSDE-based app just
for the practice. If I keep on doing it like I did with the first one - making
the client as thin as possible i.e. pushing as much processing as I can back to
the server - it seems like I'm positioning myself for a transition to N-tier
development if/when that time comes. I'm already looking forward to my current
contract expiring sometime this spring so I can spend a couple months rewriting
that first SQL Server app in .NET just to get a feel for it.

To stop rambling and address your question, I can see a few reasons to develop
with an MSDE back end:

1) IT Prejudice. A lot of IT orgs despise "Access Databases" - not realizing
the distinction between MS-Access-The-Front-End-Development-Tool and JET, the
file-based DB engine that is often used with it. Having an MSDE back end that
can be migrated to SQL Server probably makes some of them feel a little better.

2) Concurrent Users. Once something is developed in the MSDE, it can be
migrated transparently to SQL Server. People can argue about how many users
JET and/or MSDE can support - but SQL Server can support a *lot* of users while
I've never heard anybody claim that JET or MSDE can support over 50.
Personally, I think more like 10 for JET...although there are people who say
they run 30 or so...probably depends on what those users are doing.

3) Control-Type Security. By restricting access to stored procedures/views,
you can butten up a SQL Server DB really tight.

4) Security Administration: You can base SQL Server security on LAN UserIDs.
Much simpler to administer from the LAN guy's perspective.

5) Data-Safety Security. With JET, LAN and local hardware problems are always
hanging over your head. I've had one app run over 7 years, 6-10 concurrent
users every day and never had a corrupted back end. In fact the *only* time it
went down was when somebody yanked on the wrong cable in a LAN closet - and then
it was back up and running within the hour. OTOH I've had other apps that went
through episodes of repeated corruption. On one it went away when we finally
got The Powers That Be to let us move it to another server. A lot of smart
people tried to find the problem, but never did.

6) Recovery. With SQL Server you can have point-in-time recovery. OTOH, it's
not trivial compared to restoring a JET DB from last nite's backup. OTOOH, if
your JET DB goes down at 4:27 in the afternoon you've probably lost a day's
work.

7) Dialup. I've never tried it, but I wouldn't even *think* about running a
JET backend over a phone line. Citrix aside, it seems to me like - in spite of
what the users say today - that there's a pretty good chance they'll want some
applications available over a phone line sometime in the future.

8) Performance as load increases. JET is fast when things are light and then
bogs down as traffic increases. MSDE or SQL Server, while maybe not as fast
with a light load, can handle heavier loads without degrading. Also, since
it's all happening on the server, you have the option of upgrading the box to
improve performance.

9) Parochial Issues. Getting one's feet wet with MSDE or SQL Server can't hurt
your technical development....
--
PeteCresswell
Nov 12 '05 #10

Pete,

On Sat, 13 Dec 2003 14:06:58 GMT, "(Pete Cresswell)" <x@y.z> wrote in
comp.databases.ms-access:
7) Dialup. I've never tried it, but I wouldn't even *think* about running a
JET backend over a phone line. Citrix aside, it seems to me like - in spite of
what the users say today - that there's a pretty good chance they'll want some
applications available over a phone line sometime in the future.


I'm in agreement with all your other points (snipped here), but had
something to add on the item above.

Yes, Access can run over a dialup/wan/vpn, technically. And although
you (Pete) may never have tried this, *way* too many people have, and
have concluded that because it *can* work, that in fact it *does* work
and is a usable implementation scenario. Oh are they making a big
mistake. Obviously the slower the connection, the more likely the
problems, but anything short of a decent speed lan is just a plain
dumb way to use Access.

To reiterate, in an Access app (where jet is used for storage, which
is the default), ALL processing occurs on the client, so if you have a
front-end on a remote pc, and you access the backend over a network
with high latency (internet, wan, dialup, with or without a vpn), you
are drastically increasing the liklihood that the front-end will have
some failed i/o as a result of the slower network response times.
When such an i/o problem occurs, there is a decent chance it will
result in corruption of the backend - something that would not have
happened if (a) a true rdbms was used or (b) the connection was over a
reliable high speed lan. Since we're talking about connection types
here (a) is immaterial.

If an Access app needed to be used over a wan/internet/dialup/<insert
other slow network type>, the way to do it is by keeping the client
and server on the same side of the slow netowrk connection. That is
best done by using (on the higher end) terminal server, citrix, or the
like or (on the low-end) remote control software like pc anywhere,
remote desktops, etc). This doesn't make the network any more stable,
but it does limit any connection problems so that they do not directly
cause corruption of the database.

So if the reader insists upon using their Access app over a slow
network, with the client on one end and the backend on the other, by
all means, please take down my number, because there's a significant
chance corruption will result and, if its not repairable by Access
itself, you'll need a recovery service like ours, but a much smarter
move would be to implement the thing correctly and not subject
yourself to unnecessary corruption risks.

I know you know this, Pete, but it seems like a good place in the
thread to stress this for potential readers who may be unsure about
the wisdom of such implementations.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051
Nov 12 '05 #11
Larry expressed it much better than I could. Surely more (concurrent) users
would require more processes???

--
HTH
Van T. Dinh
MVP (Access)


"Ted Theodoropoulos" <te********@yahoo.com> wrote in message
news:f5**************************@posting.google.c om...
users should not even enter the equation except possibly as a
coefficient. the number of concurrent processes are what's important.
concurrent users doesn't necessarily mean concurrent connections and
it certainly doesn't mean concurrent processes. i've yet to see solid
testing data around MSDE but i have used in successfully in several
situations including one or two production environments but admittedly
they were small.

having the front end sql tools is manditory, however. don't even
think about trying to create, maintain, and benchmark your db using
sql statements. u need QA, EM and Profiler. it takes a lot of work
(and somewhat defeats the purpose) to create a completely disconnected
front-end in access. i'm of the opinion that if you're going to put
in that kind of effort you should think about .NET where u have much
more power at your disposal.

u lose the reporting in access which is great. let me stop right
there. u didn't ask about the front end and i don't want to start
another VB vs Access war. to answer your question, YES, msde is worth
investigating but don't underestimate the work involved. if you're
not having problems, i'd wait a bit.


Nov 12 '05 #12
x@y.z ((Pete Cresswell)) wrote in
<7d********************************@4ax.com>:
RE/
I heard about the MSDE engine. Can someone give me some pointer
if I should consider using it instead of the Jet engine that
comes default with Access?
BTW the code in my app uses mainly DAO library.
Others have said that they've developed apps with C/S back ends
successfully using 100% DAO. Certainly thats the fastest and
least-expensive way to get an app up and running.

I've only done one major app with a SQL Server back end. For that
one, I bit the bullet and tried to push as much processing as I
could back to the server via stored procedures. I've still got
some DAO in the "System"/"Table Maintainence" screens because it
just didn't seem right to burn up a lot of billable hours on
something that only one or two people would ever use...but the
part of the app that's exposed to Joe User is 100% stored
procedure driven.

So far, I've found development of stored procedures to be a real
time sink. My take is that the development manhours increase by a
good 30-50% when stored procedures are used instead of DAO
QueryDefs. Maybe I'll discover some tool to speed it up and
certainly my expertise will improve....but developing one of those
things will always take much, much, much longer than an MS Access
QueryDef.


Why not write everything as a traditional stored query, let Jet try
to make it efficient, and then move to the server the ones that Jet
fails to successfully optimize?

[]
2) Concurrent Users. Once something is developed in the MSDE, it
can be migrated transparently to SQL Server. People can argue
about how many users JET and/or MSDE can support - but SQL Server
can support a *lot* of users while I've never heard anybody claim
that JET or MSDE can support over 50. Personally, I think more
like 10 for JET...although there are people who say they run 30 or
so...probably depends on what those users are doing.
Mostly it depends on how you architect your app. If you use
procedures that work well on C/S, it will also make a very
efficient Jet app. The chief principle is: never load more data
than you really need at one time, which basically means request one
record at a time for main forms, never binding to full tables
(i.e., no WHERE clause). Unbound forms may get you increased
concurrency, but are seldom worth the effort -- and that's true for
both Jet and C/S.

[]
6) Recovery. With SQL Server you can have point-in-time
recovery. OTOH, it's not trivial compared to restoring a JET DB
from last nite's backup. OTOOH, if your JET DB goes down at 4:27
in the afternoon you've probably lost a day's work.
First off, I've only twice had clients lose data and we didn't in
either case send the files to Peter Miller. In one case we had
already recovered all but 3 records via backups, and in another, it
was long before I knew Peter existed. I've only twice sent clients
to Peter, and only once did Peter need to do anything (in the
other, it turned out we could still synch the replicated MDB with
another in the replica set -- Peter pointed out this possibility,
as I'd completely forgotten the file was replicated, and didn't
charge the client).

My clients have lost *time* in recovering from backups and waiting
for Peter to recover data, and it has cost money to recover from
corruption, but, as you say, restoring a server-based database
ain't exactly a triviality, either.
7) Dialup. I've never tried it, but I wouldn't even *think*
about running a JET backend over a phone line. Citrix aside, it
seems to me like - in spite of what the users say today - that
there's a pretty good chance they'll want some applications
available over a phone line sometime in the future.
Dialup networking is never ever going to work for Access, but
Windows Terminal Server over dialup is certainly fast enough to
work with and completely safe, as the Access application is never
opened across the dialup connection.

But if I were, in fact, contemplating deploying an app to remote
users via WTS, I would certainly recommend that both sites have
broadband Internet access -- it's just more reliable and easier
than dialup. It's probably cheaper in the long run, too, if you
need a telephone line for each user.
8) Performance as load increases. JET is fast when things are
light and then bogs down as traffic increases. MSDE or SQL
Server, while maybe not as fast with a light load, can handle
heavier loads without degrading. Also, since it's all happening
on the server, you have the option of upgrading the box to improve
performance.
That all depends on the architecture of the Access application. I
could write an Access app that could bring a SQL Server to its
knees. The efficient techniques that make a C/S app work well also
benefit a Jet application.
9) Parochial Issues. Getting one's feet wet with MSDE or SQL
Server can't hurt your technical development....


An MSDE app would be architected differently from a Jet app and
would have the advantage of having the appropriate architecture for
SQL Server should you switch later.

But you can also design an app to run against Jet with an upsizing
in mind, and it will be a pretty good Jet app, too.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #13
RE/
if the reader insists upon using their Access app over a slow
network, with the client on one end and the backend on the other, by
all means, please take down my number, because there's a significant
chance corruption will result and, if its not repairable by Access
itself, you'll need a recovery service like ours, but a much smarter
move would be to implement the thing correctly and not subject
yourself to unnecessary corruption risks.

I know you know this, Pete,


Actually, I didn't know about the slow network part...and that may explain a
couple of mysterious app failures I've had.

Thanks for the insight.

The place where I've done most of my MS Access development tends to LANs that
are as slow as death.....And the app I was taking about that's been running all
these years with no problem was in the Bond Trading area of the company - where
they're extremely parochial and have a lot of control over their environment.
i.e. their LANs aren't that slow....
--
PeteCresswell
Nov 12 '05 #14
"David W. Fenton" wrote
Why not write everything as a traditional
stored query, let Jet try to make it efficient,
and then move to the server the ones that Jet
fails to successfully optimize?
This mode of operation worked well for a team on which I worked for several
years. In fact, we found that Jet, even Jet 2.5, was pretty darn good at
generating efficient ODBC queries. As I recall, the only case where we _had_
to intervene was "complex queries" on reports, and we did that with Views,
just to force the many joins to be done on the server. Views, in my
experience, are far simpler and quicker to create than stored procedures.
Mostly it depends on how you architect
your app. If you use procedures that work
well on C/S, it will also make a very
efficient Jet app. The chief principle is: never
load more data than you really need at one
time, which basically means request one
record at a time for main forms, never
binding to full tables (i.e., no WHERE
clause). Unbound forms may get you increased
concurrency, but are seldom worth the effort --
and that's true for both Jet and C/S.


I agree and would comment that limiting the data by using queries and WHERE
clauses in SQL may not _visibly_ increase performance on a single-user
standalone app, it usually does not take that much more effort than opening
a form on a whole table or query, either.

Larry Linson
Microsoft Access MVP
Nov 12 '05 #15
bo*****@localhost.not (Larry Linson) wrote in
<Ou******************@nwrddc01.gnilink.net>:
limiting the data by using queries and WHERE
clauses in SQL may not _visibly_ increase performance on a
single-user standalone app, it usually does not take that much
more effort than opening a form on a whole table or query, either.


Somewhere in ADH97 I believe they say that speed of loading data
one record at a time versus navigating a full data set tips over in
favor of the single record in the neighborhood of 20K records. I
can't find the reference right now, but it was burned into my brain
in a project from a few years ago where we started out with 6K
records in the main table and were contemplating ways to improve
performance/concurrency. We thought of going unbound but based on
what I understood from ADH97 at the time, it would only really pay
off with a noticeable performance increase after we more than
tripled the number of records in the main data table.

Maybe it was a rule of thumb for unbound forms -- I just don't
remember -- and it was unbound that we were contemplating (because
of some concurrency problems).

It's also one of those numbers that would be highly contingent on a
number of features of the structure of your data table, the
architecture of your network and the forms you are using to load
the data. Any number of parameters could cause the efficiency to
kick in sooner or later than the 20K number, and probably by a very
wide margin of variability.

But as an order of magnitude figure, I thought it was fairly
useful.

I have since been working with apps with much more data and find
that loading one record at a time is extremely efficient with 100s
of thousands of records.

And this is all-Jet I'm talking about.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #16
Perhaps that is a valid figure. I certainly haven't done any experimentation
either to confirm or to argue.

But, no matter what size the table, I've never noticed a visible slowdown on
the monitor from selecting single/few records instead of all. That's not to
say there may not be a _measurable_ difference, just that it's probably so
miniscule, you don't notice it.

I have seen a visible difference when opening one/zero records in a
client-server environment, with fewer than 20,000 records in the table. As I
recall, the main table in that application had between 15,000 and 16,000
rather large records. But, in the production system, the data was retrieved
via a WAN, with varying speed links... the outsourced contractors had a T1
(approx 1.5 MBPS) telephone line shared between several users on that WAN.
They thought it was blazingly fast, but that was only in comparison to the
56K leased line that the T1 had replaced. <G>

It might be like those "blazingly fast" lightweight forms that can save 5 -
15 milliseconds of form load time. <G> Faster, but should we care? If
somebody at Redmond didn't blush in embarrassment when Mike Groh published
those results, they just have no shame. <G>

Just intuitively, I would have thought the crossover-point number would be
considerably smaller than 20,000 (depending, of course, on record size).

Larry Linson

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
bo*****@localhost.not (Larry Linson) wrote in
<Ou******************@nwrddc01.gnilink.net>:
limiting the data by using queries and WHERE
clauses in SQL may not _visibly_ increase performance on a
single-user standalone app, it usually does not take that much
more effort than opening a form on a whole table or query, either.


Somewhere in ADH97 I believe they say that speed of loading data
one record at a time versus navigating a full data set tips over in
favor of the single record in the neighborhood of 20K records. I
can't find the reference right now, but it was burned into my brain
in a project from a few years ago where we started out with 6K
records in the main table and were contemplating ways to improve
performance/concurrency. We thought of going unbound but based on
what I understood from ADH97 at the time, it would only really pay
off with a noticeable performance increase after we more than
tripled the number of records in the main data table.

Maybe it was a rule of thumb for unbound forms -- I just don't
remember -- and it was unbound that we were contemplating (because
of some concurrency problems).

It's also one of those numbers that would be highly contingent on a
number of features of the structure of your data table, the
architecture of your network and the forms you are using to load
the data. Any number of parameters could cause the efficiency to
kick in sooner or later than the 20K number, and probably by a very
wide margin of variability.

But as an order of magnitude figure, I thought it was fairly
useful.

I have since been working with apps with much more data and find
that loading one record at a time is extremely efficient with 100s
of thousands of records.

And this is all-Jet I'm talking about.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #17
NB
Now I see that I'd better leave my app as it is.

One question: how does T-SQL differ from PL/SQL as I only have
experience in Oracle PL/SQL and haven't touched SQL Server.

Thanks for all the opinion

NB
Nov 12 '05 #18
Server database's SQL all tend to be somewhat closer to ANSI - SQL than
Jet's, but each one has some "flavor" of its own. I haven't worked with
PL/SQL, so I couldn't comment on the differences.

"NB" <ni******@lycos.com> wrote in message
news:5c**************************@posting.google.c om...
Now I see that I'd better leave my app as it is.

One question: how does T-SQL differ from PL/SQL as I only have
experience in Oracle PL/SQL and haven't touched SQL Server.

Thanks for all the opinion

NB

Nov 12 '05 #19
TC
PL/SQL is basically just Oracle's normal SQL, embedded (made available)
within a procedural environment (if's, else, case, procedures, etc.).

TC
"Larry Linson" <bo*****@localhost.not> wrote in message
news:C%*******************@nwrddc01.gnilink.net...
Server database's SQL all tend to be somewhat closer to ANSI - SQL than
Jet's, but each one has some "flavor" of its own. I haven't worked with
PL/SQL, so I couldn't comment on the differences.

"NB" <ni******@lycos.com> wrote in message
news:5c**************************@posting.google.c om...
Now I see that I'd better leave my app as it is.

One question: how does T-SQL differ from PL/SQL as I only have
experience in Oracle PL/SQL and haven't touched SQL Server.

Thanks for all the opinion

NB


Nov 12 '05 #20
there is no linear relationship between users and processes. in a
disconnected app for example, i have as many as 150 concurrent users
and i've only had the number of concurrent connections exceed 40 once.
now mind you there is a difference between connections and processes.
from what i have read, MSDE throttles when processes, not connections
exceed 5. when you use connection pooling and disconnected recordsets
each of your users can go several minutes without needing to grab a
connection from the pool and initiate a process.

the most important thing that needs to be considered when calculating
concurrent processes is the nature of the app. in access things are
obviously a little different. but if you're using ado.net, for
example, you can have reads and updates cached in the data adapter,
you can have defaults invoked and u can even have constraints and
referential integrity (i beleive) enforced all client side WITHOUT
initiating a process on the server. it's almost like a replicated
environment. when u invoke the update method of the data adapter
(similar to the updatebatch method of the recordset object) that's
when the server process(es) are initiated.

once that is figured out, then the number of users comes in to play as
a coefficient. my point is that u can't simply take the number of
users without determining the nature of the app. i see people make
that mistake all the time.
Nov 12 '05 #21
"TC" wrote
PL/SQL is basically just Oracle's normal
SQL, embedded (made available)
within a procedural environment (if's, else,
case, procedures, etc.).


That's also what T-SQL is for MS SQL Server, but there are, unless I am
mistaken, some differences here and there... and I don't know the details.
Nov 12 '05 #22
there are quite a few difference between pl/sql and t-sql even though
they accomplish the same thing. not all of these differences are
syntactical. i'm a die hard sql server user but have to conceed that
pl/sql has a slight edge as far as functionality goes. as a quick
example, certain set operators in pl/sql such as the MINUS operator
function are a PITA to work around in t-sql when you are subtracting
several sub sets from the main dataset.

if u r really interested in the differences go to
comp.databases.ms-sqlserver and search. there are several oracle
trolls over there that are constantly posting this 10+ page manifest
on the advantages of pl over t-sql. some of the points in this
manifest are utter bullshit and some are very accurate. definately
worth a read.

"TC" <a@b.c.d> wrote in message news:<1071551663.326546@teuthos>...
PL/SQL is basically just Oracle's normal SQL, embedded (made available)
within a procedural environment (if's, else, case, procedures, etc.).

TC

Nov 12 '05 #23
Yes. If you look at my earlier post, I wrote that it depends on how dynamic
the data is.

Also, if you have, says 5 users doing updates and the average usage of
processes is 1 for example, would you say that it is reasonable to
extrapolate that for 10 users doing updates, the average usage of process is
at least greater than 1 and likely to 2.

Personally, I tend to be conservative with the number of users because I
look at it this way: the license fee for the MS-SQL Server is fairly
reasonable compared to other development costs, most notably my fee /
salary. So, why would I want to throttle the application and the client
might think that the application I developed is not efficient or worst, not
up to scratch ...

--
Cheers
Van T. Dinh

"Ted Theodoropoulos" <te********@yahoo.com> wrote in message
news:f5**************************@posting.google.c om...
there is no linear relationship between users and processes. in a
disconnected app for example, i have as many as 150 concurrent users
and i've only had the number of concurrent connections exceed 40 once.
now mind you there is a difference between connections and processes.
from what i have read, MSDE throttles when processes, not connections
exceed 5. when you use connection pooling and disconnected recordsets
each of your users can go several minutes without needing to grab a
connection from the pool and initiate a process.

the most important thing that needs to be considered when calculating
concurrent processes is the nature of the app. in access things are
obviously a little different. but if you're using ado.net, for
example, you can have reads and updates cached in the data adapter,
you can have defaults invoked and u can even have constraints and
referential integrity (i beleive) enforced all client side WITHOUT
initiating a process on the server. it's almost like a replicated
environment. when u invoke the update method of the data adapter
(similar to the updatebatch method of the recordset object) that's
when the server process(es) are initiated.

once that is figured out, then the number of users comes in to play as
a coefficient. my point is that u can't simply take the number of
users without determining the nature of the app. i see people make
that mistake all the time.

Nov 12 '05 #24
NB
Back to the concurrent processes: Upon opening my app, I open a hidden
form and maintain a recordset from a table on the backend until the
app is closed. By doing that I significantly improve forms' load time
on users' front end.

The question is if I still have to do that with an MSDE back-end.
Would my very-complex form load time worsen without a persistent
back-end connection? And that would count as one process, right?

Thks
NB
Nov 12 '05 #25
are u talking about doing this in an ADP or MDB? if it's an MDB, are
you going to use pass thru or a linked tables? honestly though, it
really shouldn't matter just having a form open with a bound recordset
without edits being made. the best way to tell is to test it using
profiler. that's a sql server client tool that u can use to run
traces that will tell u when process begin and end.

ni******@lycos.com (NB) wrote in message news:<5c*************************@posting.google.c om>...
Back to the concurrent processes: Upon opening my app, I open a hidden
form and maintain a recordset from a table on the backend until the
app is closed. By doing that I significantly improve forms' load time
on users' front end.

The question is if I still have to do that with an MSDE back-end.
Would my very-complex form load time worsen without a persistent
back-end connection? And that would count as one process, right?

Thks
NB

Nov 12 '05 #26
TC
You don't need to open a formb - or a table. Just open the >database<:

dim db as database
set db = dbengine.opendatabase(...)

Make sure db stays in scope for the whole run.

HTH,
TC
"NB" <ni******@lycos.com> wrote in message
news:5c*************************@posting.google.co m...
Back to the concurrent processes: Upon opening my app, I open a hidden
form and maintain a recordset from a table on the backend until the
app is closed. By doing that I significantly improve forms' load time
on users' front end.

The question is if I still have to do that with an MSDE back-end.
Would my very-complex form load time worsen without a persistent
back-end connection? And that would count as one process, right?

Thks
NB

Nov 12 '05 #27
ni******@lycos.com (NB) wrote in
<5c*************************@posting.google.com> :
Back to the concurrent processes: Upon opening my app, I open a
hidden form and maintain a recordset from a table on the backend
until the app is closed. By doing that I significantly improve
forms' load time on users' front end.

The question is if I still have to do that with an MSDE back-end.
Would my very-complex form load time worsen without a persistent
back-end connection? And that would count as one process, right?


No, it shouldn't be necessary with MSDE. The whole point of doing
it with Jet is to make sure the LDB file is created and persists,
as the time it takes to delete and recreate that file can be pretty
significant.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #28
te********@yahoo.com (Ted Theodoropoulos) wrote:
if u r really interested in the differences go to
comp.databases.ms-sqlserver and search. there are several oracle
trolls over there that are constantly posting this 10+ page manifest
on the advantages of pl over t-sql. some of the points in this
manifest are utter bullshit and some are very accurate. definately
worth a read.


I remember reading a paper from Borland about the advantages of Paradox, I think it
was a Windows version, over Access. Back in A1.0/2.0 days. They had some useful
ideas but then they stated that Paradox had something like 17 different data types
but Access had only five or six or something like that. Of course Access's number
type of field has byte, integer, long, single and double as subtypes. So when you
compared them there was only one or two differences. Which I thought was really
misleading on Borland's part.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #29
DFS

"Tony Toews" <tt****@telusplanet.net> wrote in message
news:48********************************@4ax.com...
te********@yahoo.com (Ted Theodoropoulos) wrote:
if u r really interested in the differences go to
comp.databases.ms-sqlserver and search. there are several oracle
trolls over there that are constantly posting this 10+ page manifest
on the advantages of pl over t-sql. some of the points in this
manifest are utter bullshit and some are very accurate. definately
worth a read.
I remember reading a paper from Borland about the advantages of Paradox, I

think it was a Windows version, over Access. Back in A1.0/2.0 days. They had some useful ideas but then they stated that Paradox had something like 17 different data types but Access had only five or six or something like that. Of course Access's number type of field has byte, integer, long, single and double as subtypes. So when you compared them there was only one or two differences. Which I thought was really misleading on Borland's part.
Borland has created some superior programs: from Turbo Pascal to Paradox for
Windows to Delphi to C++Builder/JBuilder to Kylix, but their marketing and
tech. support leaves a lot to be desired.

I used PDox for Windows for a couple of years; the included ObjectPAL
language was a powerful, Delphi-like scripting language. It had some useful
methods in it that (I believe) MS still hasn't added to VB or VBA. For
instance, it had a string function called breakApart() that would parse a
string, using spaces or any other identifier, and place the individual words
or phrases into array addresses.

var wordString String, array wordArray(50) String
wordString = "The network is the computer."
wordArray = breakApart(wordString," ")

result:
wordArray(0) = "The"
wordArray(1) = "network"
wordArray(2) = "is"
wordArray(3) = "the"
wordArray(4) = "computer."

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Nov 12 '05 #30

On Sat, 20 Dec 2003 22:36:31 -0500, "DFS" <no****@nospam.com> wrote in
comp.databases.ms-access:
I used PDox for Windows for a couple of years; the included ObjectPAL
language was a powerful, Delphi-like scripting language. It had some useful
methods in it that (I believe) MS still hasn't added to VB or VBA. For
instance, it had a string function called breakApart() that would parse a
string, using spaces or any other identifier, and place the individual words
or phrases into array addresses.
Well, there's the split function, but I take your point that its not
whether vb/vba support this sort of thing now, but the value of
introducing such things early on that you value.
var wordString String, array wordArray(50) String
wordString = "The network is the computer."
wordArray = breakApart(wordString," ")

result:
wordArray(0) = "The"
wordArray(1) = "network"
wordArray(2) = "is"
wordArray(3) = "the"
wordArray(4) = "computer."


I don't know if I'd call such a method something worth noting in
comparing languages or development tools. Normally, it would make
sense to reference things available in one language or tool that are
unavailable, or effectively unavailable on the other. Things like OO,
pointers, specialized data types, etc seem relevant. But something
like BreakApart is exactly the sort of thing that, if you find it
useful (and I don't question for an instance that one could find it
useful), it's terribly simple to just write the few lines of code to
do this and put it in your standard programmer's utility module that
you drop into any project you work on (or add to a code repository
tool like that from FMS if you haven't implemented your own).

In VBA this sort of thing is as easy as:

Sub BreakApart(byref a$(), b$, c$)
dim i&, j&, k&
i=instr(b,c)
while i
a(k)=mid(b,j+1,i-j-1)
k = k + 1
j = i
i = mid(i+1,b,c)
wend
if k>0 then a(k)=mid(i+1,b)
end sub

and could be called like:

BreakApart wordArray,wordString," "

Of course, the code above doesn't reset the array's non-used elements
or handle longer than one character separators but that sort of thing
is easy enough to address.

You might say, 'yeah, but nine lines of code is more than one line'
which is true enough, but more point is that things that are easily
added to a language are not important missing elements. Its things
that can't be added, or can't easily be added that really limit an
environment.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051
Nov 12 '05 #31
DFS
"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:qo********************************@4ax.com...

On Sat, 20 Dec 2003 22:36:31 -0500, "DFS" <no****@nospam.com> wrote in
comp.databases.ms-access:
I used PDox for Windows for a couple of years; the included ObjectPAL
language was a powerful, Delphi-like scripting language. It had some usefulmethods in it that (I believe) MS still hasn't added to VB or VBA. For
instance, it had a string function called breakApart() that would parse a
string, using spaces or any other identifier, and place the individual wordsor phrases into array addresses.
Well, there's the split function,


Wasn't aware of it. It's not in A97. I do see it in A2000, a mere 7 years
after it was included in ObjectPAL. VBA2000 also has the related Join
function, which re/assembles the values FROM an array, using a delimiter.

but I take your point that its not
whether vb/vba support this sort of thing now, but the value of
introducing such things early on that you value.
What he said. PDoxWin had that feature in 1993. It also natively supported
drag-n-drop, I think (my memory's not so great, its' been at least 5 years
since I've looked at PDox).
var wordString String, array wordArray(50) String
wordString = "The network is the computer."
wordArray = breakApart(wordString," ")

result:
wordArray(0) = "The"
wordArray(1) = "network"
wordArray(2) = "is"
wordArray(3) = "the"
wordArray(4) = "computer."


I don't know if I'd call such a method something worth noting in
comparing languages or development tools.


I would. Regardless of how easy you can mimic it, the fact that such
methods and features are included for you is an indication of a superior
product. Not digging at Access - I've made a living doing Access/VB
development for the last 8 years - but PDoxWin was a good desktop db system
and development environment.
Normally, it would make
sense to reference things available in one language or tool that are
unavailable, or effectively unavailable on the other. Things like OO,
pointers, specialized data types, etc seem relevant.
I remember thinking it was a slick development environment for a database
language. It had some interesting graphic objects (circles, triangles, etc)
and methods, too. It also supported grouping screen objects together
(finally introduced in A2000). It had an easy to use FileBrowser object,
and many other goodies as I recall.


But something
like BreakApart is exactly the sort of thing that, if you find it
useful (and I don't question for an instance that one could find it
useful), it's terribly simple to just write the few lines of code to
do this and put it in your standard programmer's utility module that
you drop into any project you work on (or add to a code repository
tool like that from FMS if you haven't implemented your own).

In VBA this sort of thing is as easy as:

Sub BreakApart(byref a$(), b$, c$)
dim i&, j&, k&
i=instr(b,c)
while i
a(k)=mid(b,j+1,i-j-1)
k = k + 1
j = i
i = mid(i+1,b,c)
wend
if k>0 then a(k)=mid(i+1,b)
end sub

and could be called like:

BreakApart wordArray,wordString," "
Yes. I wrote my own similar version a few years ago, in VB.
Of course, the code above doesn't reset the array's non-used elements
or handle longer than one character separators but that sort of thing
is easy enough to address.

You might say, 'yeah, but nine lines of code is more than one line'
which is true enough, but more point is that things that are easily
added to a language are not important missing elements. Its things
that can't be added, or can't easily be added that really limit an
environment.
"Easy" is in the eye of the user. It still can be relatively difficult to
use .ocx and ActiveX controls.
Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

Nov 12 '05 #32
The ldb creation/destruction issue may be part of it with JET (I've
got no experience with it) but I also noticed that without the
constant connection to the backend that my front ends were creating 2
connections to the backend tables. Not pretty when 30 or more users
were performing updates.

Strangely enough the problem came about when I 'cleaned up' the
original code and made sure all the objects were used in the smallest
scope needed, and set to nothing explicitly.

Opening a persitant global connection reduced the connections back to
one again. Also if the forms were opened and then closed only one
connection would be maintained; beats me.

This was with A97 (no server involved).

I was measuring connections by what the msldbuser.dll was calling a
connection/user (active) via a VB6 app I've developed to count and log
connections to the various mdb's I administer.

Obviously connecting to a database other than JET would be a whole
different kettle of fish.

Peter

dX********@bway.net.invalid (David W. Fenton) wrote in message news:<94***************************@24.168.128.78> ...
ni******@lycos.com (NB) wrote in
<5c*************************@posting.google.com> :
Back to the concurrent processes: Upon opening my app, I open a
hidden form and maintain a recordset from a table on the backend
until the app is closed. By doing that I significantly improve
forms' load time on users' front end.

The question is if I still have to do that with an MSDE back-end.
Would my very-complex form load time worsen without a persistent
back-end connection? And that would count as one process, right?


No, it shouldn't be necessary with MSDE. The whole point of doing
it with Jet is to make sure the LDB file is created and persists,
as the time it takes to delete and recreate that file can be pretty
significant.

Nov 12 '05 #33
Pi*************@mail.com (Pink Panther) wrote in
<ec**************************@posting.google.com >:
The ldb creation/destruction issue may be part of it with JET
(I've got no experience with it) but I also noticed that without
the constant connection to the backend that my front ends were
creating 2 connections to the backend tables. Not pretty when 30
or more users were performing updates.

Strangely enough the problem came about when I 'cleaned up' the
original code and made sure all the objects were used in the
smallest scope needed, and set to nothing explicitly.


I've never in any of my applications seen any performance benefit
from the persistent connection, so I don't use it, so I'm not
really qualified to say.

I can say that I've seen cases where MSLDBUSR.DLL showed double
logons, and it eventually stopped happening, but I never figured
out what caused it and what made it go away.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #34
no****@nospam.com (DFS) wrote in
<vu************@corp.supernews.com>:
"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:qo********************************@4ax.com.. .

On Sat, 20 Dec 2003 22:36:31 -0500, "DFS" <no****@nospam.com>
wrote in comp.databases.ms-access:
>I used PDox for Windows for a couple of years; the included
>ObjectPAL language was a powerful, Delphi-like scripting
>language. It had someuseful >methods in it that (I believe) MS still hasn't added to VB or
>VBA. For instance, it had a string function called
>breakApart() that would parse a string, using spaces or any
>other identifier, and place the individualwords >or phrases into array addresses.


Well, there's the split function,


Wasn't aware of it. It's not in A97. I do see it in A2000, a
mere 7 years after it was included in ObjectPAL. VBA2000 also has
the related Join function, which re/assembles the values FROM an
array, using a delimiter.


Split() doesn't put the results in an array.

In Pdox for DOS, PAL had a SubStr function that you could pass an
input, a separator string and two variables, and SubStr would put
the part before the separator string in the first variable and the
part after it in the second variable. I created my own SubStr
replacement shortly after I started working in Access. As Peter
points out, it's relatively trivial to do that kind of thing, as
compared to basic structural issues (like data types) that you
can't really alter yourself.
but I take your point that its not
whether vb/vba support this sort of thing now, but the value of
introducing such things early on that you value.


What he said. PDoxWin had that feature in 1993. It also natively
supported drag-n-drop, I think (my memory's not so great, its'
been at least 5 years since I've looked at PDox).


It's too bad Borland couldn't figure out a way to convince existing
Paradox developers to stay with the platform. PDoxWin was actually
a pretty good first effort at a Windows database -- Access would
not have been nearly as good without the things they copied from
it. And, I do believe that the right-click properties dialog that
is now ubiquitous in MS products was first introduced as the
properties inspector in an early release of PDoxWin.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #35
DFS
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
no****@nospam.com (DFS) wrote in
<vu************@corp.supernews.com>:
"Peter Miller" <pm*****@pksolutions.com> wrote in message
news:qo********************************@4ax.com.. .

On Sat, 20 Dec 2003 22:36:31 -0500, "DFS" <no****@nospam.com>
wrote in comp.databases.ms-access:

>I used PDox for Windows for a couple of years; the included
>ObjectPAL language was a powerful, Delphi-like scripting
>language. It had someuseful
>methods in it that (I believe) MS still hasn't added to VB or
>VBA. For instance, it had a string function called
>breakApart() that would parse a string, using spaces or any
>other identifier, and place the individual

words
>or phrases into array addresses.

Well, there's the split function,


Wasn't aware of it. It's not in A97. I do see it in A2000, a
mere 7 years after it was included in ObjectPAL. VBA2000 also has
the related Join function, which re/assembles the values FROM an
array, using a delimiter.


Split() doesn't put the results in an array.


After a brief try I can't figure out how to use Split, but the A2000 help
says Split "Returns a zero-based, one-dimensional array containing a
specified number of substrings"


In Pdox for DOS, PAL had a SubStr function that you could pass an
input, a separator string and two variables, and SubStr would put
the part before the separator string in the first variable and the
part after it in the second variable. I created my own SubStr
replacement shortly after I started working in Access. As Peter
points out, it's relatively trivial to do that kind of thing, as
compared to basic structural issues (like data types) that you
can't really alter yourself.
but I take your point that its not
whether vb/vba support this sort of thing now, but the value of
introducing such things early on that you value.


What he said. PDoxWin had that feature in 1993. It also natively
supported drag-n-drop, I think (my memory's not so great, its'
been at least 5 years since I've looked at PDox).


It's too bad Borland couldn't figure out a way to convince existing
Paradox developers to stay with the platform. PDoxWin was actually
a pretty good first effort at a Windows database -- Access would
not have been nearly as good without the things they copied from
it. And, I do believe that the right-click properties dialog that
is now ubiquitous in MS products was first introduced as the
properties inspector in an early release of PDoxWin.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

Nov 12 '05 #36
"David W. Fenton" wrote
It's too bad Borland couldn't figure out
a way to convince existing Paradox
developers to stay with the platform.
PDoxWin was actually a pretty good
first effort at a Windows database -- Access
would not have been nearly as good
without the things they copied from
it. And, I do believe that the right-click
properties dialog that is now ubiquitous
in MS products was first introduced as the
properties inspector in an early release
of PDoxWin.


I was a loyal user of Paradox in the DOS world -- my first copy was "Ansa
Paradox" before Borland bought Ansa to get the Paradox product. I thought it
was clearly the best DOS database for a power user (and that was my status
with PDOX -- I took a PAL class, but never considered myself a developer).

Paradox lost out Windows-wise for three reasons, in my view. (1) Microsoft
beat them to market with Access, and it was as good in Windows as PDOX had
been in DOS (2) In the first 4.5 releases of PDOX/WIN, they "never got
Windows right", IMNSHO. (3) They had financial trouble and clients feared
using software from a company that "might go under".

And, they didn't help themselves with their developer customers by replacing
PAL with Object Pascal -- not at all similar. PDOX / DOS developers I knew
were very hacked off at Borland about that. But, if their customers had been
willling to pay for Paradox applications, they'd have gotten over it and
stuck with the product, anyway.

Of all the developers I met at the Paradox Users of North Texas user group,
only two didn't move to Access. Those two just moved on to developing
applications in C and C++. Some of them gave Delphi a close look, but they
just couldn't stir up enough interest to pursue it (same reason #3 as
above).

Larry Linson
Nov 12 '05 #37
rkc

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
no****@nospam.com (DFS) wrote in
<vu************@corp.supernews.com>: Split() doesn't put the results in an array.


What does it do then?
Nov 12 '05 #38
rkc previously wrote:

"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
no****@nospam.com (DFS) wrote in
<vu************@corp.supernews.com>:

Split() doesn't put the results in an array.


What does it do then?


On this side of the pond it puts the results into an array.

Peter Russell

Nov 12 '05 #39
bo*****@localhost.not (Larry Linson) wrote in
<Cp******************@nwrddc01.gnilink.net>:
"David W. Fenton" wrote
It's too bad Borland couldn't figure out
a way to convince existing Paradox
developers to stay with the platform.
PDoxWin was actually a pretty good
first effort at a Windows database -- Access
would not have been nearly as good
without the things they copied from
it. And, I do believe that the right-click
properties dialog that is now ubiquitous
in MS products was first introduced as the
properties inspector in an early release
of PDoxWin.
I was a loyal user of Paradox in the DOS world -- my first copy
was "Ansa Paradox" before Borland bought Ansa to get the Paradox
product. I thought it was clearly the best DOS database for a
power user (and that was my status with PDOX -- I took a PAL
class, but never considered myself a developer).


I was paid to develop in Paradox, but did it only for my employer
and myself.
Paradox lost out Windows-wise for three reasons, in my view. (1)
Microsoft beat them to market with Access, . . .
But they didn't. Paradox for Windows came out before the first
release of Access.
. . . and it was as good in
Windows as PDOX had been in DOS. . .
Microsoft certainly knew how to write programs for Windows better
than anyone else.
. . . (2) In the first 4.5 releases of
PDOX/WIN, they "never got Windows right", IMNSHO. . . .
Oops! Jumped the gun!
. . . (3) They had
financial trouble and clients feared using software from a company
that "might go under".
Well, there was also the problem that they created a completely new
version of PAL for PDoxWin, of necessity, in order to be event
driven (no more WAIT needed).
And, they didn't help themselves with their developer customers by
replacing PAL with Object Pascal -- not at all similar. . .
It was ObjectPAL, not Pascal, an enhanced version of PAL to work in
an event-driven, GUI environment.
. . . PDOX / DOS
developers I knew were very hacked off at Borland about that. But,
if their customers had been willling to pay for Paradox
applications, they'd have gotten over it and stuck with the
product, anyway.
Well, by the second release or so, Access was out, and it was just
a lot easier. I stopped with PDox3.5 for DOS and never upgraded to
4.0 (which was, apparently, a good upgrade, though). I was
frustrated with users who wanted to use the mouse and wanted a
Windows program, so I sat on the sidelines until Access 2, though I
did fiddle around a bit with PDoxWin.
Of all the developers I met at the Paradox Users of North Texas
user group, only two didn't move to Access. Those two just moved
on to developing applications in C and C++. Some of them gave
Delphi a close look, but they just couldn't stir up enough
interest to pursue it (same reason #3 as above).


Access was a better product because it was better integrated with
Windows and was designed with Windows standards from the ground up.
This was an advantage MS had over any other software maker, of
course.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #40
ru***@127.0.0.1 (Peter Russell) wrote in
<me**********************@russellscott.btinternet. com>:
rkc previously wrote:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.78.. .
> no****@nospam.com (DFS) wrote in
> <vu************@corp.supernews.com>:

> Split() doesn't put the results in an array.


What does it do then?


On this side of the pond it puts the results into an array.


Sorry, I mis-spoke. I have not used Split(), just looked at it,
because I'm too wedded to coding for my own version of it written
before VBA had such a function.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #41
The results are stored as a one dimensional array. I have no idea
what Fenton is talking about.
Split() doesn't put the results in an array.


What does it do then?

Nov 12 '05 #42

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: izzy | last post by:
I was wondering if any of you guys can kindly help me in finding all the different versions of MSDE 2000 that came out since it's first release. I expected to find something similar like Sun's...
5
by: Igor Solodovnikov | last post by:
Hi. I am trying to automatically backup transaction log when error 9002 happened. So i have created appropriate job and alert to catch this error. I have two instances of sql server under Windows...
10
by: noname | last post by:
MSDE 2000 Release A installed under windows 2000 pro will not communicate with SQL Server Manager nor MS Access on peer computer. Can someone help? Have set DISABLENETWORKPROTOCOLS=0 at install...
3
by: *no spam* | last post by:
I want to move my Access 2K database into MSDE. The Access Upsizing Wizard crashes (a known bug wi A2K), so I'm using the following suggested method: Access --> New --> Project (Existing...
5
by: Robin Tucker | last post by:
I'm looking for a simple way of telling (inside a stored procedure) if I'm currently using MSDE or a full SQL server. Ideally, there is some pre-defined environment variable that won't cause me...
7
by: Diogo Alves - Software Developer | last post by:
hi there I am developing a software that needs a database to be shared, I've heard about MSDE, can someone tell me if * There is a newer version than MSDE 2000? * It's possible to use MSDE...
2
by: Rosy Moss | last post by:
I am in the process of cleaning up a database that our company uses to track jobs, time and expense, and customer information. We are running Windows 2000 Server with approximately 20 terminals...
3
by: Paul Aspinall | last post by:
Hi I want to package my C# winforms app, to be deployed with MSDE, as easily as possible for the end user. I want to create an MSI or Installshield (prefer MSI), to setup my C# app, together...
4
by: Anthony P. Mancini | last post by:
Does anyone know how to make the MSDE do SQL authentication ? It appears to authenticate using Windows at all times. Thanks, Anthony
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.