howto - "modularize design" of front-end application ??

[Bob]

sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.

my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and end-
user performance.

There are a couple of "sections" to the application which could easily
(from a logical perspective) be "modularized" (ie: there's an import/
export functionality).

So - what I would like to do, is move this functionality into its own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.

Is there some way to "dll-ize" a chunk of this app?

if not - what IS the best way to handle an application that is
becoming very large?

TIA - Bob

 

[Brian]

Can you define "very large"? How big is the front end in terms of bytes?

If it is getting large because of the natural growth of DB's over time (even
the front end, due to internal temporary storage used by Access), you could
look at automating a compact/repair when the front end closes.

If there are just too many functions in the front end, you might take a look
reorganizing your app so that functions are categorized and split onto menus
-> submenus -> action forms.

 

[Bob]

hi Brian;

TX for your reply. very large in terms of the quantity of queries,
and forms.
The actual size of the app is around 5M.

I suppose my biggest concern over this, is oriented toward managing
the app on the dev. side. I think performance issues could probably be
mostly dealt with by migrating the back-end into a sql-server.

There are a vast number of queries (and forms) to try to keep track
of. Which does what for which function and so on.
It would be nice to have those functions which are separat-able in
different files - but I wouldn't want to have to merge multiple files
to produce a final mde each time changes are made.

Can you define "very large"? How big is the front end in terms of bytes?

If it is getting large because of the natural growth of DB's over time (even
the front end, due to internal temporary storage used by Access), you could
look at automating a compact/repair when the front end closes.

If there are just too many functions in the front end, you might take a look
reorganizing your app so that functions are categorized and split onto menus
-> submenus -> action forms.

sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.

my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and end-
user performance.

There are a couple of "sections" to the application which could easily
(from a logical perspective) be "modularized" (ie: there's an import/
export functionality).

So - what I would like to do, is move this functionality into its own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.

Is there some way to "dll-ize" a chunk of this app?

if not - what IS the best way to handle an application that is
becoming very large?

TIA - Bob

 

[Arvin Meyer [MVP]]

Actually, 5 MB is tiny, almost infinitesimal, for a database. Even 50 MB is
considered small.

I built a large database for a homebuilder consisting of 12 front-end
applications, linked to a single 100+ MB Access/JET database on the server.
A total of 50 users had from 1 to 3 front-end mdb's open at any given time.
I divided by apps by function. The Permitting dept, maintained the
Subdivisions and Lots, and the housing Starts. There was a Bidding
department, a Service Dept., a Purchasing Dept. a Sales Dept. Closing Dept,
etc., etc.

Code common to everyone, or at least multiple applications, was stored in a
Code database which was referenced by the apps that needed it. A total of
about 60 MB in the 12 database apps kept each to between 3 MB and 8 or 9 MB,
and as such each was secure from the others, and loaded and ran much faster
due to their smaller sizes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

hi Brian;

TX for your reply. very large in terms of the quantity of queries,
and forms.
The actual size of the app is around 5M.

I suppose my biggest concern over this, is oriented toward managing
the app on the dev. side. I think performance issues could probably be
mostly dealt with by migrating the back-end into a sql-server.

There are a vast number of queries (and forms) to try to keep track
of. Which does what for which function and so on.
It would be nice to have those functions which are separat-able in
different files - but I wouldn't want to have to merge multiple files
to produce a final mde each time changes are made.

Can you define "very large"? How big is the front end in terms of bytes?

If it is getting large because of the natural growth of DB's over time
(even
the front end, due to internal temporary storage used by Access), you
could
look at automating a compact/repair when the front end closes.

If there are just too many functions in the front end, you might take a
look
reorganizing your app so that functions are categorized and split onto
menus
-> submenus -> action forms.

sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.

my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and end-
user performance.

There are a couple of "sections" to the application which could easily
(from a logical perspective) be "modularized" (ie: there's an import/
export functionality).

