Packaging and distribution...


A

Adrian Brown

Im sure this has been asked 1000 times before but i couldnt find a
definitive list. I have built an access 2007 database that is split into
frontend/backend components. I need to distribute this to my end user and
also from time to time update the frontend. Some frontend updates may
require additional tables and/or alteration to tables already in the
backend. I can happily package the database using the developer extensions,
but is there any good guidance for how to best install and setu the links.
Currently i would copy the backend to an area on the server and then install
the frontend on each client. I then manually set the /X option to relink
which calls a macro to ask me to relink the tables which i do by hand then
rever the /x back to startup which clls my startup macro.

This all seems very manual - i would ideally like to be able to place a .exe
or .msi on my website and allow the end users to download as they wish.
Also it would be nice to be able to place updates on there which will update
from old to new version. I presume to do this i would require some VBA code
in the new version to check which version the backend is at and manually
update / create tables to bring it inline with the new version. But whats
the best way to package up a new frontend. Is there a way to automatically
install the frontend/backend in different locations and set the links? I
guess i could use some registry entries??

The other problem i have is the trusts warning that pops up - even though i
have digitally signed the app? Is it possible to stop this?

Many Thanks
 
Ad

Advertisements

A

Albert D. Kallal

There are several notable limitations of the package wizard system that will
stop you from achieving the goals that your outlining in this process.

The first and foremost limitation of the package wizard is that it has no
ability to overwrite existing files that you installed. Because of this
limitation, it means that you simply have to delete, or uninstall the
previous front end that you've setup. Because of this significant
limitation, I'm going to suggest that you don't bother with the package
wizard to distribute an ***updates*** to your new front ends.

In addition to the above limitations, you commented that it be nice to have
some kind of downloadable executable that you run for the install on the end
user's machine. If you look at the contents and the resulting files that
produced by the package wizard, you'll see it's nothing remotely close to
that of a single file that you can download, or copy.

Once again because of this limitation, the package wizard don't allow you to
setup a single file to download. Obviously for downloading from the website
this becomes very problematic, and therefore once again I suggest because
this limitation you avoid using the package wizard to update the new front
end.

Furthermore if you think about this, an update to your front and is really
only a simple mde (or accDE) file copy from your web site or package source
to overwrite the old front end (and, you are using a accDE file..right???)
So, updates are just a simple file copy here. The package wizard that has
all of the above limitations and can't even produce one single file that you
could download and unpack and then install. So, the PW is an MSI install
with all kinds of extra baggage and folders and files.

Hence, for the last 7+ years, and long before even the access 2007 package
wizard I have done the following:

I first build a runtime package and setup. This is ONLY the runtime install.
You might only install a "readme" text file with this. No matter how you
slice this, the goal here is to get the runtime system installed on the end
computer. Once this install is done it should stay in place for years, and
it is only a one time process. This is the only instance where I actually
use something produced by the package wizard. Once we've done this process
and I have the runtime installed, we'll never bother with this whole thing
again for the next 5 or more years from the clients point of view. For the
IT person, you might build and send them a copy of this runtime disk that
you made. ( one thing the package does right is to create a standard auto
run CD).

Once you have the runtime installed on that computer, the next goal is to
setup something that allows you to install you new front end, and of course
the many frequent updates you going to have to this front end. For a long
time I used to use a paid copy of winzip (you can find that here
www.winzip.com). It is only a few dollars For registered copy and is well
worth it.

However I then discovered the free open source installer who called inno.
The advantage of inno over WinZip is that you can package a bunch of files
into a file for download, and it also has the ability to set up shortcuts
etc. on your desktop, something that WinZip it did not have.

You'll find the inno installer here:
http://www.jrsoftware.org/isinfo.php

Note the above link also has a good number of support and very active
newsgroups that can help you with your install setups also. I highly
recommend this installer, and it is free. and, for questions, check
out the newsgroups there also.
i would ideally like to be able to place a .exe
or .msi on my website and allow the end users to download as they wish.

Because the inno installer produces a single .exe file that unpacks, then I
actually placed a copy of this file on my website. You then place ONE line
of code in my application behind a button that does the application follow
hyperlink to that .exe. I've done that for years, and it means you can have
a one button update right inside your software menu. ( and you can do the
whole process with about one line of code in access, which is again really
terrific).

Because I didn't like the *one* prompt about your about to download an
executable
file from the web, I went one step further and grabbed a copy of the FTP
library at www.mvps.org/access. Now when the user presses a button I use the
FTP library to download this copy of the new front and, and then simply
execute the inno install package.

