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

[Ted Theodoropoulos]

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.

[Larry Linson]

"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.

[Ted Theodoropoulos]

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:<107155166 3.326546@teutho s>...

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

TC

[Van T. Dinh]

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********@yah oo.com> wrote in message
news:f5******** *************** ***@posting.goo gle.com...

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.

[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

[Ted Theodoropoulos]

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.goo gle.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?

Thks
NB

[TC]

You don't need to open a formb - or a table. Just open the >database<:

dim db as database
set db = dbengine.openda tabase(...)

Make sure db stays in scope for the whole run.

HTH,
TC
"NB" <ni******@lycos .com> wrote in message
news:5c******** *************** **@posting.goog le.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?

Thks
NB

[David W. Fenton]

ni******@lycos. com (NB) wrote in
<5c************ *************@p osting.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?

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

[Tony Toews]

te********@yaho o.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

[DFS]

"Tony Toews" <tt****@teluspl anet.net> wrote in message
news:48******** *************** *********@4ax.c om...

te********@yaho o.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(word String," ")

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