So - what I would like to do, is move this functionality into its own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.

Is there some way to "dll-ize" a chunk of this app?

if not - what IS the best way to handle an application that is
becoming very large?

TIA - Bob

 

[Brian]

Following up on your response & Arvin's, I checked one of my apps that has
265 queries in it. I don't know if that is a lot or not compared to yours,
but for what it is worth, here is my opinion:

1. I would probably separate the front end apps by department or general
user function, rather than using different apps to call each other. That
could involve a lot of keeping track of different apps and how they relate
and extra code for relatively little return. Instead, I would tend to tie
each app just to the common back end.
2. Developing and following a strict naming convention within the app can
help separate objects (queries, forms, reports, etc) for the developer. For
example, all accounting-related forms can be prefaced with "Acct", while all
operations-related forms could be prefaced with "Ops"; the same is true of
queries. I try to use logical names for queries; although the user will never
see these names, it keeps the developer (me) on track. A query named
"LookupContractBalance" is probably preferable to Query25 or ContBalLkup, at
least as far as helping me find my own stuff.
3. Setting up a multi-level menu (either on a form or via a menu bar) can
help separate functions into logical groups. A button or menu bar item for
accounting functions that brings up a submenu of AR, AP, and Payroll, that in
turn each have a set of related forms is an example.
4. If you post a simple question about Access performance, you will
undoubtedly get some the links to some of the MVP's sites that deal with
Access efficiences - such things as keeping a connection to the back end open
continuously by making your main menu/form a bound form - rather than opening
& closing DB connections every time you open a form. This can have a huge
impact on performance. There are many other factors that will likely allow
you to stay within a Jet connection an not feel forced to a SQL back end
right away.
5. One of the challenges is taking existing naming conventions and
standardizing them. If you have not already done so, obtain a copy of Find
and Replace by Rick Fisher (www.rickworld.com). This tool that allows one to
search (and replace) strings anywehre in an app (form/query names, code
module content, query SQL statements, etc)revolutionized the way I develop
and saved me countless hours trying to find and replace strings and rename
objects in my apps.

hi Brian;

TX for your reply. very large in terms of the quantity of queries,
and forms.
The actual size of the app is around 5M.

I suppose my biggest concern over this, is oriented toward managing
the app on the dev. side. I think performance issues could probably be
mostly dealt with by migrating the back-end into a sql-server.

There are a vast number of queries (and forms) to try to keep track
of. Which does what for which function and so on.
It would be nice to have those functions which are separat-able in
different files - but I wouldn't want to have to merge multiple files
to produce a final mde each time changes are made.

Can you define "very large"? How big is the front end in terms of bytes?

If it is getting large because of the natural growth of DB's over time (even
the front end, due to internal temporary storage used by Access), you could
look at automating a compact/repair when the front end closes.

If there are just too many functions in the front end, you might take a look
reorganizing your app so that functions are categorized and split onto menus
-> submenus -> action forms.

sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.

my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and end-
user performance.

There are a couple of "sections" to the application which could easily
(from a logical perspective) be "modularized" (ie: there's an import/
export functionality).

So - what I would like to do, is move this functionality into its own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.

Is there some way to "dll-ize" a chunk of this app?

if not - what IS the best way to handle an application that is
becoming very large?

TIA - Bob

 

[Bob]

hi Arvin!
TYVM for your reply...

So now I'm curious about your apps...
did you distribute them to the users as mde's ?
assuming you did - how did you link to your common code database?
is the common code DB an mde or mdb? assuming it must be an mdb...
cause not sure how else you'd be able to get @ the code ... ??

TIA!

Actually, 5 MB is tiny, almost infinitesimal, for a database. Even 50 MB is
considered small.

