>> currentDb access.application

[Sky]

When the library db must stay as an mdb and the db referencing it must
become
an mde.

Access does not permit a front-end compiled mde to reference an uncompiled
library .mdb. You must first compile the library and change the reference to
the compiled version, before you are permitted to compile the front-end
referencing db.

You can automated compiling both the library and the front end as I
described in a prior message in this thread.

- Steve

 

[David W. Fenton]

[quoting me:]

In my case, there is some bloating after the user installs a new
version of the application that has new structural changes in the
backend tables. The install process automatically rebuilds the
user back-end tables to conform with the newest dictionary of the
application, possibly with new tables, new fields, renamed fields,
etc. The user data is preserved during the process, which can
cause some bloating.

I still don't see what that has to do with launching another
instance of Access. It's easy enough to make structural changes in
your back end just using DAO without any need to open an additional
copy of Access.

[]

What procedure do you use to convert the user's backend data for
upgrade? Do you do it manually onsite? Do you compact it
afterwards? I always do, using an automated tool.

I've never found it cost-effective to take the time to program it --
I always do it manually. Of course, it happens very, very seldom if
you're app has not gone into production use before it was finished.

But even if you're automating the process, I still see absolutely no
justification for using a second instance of Access to do the job.

 

[David W. Fenton]

I don't know of any other reasons why Application.SaveAsText 6
fails, so I guess I'm not helpful to you.

When run with the back end open, there is no issue.

I'm trying to run it from the front end to back up the data tables
in the back end, and using another instance of Access is the only
way I can think of to do that, but I've never been able to get it to
work.

Have you been successful automating another instance of Access to
back up back end data tables in this fashion?

 

[David W. Fenton]

That was exactly my point when David asked what the impediments
were to using a library db. *Can't* use the reference db as a
library, can you? So you open another instance of Access and use
automation with the Access. Application object instead.

That looks like a design error to me.

Again, I just don't see there is ever much cause to launch a second
instance of Access. Not a single one of the examples that anyone
else has come up with seems necessary at all.

 

[Sky]

I still don't see what that has to do with launching another
instance of Access. It's easy enough to make structural changes in
your back end just using DAO without any need to open an additional
copy of Access.

As I said, the reason is that automated updating of tables while preserving
the existing data can cause bloating in the (multiple) back end databases as
well as the front end database. I have a separate application that is
invoked using automation that performs it.

[]

What procedure do you use to convert the user's backend data for
upgrade? Do you do it manually onsite?

I've never found it cost-effective to take the time to program it --
I always do it manually. Of course, it happens very, very seldom if
you're app has not gone into production use before it was finished.

Hm, ok then. You do it manually. You probably compact manually (after
altering a table structure.) It is not cost-effective to automate the
process relative to your time and rates.