The whole process has become extremely slick, and easy from an end user's
point if view. Take a look at the following series of screen shots as to how
my users now update their MS access software:
http://www.kallal.ca/ridestutorialp/upgrade.html

The inno script that runs after the download looks like:

[Setup]

SourceDir=c:\Documents and Settings\All Users\Application Data\RidesL

AppName=Rides Reservation System
AppVerName= Rides 2.0
DefaultDirName={commonappdata}\RidesL
DefaultGroupName=Rides
Compression=lzma
SolidCompression=yes
DirExistsWarning=no
DisableDirPage=yes
DisableProgramGroupPage=yes
Uninstallable=no

[Files]
Source: "RidesXP.mde"; DestDir: "{app}"

*-----------------------------------

As you can see this script to update and copy overwrite the old front end is
very simple.
frontend updates may
require additional tables and/or alteration to tables already in the
backend.

Now this is the where things get a little more tricky, and I have for years
updated a considerable amount of clients software to people that I've never
met or seen. What this means is that when you develop your updates, you
gonna have to use program code in your front and to check the version of the
backend, and then execute the appropriate commands and statements to add
those additional tables and fields that you make.

In a nutshell this means that as you develop the next new update great
version of the software, every time you add a new field or table
modification to the back end, you actually don't go into the tables and
modify them, You write little bits of code in your front end in a routine
called upgrade.

The code to add a new field to a table in the back end looks like:

Set rst = CurrentDb.OpenRecordset("Locations")

On Error resume next

strtemp = rst!Capacity.Name

if err.num <> 0 then

' field not in table

strToDB = strBackEnd ' path to back end

Set db = OpenDatabase(strToDB) ' open back end directly

Set nT = db.TableDefs("Locations")

nT.Fields.Append nT.CreateField("Capacity", dbLong)

nT.Fields.Refresh

db.Close

Set db = Nothing

End if

