[AccessD] Access 2007 - The story so far.

Hi Bill,

For this (and most roll outs) I use a VB script that the user puts on their
desktop or program list.   The script is the database as far as the users
are concerned. They double click it and the database opens.

In reality here is what happens.  The VB Script check a version number
stored in a local txt file against a version number in a text file on the
server. If the numbers are different then the script kills the local FE copy
of the users database (there is no data in there ever, just forms and code)
and downloads a new FE to the local machine and then opens it. This happens
so seamlessly that the users never even notice it happening.   If the
version numbers match then the existing local version of the FE just opens.

Now, when the local version of the FE opens it has code that uses ADO to
talk to the BE database which is on a different server altogether. This is
often SQL Server, but I can and do also use Access as the BE.

So what I meant by "Update" was to change the version number and force the
VB Script to download a new version.  This new version would always fail as
the ADO references would always be broken.  It would fail elegantly along
the lines of "I cannot talk to, or find, the back end database" and
automatically close.  This code is already in the FE as part of the database
start up code. There are four checks.

1: Server is online
2: Database is on Server
3: User has permission to Server
4: User has permission to access database

When I said "Run it manually" what I was doing was inadvertently fixing the
problem.  I would open up an unlocked version of the database on the PC that
was having issues and check out the code to see exactly where it would
error, I would then recompile it (And here is where I would inadvertently
fix the problem). Recompiling on a NON SP1 machine fixes the references
immediately. Then ofc ourse it would run with out error and make me look
like a total moron.

Remember the very first time I had this I said I removed the error handlers
(then recompilied) and then ran it and I was surprised it ran without error.
If had hadn't recompiled it would have failed, but force of habit, I always
compile before running code.

But downloading a new version of the FE which had been compiled on an SP1
machine, and voila! Instant fail again.

Let me know if that is clear or too much gibberish Bill.
Cheers
Darryl.




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
(VBACreations.Com)
Sent: Monday, 22 August 2011 10:18 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Access 2007 - The story so far.

Darryl, I want to go to one of your earlier characterizations of the
problem:

Quoting:
...the script used to update the database on the other PC's was failing
[when]
the front end tried to talk to the back end using ADO.  If I manually ran it
then Access seemed to fix the reference, but using script to open the accde
file it would always fail as per the links below.

1) What do you mean "update" the database on the other machines?... you mean
anything at all (insert, delete, etc) or do you mean copying objects?
2) What do you mean when you manually ran it? Ran what, the script? 

Can you give more details of when this does and does not fail?

Does it fail in either of these environments

If a database and excel file which uses ADO to query the accdb file are both
on a W7SP1 user's machine then given to a client such that
    a) XP user keeps both files on his/her desktop
    b) Several XP users keeps the Excel file on their PCs and the accdb on
goes on a server for 
          several users to make use of

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Monday, August 22, 2011 12:29 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access 2007 - The story so far.