Distributing Central App To Remote Sites

[Heather]

I have created a database application consisting of a front end and backend
file. Data entry and administration of the application are done at a central
location. The next requirement is to be able to distribute the appllication each
month to five remote sites by sending the sites a CD. The sites will only use
the application to lookup data in the database and print reports. The sites will
make no data entry. I need comments and suggestions to the following:

1. What is the easiest way to combine the front end and back end into one file?
I want one file so that if changes are made to the front end, the changes get
transmitted to the remote sites automatically at the monthly update. Also, one
file simplifies the remote sites' installation of the update.

2. Is there some simple way to make the entire front end Read Only or to
somehow block out the data entry forms?
Does anyone have any other thoughts on how to distribute the central application
monthly?

Thanks for all input,

Heather

[TC]

When the database file is copied from the CD to the user's PC, it will
automatically be marked "read only", so it will run in read-only mode. (The
user could, of course, go to the file properties, & un-tick the read only
checkbox.) Would that be enough protection? If not, you could use Access
user-level security to achieve what you want. However, that has a
significant learning curve, & many traps for the unwary!

As for recombing the FE and BE, I guess you would simply delete the links
from the FE, then import the tables from the BE, no? Or am I missing
something?

Also be aware that if you have any code "behind the scenes" which uses the
OpenRecordSet statement against your tables, and those statements do >not<
provide an explicit dbOpenXXX parameter, you can get problems when changing
from linked tables to local ones.

HTH,
TC
"Heather" <ha******@cseducationalsystems.org> wrote in message
news:cN*******************@newsread1.news.atl.eart hlink.net...

I have created a database application consisting of a front end and backend file. Data entry and administration of the application are done at a central location. The next requirement is to be able to distribute the appllication each month to five remote sites by sending the sites a CD. The sites will only use the application to lookup data in the database and print reports. The sites will make no data entry. I need comments and suggestions to the following:

1. What is the easiest way to combine the front end and back end into one file? I want one file so that if changes are made to the front end, the changes get transmitted to the remote sites automatically at the monthly update. Also, one file simplifies the remote sites' installation of the update.

2. Is there some simple way to make the entire front end Read Only or to
somehow block out the data entry forms?
Does anyone have any other thoughts on how to distribute the central application monthly?

Thanks for all input,

Heather

[Larry Linson]

Will the remote sites have Access installed, or will you need to distribute
the runtime support from the Developer Edition (or, for Access 2003, from
the "Visual Studio Tools for Microsoft Office 2003 System" -- yech, what a
name!)? If the latter, you are going to package with the Packaging and
Distribution Wizard so it will be an "automated" install and, thus, there
will be no problem to install both the front-end and the back-end.

If you establish a convention where they install into a folder with
identical names, they may not even have to relink the linked tables -- which
is no big deal with the Linked Table Manager, and even if you use the
runtime where they do not have LTM, the code is simple and available to do
it.

You may want to use Access security to ensure that they don't do any data
entry, either on purpose or inadvertently, though. An alternative is to
provide them with a "developed application" so they only have access to
Forms, not to the Database Window or Datasheet View... and have your forms
provide for reading, searching, and running reports but nothing else.

And, even if they have retail Access, you could include a batch file to copy
the front-end and back-end.

But, if you'd really feel more comfortable with a single DB, make a copy of
the front-end and the back-end. Delete the linked TableDefs from the
front-end and import the tables from the back-end. Viola! You have your
monolithic database in a trivially easy operation.

Larry Linson
Microsoft Access MVP
"Heather" <ha******@cseducationalsystems.org> wrote in message
news:cN*******************@newsread1.news.atl.eart hlink.net...

I have created a database application consisting of a front end and backend file. Data entry and administration of the application are done at a central location. The next requirement is to be able to distribute the appllication each month to five remote sites by sending the sites a CD. The sites will only use the application to lookup data in the database and print reports. The sites will make no data entry. I need comments and suggestions to the following:

1. What is the easiest way to combine the front end and back end into one file? I want one file so that if changes are made to the front end, the changes get transmitted to the remote sites automatically at the monthly update. Also, one file simplifies the remote sites' installation of the update.