I built a large database for a homebuilder consisting of 12 front-end
applications, linked to a single 100+ MB Access/JET database on the server.
A total of 50 users had from 1 to 3 front-end mdb's open at any given time.
I divided by apps by function. The Permitting dept, maintained the
Subdivisions and Lots, and the housing Starts. There was a Bidding
department, a Service Dept., a Purchasing Dept. a Sales Dept. Closing Dept,
etc., etc.

Code common to everyone, or at least multiple applications, was stored in a
Code database which was referenced by the apps that needed it. A total of
about 60 MB in the 12 database apps kept each to between 3 MB and 8 or 9 MB,
and as such each was secure from the others, and loaded and ran much faster
due to their smaller sizes.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

hi Brian;

TX for your reply. very large in terms of the quantity of queries,
and forms.
The actual size of the app is around 5M.

I suppose my biggest concern over this, is oriented toward managing
the app on the dev. side. I think performance issues could probably be
mostly dealt with by migrating the back-end into a sql-server.

There are a vast number of queries (and forms) to try to keep track
of. Which does what for which function and so on.
It would be nice to have those functions which are separat-able in
different files - but I wouldn't want to have to merge multiple files
to produce a final mde each time changes are made.

Can you define "very large"? How big is the front end in terms of bytes?
If it is getting large because of the natural growth of DB's over time
(even
the front end, due to internal temporary storage used by Access), you
could
look at automating a compact/repair when the front end closes.
If there are just too many functions in the front end, you might take a
look
reorganizing your app so that functions are categorized and split onto
menus
-> submenus -> action forms.
:
sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.
my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and end-
user performance.
There are a couple of "sections" to the application which could easily
(from a logical perspective) be "modularized" (ie: there's an import/
export functionality).
So - what I would like to do, is move this functionality into its own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.
Is there some way to "dll-ize" a chunk of this app?
if not - what IS the best way to handle an application that is
becoming very large?
TIA - Bob

 

[Bob]

hi Brian!

TX again for your input...
I suppose I could separate the functions into separate applications..
but where Arvin is apparently having success running multiple
instances of access - that scares the crap out of me ;-) granted that
not too many of my users "cross-over" multiple function categogies,
but there are a few...

I do, in fact, already follow a naming convention which does help to
separate things... but there's just soooo much - I'd really like to
isolate into separate files . I also have a menu that works well in
the user experience.

As I said earlier, the perforance piece isn't my primary concern at
the moment; but I'm starting to think down that path. I'll bet I'm not
maintaining an open connection to the back end... I'll look into that.

The interesting part of this, is that I originally tried doing
something similiar to what Arvin described (a common database).
Everything worked wonderfully until I tried to run it as an mde! Then
it all fell apart, and I abandoned that idea. Maybe it's worth re-
visiting, if there's a work-around...

TX again!

Following up on your response & Arvin's, I checked one of my apps that has
265 queries in it. I don't know if that is a lot or not compared to yours,
but for what it is worth, here is my opinion:

1. I would probably separate the front end apps by department or general
user function, rather than using different apps to call each other. That
could involve a lot of keeping track of different apps and how they relate
and extra code for relatively little return. Instead, I would tend to tie
each app just to the common back end.
2. Developing and following a strict naming convention within the app can
help separate objects (queries, forms, reports, etc) for the developer. For
example, all accounting-related forms can be prefaced with "Acct", while all
operations-related forms could be prefaced with "Ops"; the same is true of
queries. I try to use logical names for queries; although the user will never
see these names, it keeps the developer (me) on track. A query named
"LookupContractBalance" is probably preferable to Query25 or ContBalLkup, at
least as far as helping me find my own stuff.
3. Setting up a multi-level menu (either on a form or via a menu bar) can
help separate functions into logical groups. A button or menu bar item for
accounting functions that brings up a submenu of AR, AP, and Payroll, that in
turn each have a set of related forms is an example.
4. If you post a simple question about Access performance, you will
undoubtedly get some the links to some of the MVP's sites that deal with
Access efficiences - such things as keeping a connection to the back end open
continuously by making your main menu/form a bound form - rather than opening
& closing DB connections every time you open a form. This can have a huge
impact on performance. There are many other factors that will likely allow
you to stay within a Jet connection an not feel forced to a SQL back end
right away.
5. One of the challenges is taking existing naming conventions and
standardizing them. If you have not already done so, obtain a copy of Find
and Replace by Rick Fisher (www.rickworld.com). This tool that allows one to
search (and replace) strings anywehre in an app (form/query names, code
module content, query SQL statements, etc)revolutionized the way I develop
and saved me countless hours trying to find and replace strings and rename
objects in my apps.