What this means is that you never go directly into the back end tables and
make modifications, Thus, every time you need to add a field, you simply add
the above code to your "upgrade" routine. I have some of these update
routines like the above that literally have hundreds and hundreds of lines
of code. If I was a little bit more smarter in the whole approach to this
process I would've made a generalized routine in which I say here id the
table name, and field name to add. (as you can see the code is quite similar
each time you add a new field, so it just begs for for someone to write a
nice generalized routin. I was in a hurry so I just kept cutting pasting the
code over and over.

Over time you'll actually learn to like this development process, because
you don't have to exit your application and open up the back and to add a
new field. You can use a similar process and similar code for also the
creation of new tables also.

You also mentioned some re-linking in your code. In all of my applications I
actually write out the location of the backend filed to local text file in
the same dir as the front end. This means that when I update the new version
of my front end, on launch time it checks the current linked location of the
back end tables, and if it doesn't match this little text file link
location, then it starts a re-link process. I'm sure you as a developer had
to write years and years ago some type of re-linking routine. In fact it's
usually often a good idea to offer menu option in which the user can browse
and select the back end.

You can use the code here to re-link.
http://www.mvps.org/access/tables/tbl0009.htm

the code to pop open the file dialog browse can also be found here
http://www.mvps.org/access/api/api0001.htm

The above two routines do most of the heavy lifting and dirty work for you.
I think just about every access developer on the planet has taken the above
to a teens and cobble together some kind of re-link system.
 
A

Adrian Brown

Many Thanks for your reply, I guess is should have realised that it wasnt up
to the job and gone with installation procedures i use for .exe files etc.
I dont suppose you know hwo to stop that annoying Trust warning i get?

Many Thanks

Adrian

Albert D. Kallal said:
There are several notable limitations of the package wizard system that
will
stop you from achieving the goals that your outlining in this process.

The first and foremost limitation of the package wizard is that it has no
ability to overwrite existing files that you installed. Because of this
limitation, it means that you simply have to delete, or uninstall the
previous front end that you've setup. Because of this significant
limitation, I'm going to suggest that you don't bother with the package
wizard to distribute an ***updates*** to your new front ends.

In addition to the above limitations, you commented that it be nice to
have
some kind of downloadable executable that you run for the install on the
end
user's machine. If you look at the contents and the resulting files that
produced by the package wizard, you'll see it's nothing remotely close to
that of a single file that you can download, or copy.

Once again because of this limitation, the package wizard don't allow you
to
setup a single file to download. Obviously for downloading from the
website
this becomes very problematic, and therefore once again I suggest because
this limitation you avoid using the package wizard to update the new front
end.

Furthermore if you think about this, an update to your front and is really
only a simple mde (or accDE) file copy from your web site or package
source
to overwrite the old front end (and, you are using a accDE file..right???)
So, updates are just a simple file copy here. The package wizard that has
all of the above limitations and can't even produce one single file that
you
could download and unpack and then install. So, the PW is an MSI install
with all kinds of extra baggage and folders and files.

Hence, for the last 7+ years, and long before even the access 2007 package
wizard I have done the following:

I first build a runtime package and setup. This is ONLY the runtime
install.
You might only install a "readme" text file with this. No matter how you
slice this, the goal here is to get the runtime system installed on the
end
computer. Once this install is done it should stay in place for years, and
it is only a one time process. This is the only instance where I actually
use something produced by the package wizard. Once we've done this process
and I have the runtime installed, we'll never bother with this whole thing
again for the next 5 or more years from the clients point of view. For the
IT person, you might build and send them a copy of this runtime disk that
you made. ( one thing the package does right is to create a standard auto
run CD).

Once you have the runtime installed on that computer, the next goal is to
setup something that allows you to install you new front end, and of
course
the many frequent updates you going to have to this front end. For a long
time I used to use a paid copy of winzip (you can find that here
www.winzip.com). It is only a few dollars For registered copy and is well
worth it.

However I then discovered the free open source installer who called inno.
The advantage of inno over WinZip is that you can package a bunch of files
into a file for download, and it also has the ability to set up shortcuts
etc. on your desktop, something that WinZip it did not have.

You'll find the inno installer here:
http://www.jrsoftware.org/isinfo.php

Note the above link also has a good number of support and very active
newsgroups that can help you with your install setups also. I highly
recommend this installer, and it is free. and, for questions, check
out the newsgroups there also.
i would ideally like to be able to place a .exe
or .msi on my website and allow the end users to download as they wish.

Because the inno installer produces a single .exe file that unpacks, then
I
actually placed a copy of this file on my website. You then place ONE line
of code in my application behind a button that does the application follow
hyperlink to that .exe. I've done that for years, and it means you can
have
a one button update right inside your software menu. ( and you can do the
whole process with about one line of code in access, which is again really
terrific).

Because I didn't like the *one* prompt about your about to download an
executable
file from the web, I went one step further and grabbed a copy of the FTP
library at www.mvps.org/access. Now when the user presses a button I use
the
FTP library to download this copy of the new front and, and then simply
execute the inno install package.

The whole process has become extremely slick, and easy from an end user's
point if view. Take a look at the following series of screen shots as to
how
my users now update their MS access software:
http://www.kallal.ca/ridestutorialp/upgrade.html

The inno script that runs after the download looks like:

[Setup]

SourceDir=c:\Documents and Settings\All Users\Application Data\RidesL

AppName=Rides Reservation System
AppVerName= Rides 2.0
DefaultDirName={commonappdata}\RidesL
DefaultGroupName=Rides
Compression=lzma
SolidCompression=yes
DirExistsWarning=no
DisableDirPage=yes
DisableProgramGroupPage=yes
Uninstallable=no

[Files]
Source: "RidesXP.mde"; DestDir: "{app}"

*-----------------------------------

As you can see this script to update and copy overwrite the old front end
is
very simple.
frontend updates may
require additional tables and/or alteration to tables already in the
backend.

Now this is the where things get a little more tricky, and I have for
years
updated a considerable amount of clients software to people that I've
never
met or seen. What this means is that when you develop your updates, you
gonna have to use program code in your front and to check the version of
the
backend, and then execute the appropriate commands and statements to add
those additional tables and fields that you make.

In a nutshell this means that as you develop the next new update great
version of the software, every time you add a new field or table
modification to the back end, you actually don't go into the tables and
modify them, You write little bits of code in your front end in a routine
called upgrade.

The code to add a new field to a table in the back end looks like:

Set rst = CurrentDb.OpenRecordset("Locations")

On Error resume next

strtemp = rst!Capacity.Name

if err.num <> 0 then

' field not in table

strToDB = strBackEnd ' path to back end

Set db = OpenDatabase(strToDB) ' open back end directly

Set nT = db.TableDefs("Locations")

nT.Fields.Append nT.CreateField("Capacity", dbLong)

nT.Fields.Refresh

db.Close

Set db = Nothing

End if

What this means is that you never go directly into the back end tables and
make modifications, Thus, every time you need to add a field, you simply
add
the above code to your "upgrade" routine. I have some of these update
routines like the above that literally have hundreds and hundreds of lines
of code. If I was a little bit more smarter in the whole approach to this
process I would've made a generalized routine in which I say here id the
table name, and field name to add. (as you can see the code is quite
similar
each time you add a new field, so it just begs for for someone to write a
nice generalized routin. I was in a hurry so I just kept cutting pasting
the
code over and over.

Over time you'll actually learn to like this development process, because
you don't have to exit your application and open up the back and to add a
new field. You can use a similar process and similar code for also the
creation of new tables also.

You also mentioned some re-linking in your code. In all of my applications
I
actually write out the location of the backend filed to local text file in
the same dir as the front end. This means that when I update the new
version
of my front end, on launch time it checks the current linked location of
the
back end tables, and if it doesn't match this little text file link
location, then it starts a re-link process. I'm sure you as a developer
had
to write years and years ago some type of re-linking routine. In fact it's
usually often a good idea to offer menu option in which the user can
browse
and select the back end.

You can use the code here to re-link.
http://www.mvps.org/access/tables/tbl0009.htm

the code to pop open the file dialog browse can also be found here
http://www.mvps.org/access/api/api0001.htm

The above two routines do most of the heavy lifting and dirty work for
you.
I think just about every access developer on the planet has taken the
above
to a teens and cobble together some kind of re-link system.
 
A

Adrian Brown

Not sure why the reply didnt come through :) so here it is again :D

Many Thanks for your reply, I guess is should have realised that it wasnt up
to the job and gone with installation procedures i use for .exe files etc.
I dont suppose you know hwo to stop that annoying Trust warning i get?

Many Thanks

Adrian


Albert D. Kallal said:
There are several notable limitations of the package wizard system that
will
stop you from achieving the goals that your outlining in this process.

The first and foremost limitation of the package wizard is that it has no
ability to overwrite existing files that you installed. Because of this
limitation, it means that you simply have to delete, or uninstall the
previous front end that you've setup. Because of this significant
limitation, I'm going to suggest that you don't bother with the package
wizard to distribute an ***updates*** to your new front ends.

In addition to the above limitations, you commented that it be nice to
have
some kind of downloadable executable that you run for the install on the
end
user's machine. If you look at the contents and the resulting files that
produced by the package wizard, you'll see it's nothing remotely close to
that of a single file that you can download, or copy.

Once again because of this limitation, the package wizard don't allow you
to
setup a single file to download. Obviously for downloading from the
website
this becomes very problematic, and therefore once again I suggest because
this limitation you avoid using the package wizard to update the new front
end.

Furthermore if you think about this, an update to your front and is really
only a simple mde (or accDE) file copy from your web site or package
source
to overwrite the old front end (and, you are using a accDE file..right???)
So, updates are just a simple file copy here. The package wizard that has
all of the above limitations and can't even produce one single file that
you
could download and unpack and then install. So, the PW is an MSI install
with all kinds of extra baggage and folders and files.

Hence, for the last 7+ years, and long before even the access 2007 package
wizard I have done the following:

I first build a runtime package and setup. This is ONLY the runtime
install.
You might only install a "readme" text file with this. No matter how you
slice this, the goal here is to get the runtime system installed on the
end
computer. Once this install is done it should stay in place for years, and
it is only a one time process. This is the only instance where I actually
use something produced by the package wizard. Once we've done this process
and I have the runtime installed, we'll never bother with this whole thing
again for the next 5 or more years from the clients point of view. For the
IT person, you might build and send them a copy of this runtime disk that
you made. ( one thing the package does right is to create a standard auto
run CD).

Once you have the runtime installed on that computer, the next goal is to
setup something that allows you to install you new front end, and of
course
the many frequent updates you going to have to this front end. For a long
time I used to use a paid copy of winzip (you can find that here
www.winzip.com). It is only a few dollars For registered copy and is well
worth it.

However I then discovered the free open source installer who called inno.
The advantage of inno over WinZip is that you can package a bunch of files
into a file for download, and it also has the ability to set up shortcuts
etc. on your desktop, something that WinZip it did not have.

You'll find the inno installer here:
http://www.jrsoftware.org/isinfo.php

Note the above link also has a good number of support and very active
newsgroups that can help you with your install setups also. I highly
recommend this installer, and it is free. and, for questions, check
out the newsgroups there also.
i would ideally like to be able to place a .exe
or .msi on my website and allow the end users to download as they wish.

Because the inno installer produces a single .exe file that unpacks, then
I
actually placed a copy of this file on my website. You then place ONE line
of code in my application behind a button that does the application follow
hyperlink to that .exe. I've done that for years, and it means you can
have
a one button update right inside your software menu. ( and you can do the
whole process with about one line of code in access, which is again really
terrific).

Because I didn't like the *one* prompt about your about to download an
executable
file from the web, I went one step further and grabbed a copy of the FTP
library at www.mvps.org/access. Now when the user presses a button I use
the
FTP library to download this copy of the new front and, and then simply
execute the inno install package.

The whole process has become extremely slick, and easy from an end user's
point if view. Take a look at the following series of screen shots as to
how
my users now update their MS access software:
http://www.kallal.ca/ridestutorialp/upgrade.html

The inno script that runs after the download looks like:

[Setup]

SourceDir=c:\Documents and Settings\All Users\Application Data\RidesL

AppName=Rides Reservation System
AppVerName= Rides 2.0
DefaultDirName={commonappdata}\RidesL
DefaultGroupName=Rides
Compression=lzma
SolidCompression=yes
DirExistsWarning=no
DisableDirPage=yes
DisableProgramGroupPage=yes
Uninstallable=no

[Files]
Source: "RidesXP.mde"; DestDir: "{app}"

*-----------------------------------

As you can see this script to update and copy overwrite the old front end
is
very simple.
frontend updates may
require additional tables and/or alteration to tables already in the
backend.

Now this is the where things get a little more tricky, and I have for
years
updated a considerable amount of clients software to people that I've
never
met or seen. What this means is that when you develop your updates, you
gonna have to use program code in your front and to check the version of
the
backend, and then execute the appropriate commands and statements to add
those additional tables and fields that you make.

In a nutshell this means that as you develop the next new update great
version of the software, every time you add a new field or table
modification to the back end, you actually don't go into the tables and
modify them, You write little bits of code in your front end in a routine
called upgrade.

The code to add a new field to a table in the back end looks like:

Set rst = CurrentDb.OpenRecordset("Locations")

On Error resume next

strtemp = rst!Capacity.Name

if err.num <> 0 then

' field not in table

strToDB = strBackEnd ' path to back end

Set db = OpenDatabase(strToDB) ' open back end directly

Set nT = db.TableDefs("Locations")

nT.Fields.Append nT.CreateField("Capacity", dbLong)

nT.Fields.Refresh

db.Close

Set db = Nothing

End if

What this means is that you never go directly into the back end tables and
make modifications, Thus, every time you need to add a field, you simply
add
the above code to your "upgrade" routine. I have some of these update
routines like the above that literally have hundreds and hundreds of lines
of code. If I was a little bit more smarter in the whole approach to this
process I would've made a generalized routine in which I say here id the
table name, and field name to add. (as you can see the code is quite
similar
each time you add a new field, so it just begs for for someone to write a
nice generalized routin. I was in a hurry so I just kept cutting pasting
the
code over and over.

Over time you'll actually learn to like this development process, because
you don't have to exit your application and open up the back and to add a
new field. You can use a similar process and similar code for also the
creation of new tables also.

You also mentioned some re-linking in your code. In all of my applications
I
actually write out the location of the backend filed to local text file in
the same dir as the front end. This means that when I update the new
version
of my front end, on launch time it checks the current linked location of
the
back end tables, and if it doesn't match this little text file link
location, then it starts a re-link process. I'm sure you as a developer
had
to write years and years ago some type of re-linking routine. In fact it's
usually often a good idea to offer menu option in which the user can
browse
and select the back end.

You can use the code here to re-link.
http://www.mvps.org/access/tables/tbl0009.htm

the code to pop open the file dialog browse can also be found here
http://www.mvps.org/access/api/api0001.htm

The above two routines do most of the heavy lifting and dirty work for
you.
I think just about every access developer on the planet has taken the
above
to a teens and cobble together some kind of re-link system.
 
Ad

Advertisements

A

Albert D. Kallal

Adrian Brown said:
Not sure why the reply didnt come through :) so here it is again :D

Many Thanks for your reply, I guess is should have realised that it wasnt
up
to the job and gone with installation procedures i use for .exe files etc.
I dont suppose you know hwo to stop that annoying Trust warning i get?

Many Thanks

Adrian

I don't have an answer, as I not deployed using 2007 as of yet.
Because this post has quite moved down in the order of things, I would
suggest that you consider re-posting..and asking as a separate question as
to handle the trust issue....

in 2003, we did have the macro security issue, but a few register entries in
my setup scripts eliminated those (I don't think they apply to 2007)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top