2. Is there some simple way to make the entire front end Read Only or to
somehow block out the data entry forms?
Does anyone have any other thoughts on how to distribute the central application monthly?

Thanks for all input,

Heather

[Heather]

Larry,

Thank you for your response to my questions here!

Do you have any sample code for the following:
<<Delete the linked TableDefs from the front-end and import the tables from the
back-end>>

Also, in TC's response he states:
<<Also be aware that if you have any code "behind the scenes" which uses the
OpenRecordSet statement against your tables, and those statements do >not<
provide an explicit dbOpenXXX parameter, you can get problems when changing
from linked tables to local ones.>>
Can you comment to this?

Thanks!!

Heather

"Larry Linson" <bo*****@localhost.not> wrote in message
news:BP******************@nwrddc03.gnilink.net...

Will the remote sites have Access installed, or will you need to distribute
the runtime support from the Developer Edition (or, for Access 2003, from
the "Visual Studio Tools for Microsoft Office 2003 System" -- yech, what a
name!)? If the latter, you are going to package with the Packaging and
Distribution Wizard so it will be an "automated" install and, thus, there
will be no problem to install both the front-end and the back-end.

If you establish a convention where they install into a folder with
identical names, they may not even have to relink the linked tables -- which
is no big deal with the Linked Table Manager, and even if you use the
runtime where they do not have LTM, the code is simple and available to do
it.

You may want to use Access security to ensure that they don't do any data
entry, either on purpose or inadvertently, though. An alternative is to
provide them with a "developed application" so they only have access to
Forms, not to the Database Window or Datasheet View... and have your forms
provide for reading, searching, and running reports but nothing else.

And, even if they have retail Access, you could include a batch file to copy
the front-end and back-end.

But, if you'd really feel more comfortable with a single DB, make a copy of
the front-end and the back-end. Delete the linked TableDefs from the
front-end and import the tables from the back-end. Viola! You have your
monolithic database in a trivially easy operation.

Larry Linson
Microsoft Access MVP
"Heather" <ha******@cseducationalsystems.org> wrote in message
news:cN*******************@newsread1.news.atl.eart hlink.net...

I have created a database application consisting of a front end and

backend

file. Data entry and administration of the application are done at a

central

location. The next requirement is to be able to distribute the

appllication each

month to five remote sites by sending the sites a CD. The sites will only

use

the application to lookup data in the database and print reports. The

sites will

make no data entry. I need comments and suggestions to the following:

1. What is the easiest way to combine the front end and back end into one

file?

I want one file so that if changes are made to the front end, the changes

get

transmitted to the remote sites automatically at the monthly update. Also,

one

file simplifies the remote sites' installation of the update.

2. Is there some simple way to make the entire front end Read Only or to
somehow block out the data entry forms?
Does anyone have any other thoughts on how to distribute the central

application

monthly?

Thanks for all input,

Heather

[Tom Mitchell]

I have the same type issue with one of my projects. I didn't want to
mess with security after the fact, so the workaround I came up with is
as follows:

1. Created a procedure the administrator can access via menus to
create a monolithic DB from the FE/BE version. New DB filename
includes date stamp. Procedure handles all the file and table
manipulations needed. Administrator simply needs to click a menu
item.
2. Edit capablities on all forms look to see if a particular table is
linked or not. If linked - then edit is enabled, if not, then not.

Its been in use for a few months, but am not sure the end users are
really giving it a good test, so not willing to claim it is a total
solution just yet.

I'll post the code if you are interested. Its kind of long and I
don't want to gum up the works if nobody is interested.

Tom

"Heather" <ha******@cseducationalsystems.org> wrote in message news:<cN*******************@newsread1.news.atl.ear thlink.net>...

I have created a database application consisting of a front end and backend
file. Data entry and administration of the application are done at a central
location. The next requirement is to be able to distribute the appllication each
month to five remote sites by sending the sites a CD. The sites will only use
the application to lookup data in the database and print reports. The sites will
make no data entry. I need comments and suggestions to the following:

1. What is the easiest way to combine the front end and back end into one file?
I want one file so that if changes are made to the front end, the changes get
transmitted to the remote sites automatically at the monthly update. Also, one
file simplifies the remote sites' installation of the update.

2. Is there some simple way to make the entire front end Read Only or to
somehow block out the data entry forms?
Does anyone have any other thoughts on how to distribute the central application
monthly?

Thanks for all input,

Heather

[Larry Linson]

"Heather" wrote

Do you have any sample code for
the following: <<Delete the linked
TableDefs from the front-end and
import the tables from the
back-end>>
No, because I've never distributed in this way. I had in mind doing it
manually; it is only a few steps. To do it programmatically, you'd work on
the TableDefs Collection, using its DELETE method to remove them and either
CopyObject or TransferDatabase to import the tables from the back-end.
Also, in TC's response he states:
<<Also be aware that if you have any
code "behind the scenes" which uses the
OpenRecordSet statement against your
tables, and those statements do >not<
provide an explicit dbOpenXXX parameter,
you can get problems when changing
from linked tables to local ones.>>

No, I really don't. You'd better ask him.

I can only envision a problem going "the other way" -- that is, moving from
local to linked tables -- and having code that opens a local table with in
dbOpenTable mode, which you cannot do on a linked table. But, the default is
dbOpenDynaset which you can do on either local or linked tables.

Larry Linson
Microsoft Access MVP

[Larry Linson]

Tom, sounds like a good "resource". Can you post it to a website? You can
get a free one at http://www.tripod.com and a number of other places. That's
where I post my sample databases -- http://accdevel.tripod.com.

Larry Linson
Microsoft Access MVP

"Tom Mitchell" <rt*****@swbell.net> wrote in message
news:70**************************@posting.google.c om...

I have the same type issue with one of my projects. I didn't want to
mess with security after the fact, so the workaround I came up with is
as follows:

1. Created a procedure the administrator can access via menus to
create a monolithic DB from the FE/BE version. New DB filename
includes date stamp. Procedure handles all the file and table
manipulations needed. Administrator simply needs to click a menu
item.
2. Edit capablities on all forms look to see if a particular table is
linked or not. If linked - then edit is enabled, if not, then not.

Its been in use for a few months, but am not sure the end users are
really giving it a good test, so not willing to claim it is a total
solution just yet.

I'll post the code if you are interested. Its kind of long and I
don't want to gum up the works if nobody is interested.

Tom

"Heather" <ha******@cseducationalsystems.org> wrote in message

news:<cN*******************@newsread1.news.atl.ear thlink.net>...

I have created a database application consisting of a front end and backend file. Data entry and administration of the application are done at a central location. The next requirement is to be able to distribute the appllication each month to five remote sites by sending the sites a CD. The sites will only use the application to lookup data in the database and print reports. The sites will make no data entry. I need comments and suggestions to the following:

1. What is the easiest way to combine the front end and back end into one file? I want one file so that if changes are made to the front end, the changes get transmitted to the remote sites automatically at the monthly update. Also, one file simplifies the remote sites' installation of the update.

2. Is there some simple way to make the entire front end Read Only or to somehow block out the data entry forms?
Does anyone have any other thoughts on how to distribute the central application monthly?

Thanks for all input,

Heather

[TC]

"Larry Linson" <bo*****@localhost.not> wrote in message
news:V_*****************@nwrddc02.gnilink.net...

"Heather" wrote

> Do you have any sample code for
> the following: <<Delete the linked
> TableDefs from the front-end and
> import the tables from the
> back-end>>
No, because I've never distributed in this way. I had in mind doing it
manually; it is only a few steps. To do it programmatically, you'd work on
the TableDefs Collection, using its DELETE method to remove them and

either CopyObject or TransferDatabase to import the tables from the back-end.

> Also, in TC's response he states:
> <<Also be aware that if you have any
> code "behind the scenes" which uses the
> OpenRecordSet statement against your
> tables, and those statements do >not<
> provide an explicit dbOpenXXX parameter,
> you can get problems when changing
> from linked tables to local ones.>>
No, I really don't. You'd better ask him.

I can only envision a problem going "the other way" -- that is, moving