hi Brian;

TX for your reply. very large in terms of the quantity of queries,
and forms.
The actual size of the app is around 5M.

I suppose my biggest concern over this, is oriented toward managing
the app on the dev. side. I think performance issues could probably be
mostly dealt with by migrating the back-end into a sql-server.

There are a vast number of queries (and forms) to try to keep track
of. Which does what for which function and so on.
It would be nice to have those functions which are separat-able in
different files - but I wouldn't want to have to merge multiple files
to produce a final mde each time changes are made.

Can you define "very large"? How big is the front end in terms of bytes?
If it is getting large because of the natural growth of DB's over time (even
the front end, due to internal temporary storage used by Access), you could
look at automating a compact/repair when the front end closes.
If there are just too many functions in the front end, you might take a look
reorganizing your app so that functions are categorized and split onto menus
-> submenus -> action forms.
:
sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.
my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and end-
user performance.
There are a couple of "sections" to the application which could easily
(from a logical perspective) be "modularized" (ie: there's an import/
export functionality).
So - what I would like to do, is move this functionality into its own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.
Is there some way to "dll-ize" a chunk of this app?
if not - what IS the best way to handle an application that is
becoming very large?
TIA - Bob

 

[Guest]

You can 'reference' an mdb or mde by adding it to your
references.

Code in a library mdb/mde must use the word CodeDB
instead of CurrentDB.

DoCmd and DLookup actions in a library mdb/mde refer
to the CurrentDB, not the CodeDB.

Queries Forms and Reports in the library mdb/mde refer to
the CodeDB

So the library mdb/mde must have links to the BE for queries
forms and reports to work,

And the CurrentDB must have links to the BE for DoCmd
to work. I just don't use DoCmd and DLookup in the library.

There is no direct way to open Forms and Reports in the
library, unless you use Access 2000 or earlier. Instead,
you need to have a function in the library to open Forms
and Reports. You can call functions in the library. You can
call functions from forms or macros or menus.

(david)

 

[Arvin Meyer [MVP]]

I rarely use MDEs because most of my applications belong to the companies I
work for, and they are free to use them as they wish, except for reselling
them as an app. The last MDE that I built was years ago, and I had changed
computers several times and no longer had the MDB to work on it. That cured
me. I also prefer not to use runtimes either. I prefer my clients to have
full versions. Unless they don't mind paying me to custom build all the
goodies that don't work in a runtimes, it is usually cheaper to use the full
version. Cheaper for support too. The common code database is custom built
for each major application where there are multiple applications. It doesn't
matter whether it is an MDB or MDE, since you link to it by setting a
reference from the app. In a code window: Tools >>> References then change
the filetype to MDB/MDE and navigate to the code database.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

hi Arvin!
TYVM for your reply...

So now I'm curious about your apps...
did you distribute them to the users as mde's ?
assuming you did - how did you link to your common code database?
is the common code DB an mde or mdb? assuming it must be an mdb...
cause not sure how else you'd be able to get @ the code ... ??

TIA!

Actually, 5 MB is tiny, almost infinitesimal, for a database. Even 50 MB
is
considered small.

