Simple Deployment Question

[Jim M]

I've been distributing a fairly mature, very specific MS Access
application to end users in small offices of colleges for several years
now. This is a part-time venture and low volume operation- this is
somewhat of a hobby for me. Many of my end users are computer phobic
and get little support from their IT departments. It is a split
database so the datafile gets put on the file server and the 3
different front ends get put on each local machine.

MY QUESTION IS: HOW CAN I GET THE FRONT END TO LINK TO THE DATAFILE
WHEN IT IS COPIED TO THE SHARED DRIVE - is there a default way to
designate a path at the time the file is copied?

My normal installation routine is as follows:

1) End users unzip the application and a few supporting files (e.g.
Word Document templates used for merges, etc)
2) Put the datafile on the shared drive.
3) Open one copy of the front end and link it to the back end - I have
code that checks for the backend end at startup, then uses
"DoCmd.RunCommand acCmdLinkTables" to open a file requestor for the
back end. The client uses the file requestor, enters the password for
the backend, and hits 'select all'.
4) After the backend is linked, the end user presses a "Defaults"
button which modifies forms and defaults in the front end (e.g. they
can put their school name on certain reports and screens, select a
default state for forms where addresses are entered, and customize
labels of certain fields that appear in forms and reports.)
5) Once the front end is customized they compact and repair it, then
copy it to a specific directory on the shared drive.
6) Each end user then double clicks on a simple script file ont he
shared drive to create directories on the local machine, copy the front
ends, and copy shortcuts for the front ends onto the desktop. This, of
course, includes the end user customizations from step 4.

Sounds simple, right? I am amazed at how impossible these things are
for many people. I wrote the script files because I spent 2 hours on
the phone one day talking an end user through putting a shortcut for
the database on their desktop. This person really did not know how to
use a mouse or how to copy a file; despite being the most comfortable
person with a computer in their office. I finally realized (after about
an hour) that the only way she knew to find files was from inside
Microsoft Word. There seems to be a whole lot of people who learn
procedures as a series of steps without ever being taught what a file,
directory, or application is... (Apologies for the rant.)

I realize I can purchase commercial installation wizard programs, but
the low volume and profit does not warrant the expense. So:
A) Are there any low-cost/freeware alternatives?
B) Can I beef up my script file to capture the path to the shared drive
in a way that can be passed to the front end, e.g. by writing it to a
text file in the same directory as the front end? Right now I use
mkdir and xcopy to do everything after the customization takes place.
C) Can I write an Access program that will copy and link the files,
e.g. using the Microsoft Scripting Runtime Library to get the path to
the shared drive, copy all the files there, then open them up for
customization?
D) If I can pass the back end location to the front end, can I link
automatically?
D) Any other thought would be appreciated.

Thanks in advance.

[Albert D. Kallal]

Good question!!

>
My normal installation routine is as follows:

1) End users unzip the application and a few supporting files (e.g.
Word Document templates used for merges, etc)

I used to use a registered version of WinZip. However, even better, and free
is inno install.

Inno installer is not only free, but it includes compression like winzip,
but
you can also use it to create shortcuts on both the desktop, and even a
entry in the start->programs-menu.....
So, in place of the winzip, if you use the above, then users DO NOT HAVE to
know where to place the front end files. So, typically, you would use
something
like

c:\program files\mycoolAppName\

With inno, this gives me a very nice windows like install. Here is a few
screen shots of what a inno looks like:
(the first part is just my regular ms-access application, but once they
click
on the download, it is a inno install).

http://www.kallal.ca/ridestutorialp/upgrade.html

2) Put the datafile on the shared drive.

Yes, the above is still a step that I talk my clients through. Often, I have
their "support" guy create a folder on the server. I also of course have
setup remote support. Here is some screen shots of what this looks like:

http://www.kallal.ca/ridestutorialp/remote.html

3) Open one copy of the front end and link it to the back end - I have
code that checks for the backend end at startup, then uses
"DoCmd.RunCommand acCmdLinkTables" to open a file requestor for the
back end. The client uses the file requestor, enters the password for
the backend, and hits 'select all'.

No, don't do the above. Roll your own code. The code to re-link tables is
here:

http://www.mvps.org/access/tables/tbl0009.htm

and, the code to open the file browse dialog is here:

http://www.mvps.org/access/api/api0001.htm

So, in a short time, you can add the ability to pop open the file browse
dialog, and also re-link.

Note that when you DO re-link, then write out a text file to same directory
as the mde is running. Then, next time, your start-up code can look for this
small text file..and when you send them updates...your code re-linked to
THEIR back end location!!

4) After the backend is linked, the end user presses a "Defaults"
button which modifies forms and defaults in the front end (e.g. they
can put their school name on certain reports and screens, select a
default state for forms where addresses are entered, and customize
labels of certain fields that appear in forms and reports.)

No, don't EVERY actually put in the company name into the forms. You REALLY
want to distribute a mde file. Have a table in the back end that is one
record, and allows you to enter all that stuff like company name etc. You
then build a function, or use expression on all those forms/reports to
return the value of the company name that appears on the main forms, and
even reports.