from local to linked tables -- and having code that opens a local table with in
dbOpenTable mode, which you cannot do on a linked table. But, the default is dbOpenDynaset which you can do on either local or linked tables.

I always forget the details of this, & don't have Access here to check. But
it's something like this. an openrecordset() on a linked table defaults to
dbopendynaset - so you cxan use the Find* methods. But if you make the table
local - perhaps to deal with multiuser concurrency issues - the
openrecordset defaults to dbopentable, for which the Find* methods are not
allowed. Conversely, I guess there might be dbopentable methods that do not
work with dynasets. So my warning is, that if you change tables from linked
to local (or perhaps vice-versa), this cause problems if your openrecordset
statements do not explicitly state the dbopen option required.

HTH,
TC

[Tom Mitchell]

Larry:

Would be more than happy to post, however, I don't really have the
time to figure out how to make tripod work right now (I made a quick
and dirty site, but can't figure out how to include file downloading).
I'd be happy to send you a copy if you would like to post on your
site, or I'll go back to tripod when I have a little more time and try
again.

Tom
"Larry Linson" <bo*****@localhost.not> wrote in message news:<H0******************@nwrddc02.gnilink.net>.. .

Tom, sounds like a good "resource". Can you post it to a website? You can
get a free one at http://www.tripod.com and a number of other places. That's
where I post my sample databases -- http://accdevel.tripod.com.

Larry Linson
Microsoft Access MVP

[Tom Mitchell]

Larry:

OK – I couldn't stand the fact that I couldn't figure out Tripod, so I
spent the time needed to figure out how to post a file. Its an ugly
page, but its the best I can do right now. See
http://rthomasmitchell.tripod.com. Download the only file there.

Some notes:
· Its in Access2000.
· Unzip the file to a temporary directory somewhere. Relink the
FE/BE, then click on the button on the startup form. It should create
a monolith DB.
· I tried to scrub out most everything that wasn't pertinent to the
example. I probably missed some stuff though.
· If you can solve the Access97 problem, let me know. I tried, but it
wasn't a central issue, so I didn't spend a lot of time on it.
Yours truly,

Tom (the somewhat obsessive compulsive access wannabe programmer)
"Larry Linson" <bo*****@localhost.not> wrote in message news:<H0******************@nwrddc02.gnilink.net>.. .

Tom, sounds like a good "resource". Can you post it to a website? You can
get a free one at http://www.tripod.com and a number of other places. That's
where I post my sample databases -- http://accdevel.tripod.com.

Larry Linson
Microsoft Access MVP

"Tom Mitchell" <rt*****@swbell.net> wrote in message
news:70**************************@posting.google.c om...

I have the same type issue with one of my projects. I didn't want to
mess with security after the fact, so the workaround I came up with is
as follows:

1. Created a procedure the administrator can access via menus to
create a monolithic DB from the FE/BE version. New DB filename
includes date stamp. Procedure handles all the file and table
manipulations needed. Administrator simply needs to click a menu
item.
2. Edit capablities on all forms look to see if a particular table is
linked or not. If linked - then edit is enabled, if not, then not.

Its been in use for a few months, but am not sure the end users are
really giving it a good test, so not willing to claim it is a total
solution just yet.

I'll post the code if you are interested. Its kind of long and I
don't want to gum up the works if nobody is interested.

Tom

"Heather" <ha******@cseducationalsystems.org> wrote in message

news:<cN*******************@newsread1.news.atl.ear thlink.net>...

I have created a database application consisting of a front end and backend file. Data entry and administration of the application are done at a central location. The next requirement is to be able to distribute the appllication each month to five remote sites by sending the sites a CD. The sites will only use the application to lookup data in the database and print reports. The sites will make no data entry. I need comments and suggestions to the following:

1. What is the easiest way to combine the front end and back end into one file? I want one file so that if changes are made to the front end, the changes get transmitted to the remote sites automatically at the monthly update. Also, one file simplifies the remote sites' installation of the update.

2. Is there some simple way to make the entire front end Read Only or to somehow block out the data entry forms?
Does anyone have any other thoughts on how to distribute the central application monthly?

Thanks for all input,

Heather