I built a large database for a homebuilder consisting of 12 front-end
applications, linked to a single 100+ MB Access/JET database on the
server.
A total of 50 users had from 1 to 3 front-end mdb's open at any given
time.
I divided by apps by function. The Permitting dept, maintained the
Subdivisions and Lots, and the housing Starts. There was a Bidding
department, a Service Dept., a Purchasing Dept. a Sales Dept. Closing
Dept,
etc., etc.

Code common to everyone, or at least multiple applications, was stored in
a
Code database which was referenced by the apps that needed it. A total of
about 60 MB in the 12 database apps kept each to between 3 MB and 8 or 9
MB,
and as such each was secure from the others, and loaded and ran much
faster
due to their smaller sizes.
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

hi Brian;

TX for your reply. very large in terms of the quantity of queries,
and forms.
The actual size of the app is around 5M.

I suppose my biggest concern over this, is oriented toward managing
the app on the dev. side. I think performance issues could probably be
mostly dealt with by migrating the back-end into a sql-server.

There are a vast number of queries (and forms) to try to keep track
of. Which does what for which function and so on.
It would be nice to have those functions which are separat-able in
different files - but I wouldn't want to have to merge multiple files
to produce a final mde each time changes are made.

Can you define "very large"? How big is the front end in terms of
bytes?

If it is getting large because of the natural growth of DB's over time
(even
the front end, due to internal temporary storage used by Access), you
could
look at automating a compact/repair when the front end closes.

If there are just too many functions in the front end, you might take
a
look
reorganizing your app so that functions are categorized and split onto
menus
-> submenus -> action forms.

:
sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.

my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and
end-
user performance.

There are a couple of "sections" to the application which could
easily
(from a logical perspective) be "modularized" (ie: there's an
import/
export functionality).

So - what I would like to do, is move this functionality into its
own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.

Is there some way to "dll-ize" a chunk of this app?

if not - what IS the best way to handle an application that is
becoming very large?

TIA - Bob

 

[Arvin Meyer [MVP]]

hi Brian!

TX again for your input...
I suppose I could separate the functions into separate applications..
but where Arvin is apparently having success running multiple
instances of access - that scares the crap out of me ;-) granted that
not too many of my users "cross-over" multiple function categogies,
but there are a few...

We averaged about 65 open connections for the 50 users, but we occasionally
hit a high of 75 connections. At least 10 users had 2 open databases, and 5
others typically had 3 open. To scare you even more, 8 of the connections
were from an asp front-end running over the corporate intranet, and 6 more
were terminal services connections, some of which often had multiple
databases open at the same time.

The last corruption was in 2003 when the database corrupted 5 weeks in a row
due to a faulty WiFi card which I told them to not use in the first place.
Never use a WiFi card on anything but a Terminal Server connection. The only
other corruption we ever had was the year before due to a bug in
autonumbers. No data was ever lost.

A well designed data structure, quality hardware, and well trained and
conscientious users are all that's required for Access stability.

 

[Bob]

David-

TX very much for your reply - I clearly should re-examine the use of
common library's... apparently whatever I did the first time around
must have been wrong.
I'll read up on 'codedb' - that was something I know I didn't use the
first time...
I am using access 2k... so that may make getting to forms a little
more direct...

You can 'reference' an mdb or mde by adding it to your
references.

Code in a library mdb/mde must use the word CodeDB
instead of CurrentDB.

DoCmd and DLookup actions in a library mdb/mde refer
to the CurrentDB, not the CodeDB.

Queries Forms and Reports in the library mdb/mde refer to
the CodeDB

So the library mdb/mde must have links to the BE for queries
forms and reports to work,

And the CurrentDB must have links to the BE for DoCmd
to work. I just don't use DoCmd and DLookup in the library.

There is no direct way to open Forms and Reports in the
library, unless you use Access 2000 or earlier. Instead,
you need to have a function in the library to open Forms
and Reports. You can call functions in the library. You can
call functions from forms or macros or menus.

(david)

sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.

my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and end-
user performance.