In my case, the customers do request changes with new features over time,
and my time is expensive. I definitely do not want to waste my time (or my
customers' money) manually upgrading every installation and back-end
database, and I do not trust any person (including myself) to enforce all
relationships and indices manually, so I have fully automated the upgrade
process to support that, consolidated into a self-contained install package
(using InnoSetup).

- Steve

 

[David W. Fenton]

[quoting me:]

As I said, the reason is that automated updating of tables while
preserving the existing data can cause bloating in the (multiple)
back end databases as well as the front end database. I have a
separate application that is invoked using automation that
performs it.

Why? I don't understand what is gained by doing this.

[]

What procedure do you use to convert the user's backend data for
upgrade? Do you do it manually onsite?

I've never found it cost-effective to take the time to program it
-- I always do it manually. Of course, it happens very, very
seldom if you're app has not gone into production use before it
was finished.

Hm, ok then. You do it manually. You probably compact manually
(after altering a table structure.) It is not cost-effective to
automate the process relative to your time and rates.

In my case, the customers do request changes with new features
over time, and my time is expensive. I definitely do not want to
waste my time (or my customers' money) manually upgrading every
installation and back-end database, and I do not trust any person
(including myself) to enforce all relationships and indices
manually, so I have fully automated the upgrade process to support
that, consolidated into a self-contained install package (using
InnoSetup).

Well, if you've got the same app running in multiple locations,
then, yes, it makes sense to automate structural changes. I don't
have any of those -- all my apps are for one client each.

 

[Bob Collinson]

I launch another instance of Access to do an 'archive' function. When the
user clicks the 'archive' button I build another copy of the front end
database with all the forms queries etc and then use another instance of
Access to copy all the tables from the backend into the 'archive' database so
the new database has now a none linked complete copy of the original. (After
this operation the user can use another button that deletes all records prior
to a selected date and then compact the back-end). The idea is that the
current database is kept to a managable size but the old records are still
available. I have some 50 customers using the database using all versions of
Access from 97 through to 2007. I couldn't find an easier way than creating
another instance of Access to do this (I am not an Access guru although I
have been using it since version 1.1).

[quoting me:]

In my case, there is some bloating after the user installs a new
version of the application that has new structural changes in the
backend tables. The install process automatically rebuilds the
user back-end tables to conform with the newest dictionary of the
application, possibly with new tables, new fields, renamed fields,
etc. The user data is preserved during the process, which can
cause some bloating.

I still don't see what that has to do with launching another
instance of Access. It's easy enough to make structural changes in
your back end just using DAO without any need to open an additional
copy of Access.

[]

What procedure do you use to convert the user's backend data for
upgrade? Do you do it manually onsite? Do you compact it
afterwards? I always do, using an automated tool.

I've never found it cost-effective to take the time to program it --
I always do it manually. Of course, it happens very, very seldom if
you're app has not gone into production use before it was finished.

But even if you're automating the process, I still see absolutely no
justification for using a second instance of Access to do the job.

 

[David W. Fenton]

=?Utf-8?B?Qm9iIENvbGxpbnNvbg==?= <Bob
(e-mail address removed)> wrote in

I launch another instance of Access to do an 'archive' function.
When the user clicks the 'archive' button I build another copy of
the front end database with all the forms queries etc and then use
another instance of Access to copy all the tables from the backend
into the 'archive' database so the new database has now a none
linked complete copy of the original. (After this operation the
user can use another button that deletes all records prior to a
selected date and then compact the back-end). The idea is that
the current database is kept to a managable size but the old
records are still available. I have some 50 customers using the
database using all versions of Access from 97 through to 2007. I
couldn't find an easier way than creating another instance of
Access to do this (I am not an Access guru although I have been
using it since version 1.1).

Why do you need to archive the front end? As developer, you ought to
have all the copies of it you need, no?

If I were doing this, on the theory that I wanted a snapshot of the
data and the application that went with it, I think what I'd do is:

1. copy the front end from the centralized reference copy on the
LAN.

2. delete the linked tables in it.

3. import the data tables into it.

4. do whatever I needed to do with deleting records and compacting.

None of this requires launching another instance of Access. The only
reason you require that is because you're basing the application
part of your archive on the currently opened front end. Since there
ought to be plenty of copies of that I don't see why you need to use
the one that's currently in use.

If your user needs to do it when disconnected from the LAN, then I'd
keep a copy of it as a template, thus obviating the need to launch
another instance of Access.

Again, in this discussion, I have not as yet seen a single situation
where launching another instance of Access from Access was actually
justified.

 

[Bob Collinson]

Hi David,

Not saying that what you say is wrong, however, I think you slightly missed
my point. I don't use this technique for my own development, just for the
end users to do their own archiveing. My customers are in, currently, 3
different countries (Australia, New Zealand and the UK so I don't want to be
doing things for them, I am too old to enjoy the travel any more...) and all
use the system in a multiuser environment. I am not doing the 'archive',
they are when they feel the need for it, say, once a year. What I want to
provide is a way using the 'normal' front end for them to click a button and
allow the process to be automatic. The system keeps track of when the
archive is 'done' and the folder that has been used to store the archive copy
and then allows the user to access the archive from the normal front end
(after it is finished) ie start up the archive system by clicking on the
relevant copy as listed in a combo box (which is of course not starting
another instance of Access within the current one .. but I digress). By
doing it this way the user doesn't have to know anything about Access and in
fact doesn't really need to know anything about where the normal frontend and
backend is stored. (The system is for security guards to use and I have
taken the, not very politically correct, view that my users are not always
the most computer savvy people..). The system does not allow the user to
delete old records unless an archive has been done within the previous 24
hours so at worst they can lose 24 hours worth of data. About the only thing
I expect of the user is that it is suggested that they be the only user on
the system at the time of doing the archive although I don't think that
really matters.

If you can suggest an easier way of doing this then I would certainly look
at incorporating it (although if it works don't mess with it is probably
appropriate).

Regards (and thanks for all the good stuff that you have provided over the
years in the different Access user groups).

 

[David W. Fenton]

=?Utf-8?B?Qm9iIENvbGxpbnNvbg==?=

If you can suggest an easier way of doing this then I would
certainly look at incorporating it (although if it works don't
mess with it is probably appropriate).

I suggested a better way that doesn't require launching another
instance of Access. Dunno how much more clear I could have been.

 

[Bob Collinson]

Well the way I read your better method (which I accept is better) the system
needs to know where the second copy of the front end is stored so that it can
copy it. Currently I have no way of knowing this (and the front end cannot
copy itself unfortunately).

What your suggestion has done is to make me rethink the way my product is
deployed. I think what I will do is suggest to the customer that a backup
copy of the front end is stored in the same folder as the back end, say
called XXXFrontEndBackup.mdb) and then check for its existance upon loading
of the front end and after an appropriate error message bomb off if it isn't
there. This way I can use your method AND make sure the customer has a
backup (which bitter experience tells me some don't ...).

Your method overcomes a problem that I have just encountered in that without
re thinking the way I do custom toolbars the custom toolbars don't get
'copied' under Access 2007 so my 'Archive' system doesn't have the custom
toolbars that the original system has. (I know that I have to get into the
new method of toolbars/ribbons eventually but up until 2007 I have been able
to do my development work in 97 and just convert to 2000, 2002/3 without any
specific coding for the appropriate version. Thanks to MS I will now,
eventually, have to do a specific version for 2007, bummer).

Anyway, I still reckon that the way of using a second instance of Access is
the only way to do my Archive procedure if you don't know where there is a
second copy of the front end. Having said that I guess that you could put
some of the archive procedure in the backend, and do the 'copy' part of the
archive from there, which means I guess that you are correct!

Anyway, thanks for making me think more about it so I will now provide my
customers with a better backed up system. Thanks

 

[David W. Fenton]

=?Utf-8?B?Qm9iIENvbGxpbnNvbg==?=

Well the way I read your better method (which I accept is better)
the system needs to know where the second copy of the front end is
stored so that it can copy it. Currently I have no way of knowing
this (and the front end cannot copy itself unfortunately).

Er, you could have your installer create the backup copy in the same
folder as the front end. Or you could put it in the same folder as
the back end, and then you could use the connect string for your
linked tables to figure out where it is.

This is not a complexity that is difficult to overcome.

What your suggestion has done is to make me rethink the way my
product is deployed. I think what I will do is suggest to the
customer that a backup copy of the front end is stored in the same
folder as the back end, say called XXXFrontEndBackup.mdb) and then
check for its existance upon loading of the front end and after an
appropriate error message bomb off if it isn't there. This way I
can use your method AND make sure the customer has a backup (which
bitter experience tells me some don't ...).

I'm really glad my suggestion has helped you.

Your method overcomes a problem that I have just encountered in
that without re thinking the way I do custom toolbars the custom
toolbars don't get 'copied' under Access 2007 so my 'Archive'
system doesn't have the custom toolbars that the original system
has. (I know that I have to get into the new method of
toolbars/ribbons eventually but up until 2007 I have been able to
do my development work in 97 and just convert to 2000, 2002/3
without any specific coding for the appropriate version. Thanks
to MS I will now, eventually, have to do a specific version for
2007, bummer).

There seems to be something of a bias against using template files
among modern programmers. I don't know if that's a holdover from the
old days when disk space was expensive, or if it's just that for
certain uses, template-based operations become problematic (since
files created from a template don't inherit changes to the
template).

But it really is a very simple way to do things, and often much
easier to implement than most of the alternatives.

Anyway, I still reckon that the way of using a second instance of
Access is the only way to do my Archive procedure if you don't
know where there is a second copy of the front end.

But that's pretty easily fixed by putting it in the same folder as
the back end.

Having said that I guess that you could put
some of the archive procedure in the backend, and do the 'copy'
part of the archive from there, which means I guess that you are
correct!

Anyway, thanks for making me think more about it so I will now
provide my customers with a better backed up system. Thanks

I'm glad to have helped.

I'm still waiting for examples of launching another instance of
Access being the only way to accomplish a task. So far, all the
suggestions we've gotten in this thread have been better handled
with in the main Access instance by simply changing the procedures
to something much simpler.

I do believe that I know of two required scenarios:

1. Michael Kaplan's SOON utility:

http://trigeminal.com/lang/1033/utility.asp?ItemID=8#8

2. if someone can get it to work, the SaveAsText 6 operation to back
up data tables in a back end data file (I can't get it to work)

Any others?

 

[Sky]

I'm still waiting for examples of launching another instance of
Access being the only way to accomplish a task. So far, all the
suggestions we've gotten in this thread have been better handled
with in the main Access instance by simply changing the procedures
to something much simpler.

I do believe that I know of two required scenarios:

1. Michael Kaplan's SOON utility:

http://trigeminal.com/lang/1033/utility.asp?ItemID=8#8

2. if someone can get it to work, the SaveAsText 6 operation to back
up data tables in a back end data file (I can't get it to work)

Any others?

Perhaps you forgot the earlier parts of this thread, to which you replied
already. Other launch examples that I proposed:

I like to automate my build process in preparing a new install, to
make it faster, more robust, and more error-free.

... the only way to compile a front-end to a compiled .mde is using
automation, using something like: appAccess.SysCmd 603,
strMdbFile, strMdeFile. ....
I like to perform automated testing of my front end during the
build process. For example, I open queries using a testing
harness, display all forms (partly to check for column widths and
overlaps), and display all reports. (Such testing can verify that
there are no field renaming or table renaming problems, for
example.) Anything that requires DoCmd in the front end requires
automation, if you are running it from a testing or build harness
as a separate programmer tool. This is even more useful if you
have multiple front ends in your application, which might be
installed for different users but within the same build and/or
install package.

Another case for launching is when you have multiple related applications
with different front-ends and back-ends. In my case, I do this in a few
cases for the user to perform complex data imports from external sources,
which is implemented as a separately launched application (also separately
maintained and funded). I do not use SOON, I simply launch a new instance
and close the prior instance.

- Steve

 

[David W. Fenton]

Perhaps you forgot the earlier parts of this thread, to which you
replied already. Other launch examples that I proposed:

All your examples were development rather than production uses, and
I am thinking about production code in an application in the field
that would benefit from opening another instance of Access (and,
yes, you're right that I didn't say that!).

[]

Another case for launching is when you have multiple related
applications with different front-ends and back-ends. In my case,
I do this in a few cases for the user to perform complex data
imports from external sources, which is implemented as a
separately launched application (also separately maintained and
funded). I do not use SOON, I simply launch a new instance and
close the prior instance.

I have not encountered any circumstance in my professional
development where there was a need for such related applications. I
believe that from the standpoint of code maintenance, it's much
easier to maintain a single application and then tailor the UI for
the particular specific use. The key clue to me that multiple apps
are a problem is when you have the same UI object
(form/query/report) in more than one front end.

One way to also approach that would be to use a library database (or
to treat the other apps as library DBs) with the common objects. But
again, I just don't see any reason to maintain separate apps.

But launching an app from another app is just the same functionality
as SOON, so I would certainly count it as a valid reason for another
instance of Access to be opened. What I *don't* acknowledge in a
production application (i.e., not during development) is any but the
one case of launching another instance of Access, staying in the
initial instance, and then shutting down the additional instance.

I just can't see any non-development reasons for that (other than
the one I've already adduced).