5) Once the front end is customized they compact and repair it, then
copy it to a specific directory on the shared drive.

If you had put the customizations in the back end table, then only one user
has to do this, and then you can actually send them bug fixes and updates to
their existing system, and they never have to re-enter that stuff.

6) Each end user then double clicks on a simple script file ont he
shared drive to create directories on the local machine, copy the front
ends, and copy shortcuts for the front ends onto the desktop. This, of
course, includes the end user customizations from step 4.

As mentioned, build a inno install. And, since you put the customizing stuff
in the back end table, and since we have re-link code at start-up..then it
is
easy a pie to send NEW updates to your users.

This person really did not know how to
use a mouse or how to copy a file; despite being the most comfortable
person with a computer in their office.

You need to setup some type of remote support system. Either pay for
something
like co-pilot, or use the free win-vnc
(I use the free win-vnc single click in the above examples).

>
I realize I can purchase commercial installation wizard programs, but
the low volume and profit does not warrant the expense. So:
A) Are there any low-cost/freeware alternatives?

Inno is free, and is likely BETTER then most commercial installers for this
kind of stuff. I highly recommend it, and so does everyone else who uses it.

you can find it here:

http://www.jrsoftware.org/isinfo.php

The above is not good..but REALLY good!!

B) Can I beef up my script file to capture the path to the shared drive
in a way that can be passed to the front end, e.g. by writing it to a
text file in the same directory as the front end?

Yes, the trick/key is to write out that small text file when the user links.
This does mean that EACH user must do this
the first time. However, if your program pops up the file browse dialog
the first time it runs, and the user navigates to the back end file this one
time, that is not too bad..and certainly LESS trouble then what you do now.

You should as a rule also be deploying a mde, as is more reliable,and users
can't accidentally change it....

I been very busy of late, but have been meaning to write an article on the
above issue....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Jim M]

Albert,

So cool! This will save me scads of time. Thanks so much. I will
download the InnoSetup. I have read some of your other posts and found
them helpful. I hope I'm not imposing on you too much if I ask a few
other questions.

1) Where can I get information on the free win-vnc?

2) I use a table to handle many of the customizations already, but
there are a few forms that are commonly used that I can be customized.
The customizations I use include:
a) Whether or not to run code when the form is closed.
b) Opting to use either a text box or combo box for a particular
field. The use of the combo box implies the use of code when the form
is opened. I do this by having two copies of certain forms and using
code to copy one of them during the customization
c) Changing a field's caption on a form and report.
d) Changing the field's default value (I have five or six places
with default state/province boxes)

I currently do these customizations from a form which opens each form I
want to change in design view and modifies them. Would using DLookup's
instead slow things down appreciably? Is there another way to do this?

Thanks! Can't wait to see that article when you get time for it...

Jim M

[Albert D. Kallal]

1) Where can I get information on the free win-vnc?

Actually, there are many versions of vnc, but I use ultra vnc. You can find
it here:

http://www.uvnc.com/index.html

Check out in the above "add ons" what is called "single click" vnc.
This is how I support users with that cool custom menu, and users don't even
have to type in a ip address, or anything. (of course this means you need to
run what is called the "repeater", and that allows you to support people
even when they are behind routers + firewalls. There is a lot of faq's, and
even some forums for the above, so any questions you have about vnc can be
found, or answered from the above link.

I currently do these customizations from a form which opens each form I
want to change in design view and modifies them. Would using DLookup's
instead slow things down appreciably? Is there another way to do this?

In my start-up code load up a global reocrdset of all of my defaults. As
mentioned, this is a single table, with one record. For each new type of
default, I add a new field to this table.

Then, when you need a default, you just go in code:

strDefaultPostalCodeType = gblrstDefauts!PostalCodeFormat

So, no, I don't need a dlookup. Each new type of default becomes a new field
in this ONE RECORD table.

Some people actually use the same form to edit the defaults,a nd then in
start-up code make the form invisible. So, in place of a global reocrdset,
you could go

strDefaultAreacode = forms!frmDefaults!Areacode

Either approach works well. I prefer the global reocrset....

You can actually see some of the defaults that users can edit in one of my
applications here:

http://www.kallal.ca/ridestutorialp/ChangeGST.htm

Most of the values are simply one field of a one record table....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Jim M]

Albert,

I'll check out the vnc. I like your use of a global recordset. I
already use a hidden global form to store variables (like the active
staff member) that I pass around. Using a recordset seems cleaner. I
guess it is still not advisable to use global variables for anything? I
remember reading years ago that they behave poorly...