There are a couple of "sections" to the application which could easily
(from a logical perspective) be "modularized" (ie: there's an import/
export functionality).

So - what I would like to do, is move this functionality into its own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.

Is there some way to "dll-ize" a chunk of this app?

if not - what IS the best way to handle an application that is
becoming very large?

TIA - Bob

 

[Bob]

Wow, Arvin - you are a trusting soul !

I use mde's for a number of reasons - but the top 2 are version
control / data control.

on the runtime issue - I tend to agree with you. I tried doing that
once way back when; and it was nothing but trouble. So I have no
problem with full versions of access running; just don't want the
users poking around in the application or database. I'm a bit anal
about the data integrity in this particular application.

I do, however, allow 2 users to have read only access to an mdb linked
to all the tables, so they can play around with ad-hoc queries &
reports.

I'll definitely look again at linking to a code-base / external
function-base as ref'd thru the references option.
TX again!
Bob

I rarely use MDEs because most of my applications belong to the companies I
work for, and they are free to use them as they wish, except for reselling
them as an app. The last MDE that I built was years ago, and I had changed
computers several times and no longer had the MDB to work on it. That cured
me. I also prefer not to use runtimes either. I prefer my clients to have
full versions. Unless they don't mind paying me to custom build all the
goodies that don't work in a runtimes, it is usually cheaper to use the full
version. Cheaper for support too. The common code database is custom built
for each major application where there are multiple applications. It doesn't
matter whether it is an MDB or MDE, since you link to it by setting a
reference from the app. In a code window: Tools >>> References then change
the filetype to MDB/MDE and navigate to the code database.
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

hi Arvin!
TYVM for your reply...

So now I'm curious about your apps...
did you distribute them to the users as mde's ?
assuming you did - how did you link to your common code database?
is the common code DB an mde or mdb? assuming it must be an mdb...
cause not sure how else you'd be able to get @ the code ... ??

TIA!

Actually, 5 MB is tiny, almost infinitesimal, for a database. Even 50 MB
is
considered small.
I built a large database for a homebuilder consisting of 12 front-end
applications, linked to a single 100+ MB Access/JET database on the
server.
A total of 50 users had from 1 to 3 front-end mdb's open at any given
time.
I divided by apps by function. The Permitting dept, maintained the
Subdivisions and Lots, and the housing Starts. There was a Bidding
department, a Service Dept., a Purchasing Dept. a Sales Dept. Closing
Dept,
etc., etc.
Code common to everyone, or at least multiple applications, was stored in
a
Code database which was referenced by the apps that needed it. A total of
about 60 MB in the 12 database apps kept each to between 3 MB and 8 or 9
MB,
and as such each was secure from the others, and loaded and ran much
faster
due to their smaller sizes.
--
Arvin Meyer, MCP,
MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmv...

hi Brian;
TX for your reply. very large in terms of the quantity of queries,
and forms.
The actual size of the app is around 5M.
I suppose my biggest concern over this, is oriented toward managing
the app on the dev. side. I think performance issues could probably be
mostly dealt with by migrating the back-end into a sql-server.
There are a vast number of queries (and forms) to try to keep track
of. Which does what for which function and so on.
It would be nice to have those functions which are separat-able in
different files - but I wouldn't want to have to merge multiple files
to produce a final mde each time changes are made.
Can you define "very large"? How big is the front end in terms of
bytes?
If it is getting large because of the natural growth of DB's over time
(even
the front end, due to internal temporary storage used by Access), you
could
look at automating a compact/repair when the front end closes.
If there are just too many functions in the front end, you might take
a
look
reorganizing your app so that functions are categorized and split onto
menus
-> submenus -> action forms.
:
sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.
my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and
end-
user performance.
There are a couple of "sections" to the application which could
easily
(from a logical perspective) be "modularized" (ie: there's an
import/
export functionality).
So - what I would like to do, is move this functionality into its
own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.
Is there some way to "dll-ize" a chunk of this app?
if not - what IS the best way to handle an application that is
becoming very large?
TIA - Bob

 

[Bob]

We averaged about 65 open connections for the 50 users, but we occasionally
hit a high of 75 connections. At least 10 users had 2 open databases, and 5
others typically had 3 open. To scare you even more, 8 of the connections
were from an asp front-end running over the corporate intranet, and 6 more
were terminal services connections, some of which often had multiple
databases open at the same time.

The last corruption was in 2003 when the database corrupted 5 weeks in a row
due to a faulty WiFi card which I told them to not use in the first place.
Never use a WiFi card on anything but a Terminal Server connection. The only
other corruption we ever had was the year before due to a bug in
autonumbers. No data was ever lost.

A well designed data structure, quality hardware, and well trained and
conscientious users are all that's required for Access stability.

had to reply to this one separately
I'm blown away by the way you're using an mdb! I never would have
thought it stable enough!
The wi-fi thing is good to know, although I'm not sure I would have
ever trusted a db connection over that in the first place

I especially like your statement about having well trained, and
conscientious users.
In my experience - there is no such animal! LOL
No matter how well you fool-proof something - someone will always find
a better fool ! ;-)

TX for making me smile today!
Bob

 

[Arvin Meyer [MVP]]

Wow, Arvin - you are a trusting soul !

I use mde's for a number of reasons - but the top 2 are version
control / data control.

on the runtime issue - I tend to agree with you. I tried doing that
once way back when; and it was nothing but trouble. So I have no
problem with full versions of access running; just don't want the
users poking around in the application or database. I'm a bit anal
about the data integrity in this particular application.

If your code is rock solid with good error handling, you can use an MDE for
a referenced code database. That will give you the integrity that your want.
I prefer User Level Security when I need to keep users out of the tables.

 

[Arvin Meyer [MVP]]

had to reply to this one separately
I'm blown away by the way you're using an mdb! I never would have
thought it stable enough!
The wi-fi thing is good to know, although I'm not sure I would have
ever trusted a db connection over that in the first place

I didn't and don't except through a Terminal Server over a VPN. It was a
young IT guy who thought WiFi was cool and answered a minor problem of
passing a laptop around a board room.

I especially like your statement about having well trained, and
conscientious users.
In my experience - there is no such animal! LOL
No matter how well you fool-proof something - someone will always find
a better fool ! ;-)

Not true at all. The company that I worked for had a turn-over rate of less
than 2%. The average employment period was over 15 years. The company was 40
years old and had employees who had been there for 37 years.

We are in Florida and when the triple hurricanes hit us in 2004, the company
suffered less than $15,000 total damage despite having 500 homes under
construction. The supers stayed, and employees who could help, made sure
that everything was secure before going home. Even the subcontractors stayed
and help tie down everything. Some companies are enlightened. They hire good
people, pay them well, and enjoy a dependable, loyal, and intelligent
workforce.

 

[Bob]

If your code is rock solid with good error handling, you can use an MDE for
a referenced code database. That will give you the integrity that your want.
I prefer User Level Security when I need to keep users out of the tables.

That's really good to know; I remember when I originally tried using a
ref'd code DB; I failed, and thought that the mde might have been part
of the reason... So I'm really glad to KNOW that will work - I'll give
it another try ... TX again

 

[Bob]

Not true at all. The company that I worked for had a turn-over rate of less
than 2%. The average employment period was over 15 years. The company was 40
years old and had employees who had been there for 37 years.

We are in Florida and when the triple hurricanes hit us in 2004, the company
suffered less than $15,000 total damage despite having 500 homes under
construction. The supers stayed, and employees who could help, made sure
that everything was secure before going home. Even the subcontractors stayed
and help tie down everything. Some companies are enlightened. They hire good
people, pay them well, and enjoy a dependable, loyal, and intelligent
workforce.

Arvin-