One last question: How do you update your customers' backends? I have
many old customers with backend databases vastly out of sync with my
new back end and I have no record of which customer has what. I wrote a
little Access database that opens a file requestor which the end user
points at their datafile and then extracts empty copies of the tables.
This allows me to determine what needs to be upgraded, though I have
yet to figure a way to handle this remotely. I have been exploring a
couple of options: Two of them write VB Code that you send to your end
user. I could not get Michael Blake's Upgrader to work except on
really simple databases, though it did seem promising. The code would
not run. Likewise, Compare'EM Lite, from Mike Noel gave me the same
trouble. SSW's Data Renovator refused to do the fields that had spaces
in them (I know! I know! It's bad - but I named them in 1994 when I
didn't know any better and now I have hundreds of objects using the bad
names...). I was able to use AccessMint, from cdsoft, but I needed to
have the data file in my hands to use it. I am considering using an
ActiveX object called dbUpgrader which needs to be installed on the end
users machine. You set up a startup form to check for a "model" data
file and it automatically upgrades when it finds a model. (Not sure how
easy that is to do: Can InnoSetup handle installing ACtiveX components?

Thanks again fro sharing your knowledge and wisdom.

Jim M

[Albert D. Kallal]

I'll check out the vnc. I like your use of a global recordset. I

already use a hidden global form to store variables (like the active
staff member) that I pass around. Using a recordset seems cleaner. I
guess it is still not advisable to use global variables for anything? I
remember reading years ago that they behave poorly...

Well, do remember that any un-handled error will re-set ALL of your
variables, and that includes global.

However, if you always distribute a mde, then errors will NOT reset
variables. This is one of many reasons why I
always use a mde for my users. So, if you MUST (or plan) to
distribute a mdb for production code (and, you should NOT
be doing this), then you really have to use a form, or perhaps
a self healing class object that tests of the variables have been
all lost due to an error,and then re-load them. I just don't
bother, and use a mde...as variables don't loose their
values when a error in code occurs...

One last question: How do you update your customers' backbends? I have
many old customers with backend databases vastly out of sync with my
new back end and I have no record of which customer has what.

The solution to the above is that any addition you make to the back end
table, it must be done via a code routine. So, as you develop the next
great update for your customers, if you need a new field, or new table
then you write code to add this new table, or field.

My front end code (after the linking part is done) then runs a routine
called Upgrade1. So, every time you need a new field, or table in the back
end, you actually write the code to do this. The code will check for a
field,
(or table) and add it if it is missing. (the code works this way, since the
end user
might have several back ends..and link to anyone of them. Further, those
several backbends might be a old backup, or whatever. Thus, your design
assumes
that the back end can be at any revision level, but your upgrade code will
ALWAYS update the mdb to the latest version if a table/field etc. is
missing...

>I have
yet to figure a way to handle this remotely. I have been exploring a
couple of options: Two of them write VB Code that you send to your end
user.

The code you write to upgrade the back end is not really that hard.

However, for the most part, there is 3 types of operations

1) increase the length of a field. This happens a few times during
development
and after you deploy. My upgrade code for that is

' check size of Anotes in tblGroupRemind

Set db = CurrentDb
Set dbTable = db.TableDefs("tblGroupRemind")
Dim intSize As Integer
intSize = dbTable("ANotes").Size

Set dbTable = Nothing

If intSize = 50 Then

Dim nField As DAO.Field

Set db = OpenDatabase(strBackEnd)

db.Execute "ALTER TABLE tblGroupRemind ALTER COLUMN Anotes text(255)",
dbFailOnError

End If
As you can see, it is not hard, but is DOES take code!!! Some of my upgrade
routines for software that is 6 years old is 100's of lines of code of the
above type stuff....

Adding a field is not hard either....
strToDB = strBackEnd

Set db = OpenDatabase(strToDB)

Set nT = db.TableDefs("Locations")
nT.Fields.Append nT.CreateField("Capacity", dbLong)
nT.Fields.Refresh
db.Close
Set db = Nothing
Adding a whole table is easy also!!. All I do is place a "copy" of the table
in the front end. this table is small, and will NEVER be used in the front
end, but remains there to be *COPIED* to the back end table. I use a table
copy as it is MUCH less code then trying to use code to create a table, and
then one field at a time. IF the table have 5, or even 25 fields, the code
to "copy" the table from the front end to the back end is rather small....

So, new tables are created in the front end. When I send out the new front
end, the code check for the missing table, and then COPIES it from the front
end to the back end!!. Very easy, and not much code...

AddDefaultTable:

strFromDB = CurrentProject.FullName
strToDB = strBackEnd

DoCmd.TransferDatabase acExport, "Microsoft Access", strToDB, acTable,
"tblDefaultsC", "tblDefaults", False

Return

All of my new default tables in the front end have the table name + "C"
appended to the end (this is so the table name does not interfere with the
actually linked table name!!!

So, the above code is only 3, or 4 lines of code, and yet it copies (well,
creates) a whole new table in the back end....

So, the above sample code shows increasing a field length, adding new
fields, and adding new tables. These are the most common updates to the back
end.

>Can InnoSetup handle installing ACtiveX components?

Yes, but you as rule don't need, nor want to bother with 3rd party
actieXcotnorls if you can avoid them...
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Jim M]

Hey, Thanks! I feel like I've enough to keep me busy for a few weeks...

Regards!
Jim