I'm in awe of that company! I never would have believed such a thing
possible ;-)
What did you say their name was ? ;o)
seriously - sounds like that's a homebuilder, and that loyalty for a
homebuilder is IMPRESSIVE.

unfortunately, in the industry this application is for, there are no
employee's even near the caliber of those you talk about.

I guess I can just day dream about having the people in that company
as users... lol

Bob

 

[Guest]

I mixed that up badly!

DoCmd actions actually take place in the LibraryDB --
it only some export commands and dlookups that refer
to the CurrentDB!

So code in your library does not normally open forms
or reports in your CurrentDB -- If you want that, perhaps
you would use a Add-In instead of a library.

(david)

David-

TX very much for your reply - I clearly should re-examine the use of
common library's... apparently whatever I did the first time around
must have been wrong.
I'll read up on 'codedb' - that was something I know I didn't use the
first time...
I am using access 2k... so that may make getting to forms a little
more direct...

You can 'reference' an mdb or mde by adding it to your
references.

Code in a library mdb/mde must use the word CodeDB
instead of CurrentDB.

DoCmd and DLookup actions in a library mdb/mde refer
to the CurrentDB, not the CodeDB.

Queries Forms and Reports in the library mdb/mde refer to
the CodeDB

So the library mdb/mde must have links to the BE for queries
forms and reports to work,

And the CurrentDB must have links to the BE for DoCmd
to work. I just don't use DoCmd and DLookup in the library.

There is no direct way to open Forms and Reports in the
library, unless you use Access 2000 or earlier. Instead,
you need to have a function in the library to open Forms
and Reports. You can call functions in the library. You can
call functions from forms or macros or menus.

(david)

sorry for the cross-postings - not sure where this best fits.
running access 2k, in front/back split configuration. focusing on
front end application.

my application has become quite large, and I would like to split it
into smaller pieces, both for ease of development/maintenance, and end-
user performance.

There are a couple of "sections" to the application which could easily
(from a logical perspective) be "modularized" (ie: there's an import/
export functionality).

So - what I would like to do, is move this functionality into its own
separate mdb/mde file (or mdl [dll-analog].
However - I would like the end user experience to be that when they
click the "import/export" button - it would appear simply as another
window in their already open application window.

Is there some way to "dll-ize" a chunk of this app?

if not - what IS the best way to handle an application that is
becoming very large?

TIA - Bob

 

[Arvin Meyer [MVP]]

That's really good to know; I remember when I originally tried using a
ref'd code DB; I failed, and thought that the mde might have been part
of the reason... So I'm really glad to KNOW that will work - I'll give
it another try ... TX again

Here's an example. Three of the 12 databases required the contractors form
which had tons of code in it for validation. Instead of trying to maintain
that form in 3 places, I put it in a CodeDB which I referenced on all 3
databases. A module maintained code to open the form:

Public Sub OpenContractors()
On Error GoTo Err_Handler
DoCmd.OpenForm "frmContractors"
Err_Handler:
ErrorSub
End Sub

You'll find the error handling code at my website:

http://www.datastrat.com/Code/Error.txt

Now in each of the 3 databases, a button had the following code:

Public Sub cmdContractors_Click()
' Code from CodeDB.mde
OpenContractors
End Sub

Simple, easy.

 

[Arvin Meyer [MVP]]

I guess I can just day dream about having the people in that company
as users... lol

Further history: The owners sold the company to a large national homebuilder
with Harvard MBAs on the board of directors. Within 6 months the leadership
was replaced. Within a year, only 2 of the original employees were left.
Many of the better subcontractors who had a 40 year history with the
company, completed their contracts and refused new ones. The company, who's
stock was trading at $96 a share, is now trading at $15. The houses being
built now are junk compared to what was being built.

Several of the employees started their own companies and because of the
reputations of the original company and them as employees are doing OK, even
in this terrible market. As a matter of fact, they are having a party soon
to which I was invited yesterday.