How to determine when back-end file has been updated

[rdemyan via AccessMonster.com]

Anyone have any ideas on how to determine when the back-end file (containing
only tables) has been updated with new data.

The date/time of the file won't work because it gets updated to the current
date/time when the back-end file is compacted.

I'm just looking for an easy way to determine when there has been a change to
data in any table in the back-end file.

Maybe something is updated in one of the system tables??

Thanks.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200612/1

[Tom van Stiphout]

On Mon, 04 Dec 2006 19:45:42 GMT, "rdemyan via AccessMonster.c om"
<u6836@uwewrote :

Not that I know of. You may need to track this yourself, perhaps by
establishing an AuditTrail table that stores the time each user logs
in and out.

-Tom.

>Anyone have any ideas on how to determine when the back-end file (containing
only tables) has been updated with new data.

The date/time of the file won't work because it gets updated to the current
date/time when the back-end file is compacted.

I'm just looking for an easy way to determine when there has been a change to
data in any table in the back-end file.

Maybe something is updated in one of the system tables??

Thanks.

[rdemyan via AccessMonster.com]

Yeah, but just because a user logs in/out doesn't mean that any data changed.
Any ideas on what would cause the file date/time of a back-end file to change
(assume that the back-end file only contains tables).. The ones that occur
to me are:

1) Updates (add/edit/delete) to data in the tables
2) Compacting

Anything else?

Tom van Stiphout wrote:

>Not that I know of. You may need to track this yourself, perhaps by
establishing an AuditTrail table that stores the time each user logs
in and out.

-Tom.

>>Anyone have any ideas on how to determine when the back-end file (containing
only tables) has been updated with new data.

[quoted text clipped - 8 lines]

>>
Thanks.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200612/1

[Tom van Stiphout]

On Tue, 05 Dec 2006 14:08:56 GMT, "rdemyan via AccessMonster.c om"
<u6836@uwewrote :

Then expand the audit trail to write events about data updates as
well.

Backup programs (you are using one of those, right?) typically leave
the datetime stamp alone, but may clear the Archive bit.

-Tom.

>Yeah, but just because a user logs in/out doesn't mean that any data changed.
Any ideas on what would cause the file date/time of a back-end file to change
(assume that the back-end file only contains tables).. The ones that occur
to me are:

1) Updates (add/edit/delete) to data in the tables
2) Compacting

Anything else?

Tom van Stiphout wrote:

>>Not that I know of. You may need to track this yourself, perhaps by
establishin g an AuditTrail table that stores the time each user logs
in and out.

-Tom.

>>>Anyone have any ideas on how to determine when the back-end file (containing
only tables) has been updated with new data.

[quoted text clipped - 8 lines]

>>>
Thanks.

[rdemyan via AccessMonster.com]

Yes, I'm afraid, I'll have to expand to write events about data updates.
This looks to me like a large task. I tend to code most of my saves because
multiple tables get updated and because this application deals with very
complex issues. I've probably got at least 100 save routines not to mention
some forms where I let Access do most if not all of the work.

I'm not using a commercial back-up routine because the company network and IT
environment is "highly secure" (users do not have administrator rights on
Windows, no executables can be installed, no downloads, no zip files, etc,
etc.). I do have a backup/compact routine that I use. I originally planned
to use it with Windows Scheduler, but now that program is no longer available
to us. However, it has it's own "timer" but that means I have to leave the
Access app up and running.

Tom van Stiphout wrote:

>Then expand the audit trail to write events about data updates as
well.

Backup programs (you are using one of those, right?) typically leave
the datetime stamp alone, but may clear the Archive bit.

-Tom.

>>Yeah, but just because a user logs in/out doesn't mean that any data changed.

[quoted text clipped - 18 lines]

>>>>
Thanks.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200612/1

[Tom van Stiphout]

On Tue, 05 Dec 2006 14:28:26 GMT, "rdemyan via AccessMonster.c om"
<u6836@uwewrote :

Yes, changing the requirements after the app has been written is
always relatively expensive.

What were you trying to accomplish in the first place? You wrote you
wanted to know if a data file was updated. Why is that so important?
Perhaps we can suggest alternatives.

In highly secure environments Access is sometimes not allowed, because
it cannot be secured.
SQL Server has better audit trail capabilities, with its Trigger
objects.

-Tom.

>Yes, I'm afraid, I'll have to expand to write events about data updates.
This looks to me like a large task. I tend to code most of my saves because
multiple tables get updated and because this application deals with very
complex issues. I've probably got at least 100 save routines not to mention
some forms where I let Access do most if not all of the work.

I'm not using a commercial back-up routine because the company network and IT
environment is "highly secure" (users do not have administrator rights on
Windows, no executables can be installed, no downloads, no zip files, etc,
etc.). I do have a backup/compact routine that I use. I originally planned
to use it with Windows Scheduler, but now that program is no longer available
to us. However, it has it's own "timer" but that means I have to leave the
Access app up and running.

Tom van Stiphout wrote:

>>Then expand the audit trail to write events about data updates as
well.

Backup programs (you are using one of those, right?) typically leave
the datetime stamp alone, but may clear the Archive bit.

-Tom.

>>>Yeah, but just because a user logs in/out doesn't mean that any data changed.

[quoted text clipped - 18 lines]

>>>>>
>Thanks.

[rdemyan via AccessMonster.com]

Tom: First, thanks for your help on this issue.

I'll explain what I'm trying to do and believe me I understand that this is
not best practice, but it is more of an issue of speed of data retrieval and
ensuring that users will use the application (about 10 to 20 users total)

My application is calculation intensive and the servers are agonizingly slow.
Administrators of my application only update the backends once a month (twice
a month max). So, my launching program allows the back-end file to be
downloaded to the user's PC. This will provide maximum speed for these
calculations/manipulations of data. Without this, just logging into the main
app connected to the server back-end file can take five minutes (normal time
is 15 seconds when linked to back-end file on PC) at some locations.

We have been operating this way for 6 months and the users are very satisfied
with this setup. It has been drilled into their heads that they are connected
to a back-end file on their PC and not the server, so there is a chance that
some of the data may not be up to date. Of course, the launcher allows users
to connect to the server back-end file if they want to (trust me, nobody
wants to).

To reiterate only administrators of the application can update data. AFter
the data is updated, they send out an e-mail via the main app that informs
users to download the back-end file the next time they login. The launcher
has a checkbox that user's have to check to tell the launcher to download the
back-end file. Also, they have to point the launcher to where the back-end
file is on the server. This is easy since there are two radio option buttons:
one that when pressed inputs the server location of the back-end file and the
other when pressed inputs the local PC location of the back-end file.

This has all worked fine, but I would like to automate the process, since
users would really rather not be bothered with having to do anything other
than entering User ID and password on the launcher. So instead of sending out
an e-mail when the back-end file is updated, I would like the launcher
program to be able to determine that the back-end file on the PC is out-of-
sync with the one on the server and then pop up a message alerting the user.
If the user says 'Yes' to the download question, the back-end file is
automatically downloaded (this typically takes 10 minutes for a 75MB file; as
I said, the servers are excrutiatingly slow; but remember, this only occurs
about once a month as opposed to dealing with slow servers daily).

I can handle the coding since I already have similar coding in the launcher
to check for front-end software updates (using version number in a front-end
table). What I need is something to check so that I can tell if the back-end
file on the server is updated relative to the back-end file on the user's PC.
File date/time will change if the back-end files are compacted which doesn't
necessarily mean that any data has been updated.

So, let me reiterate why we can do this in my situation:

1) Most importantly, back-end file is only updated once or twice a month and
only by application administrators. Almost all users of the app only want to
use the data not add/edit data. Again, this is mostly a
calculation/manipulation/analysis application of existing data. If this were
a typical app where data is updated all the time, this "download" strategy
would never work.

2) The servers are excrutiatingly slow and if I didn't have the abiility to
download the back-end file, no one would use the app.

3) IT will not allow us to install SQL Server without a certification process
on the app and SQL Server that takes a minimum of two years. There is no
reasoning with IT on this problem.

Thanks for any suggestions.
Tom van Stiphout wrote:

>Yes, changing the requirements after the app has been written is
always relatively expensive.

What were you trying to accomplish in the first place? You wrote you
wanted to know if a data file was updated. Why is that so important?
Perhaps we can suggest alternatives.

In highly secure environments Access is sometimes not allowed, because
it cannot be secured.
SQL Server has better audit trail capabilities, with its Trigger
objects.

-Tom.

>>Yes, I'm afraid, I'll have to expand to write events about data updates.
This looks to me like a large task. I tend to code most of my saves because

[quoted text clipped - 23 lines]

>>>>>>
>>Thanks.

--
Message posted via http://www.accessmonster.com

[jlepack]

I use an FE/BE system where the user has a copy of the FE on their PC
that is connected to the BE. I have a table in my BE that has a FE
version number and a table in my FE that has the FE verison number.
Anytime I change the FE I change the verison number for the client.
Everytime the user opens the FE on their PC it checks this table on the
local to make sure the version number in the BE matches. If it doesn't
the program downloads the new FE.

It shouldn't be too difficult to implement this with your situation.

Cheers,
Jason Lepack

rdemyan via AccessMonster.c om wrote:

Tom: First, thanks for your help on this issue.

I'll explain what I'm trying to do and believe me I understand that this is
not best practice, but it is more of an issue of speed of data retrieval and
ensuring that users will use the application (about 10 to 20 users total)

My application is calculation intensive and the servers are agonizingly slow.
Administrators of my application only update the backends once a month (twice
a month max). So, my launching program allows the back-end file to be
downloaded to the user's PC. This will provide maximum speed for these
calculations/manipulations of data. Without this, just logging into the main
app connected to the server back-end file can take five minutes (normal time
is 15 seconds when linked to back-end file on PC) at some locations.

We have been operating this way for 6 months and the users are very satisfied
with this setup. It has been drilled into their heads that they are connected
to a back-end file on their PC and not the server, so there is a chance that
some of the data may not be up to date. Of course, the launcher allows users
to connect to the server back-end file if they want to (trust me, nobody
wants to).

To reiterate only administrators of the application can update data. AFter
the data is updated, they send out an e-mail via the main app that informs
users to download the back-end file the next time they login. The launcher
has a checkbox that user's have to check to tell the launcher to download the
back-end file. Also, they have to point the launcher to where the back-end
file is on the server. This is easy since there are two radio option buttons:
one that when pressed inputs the server location of the back-end file and the
other when pressed inputs the local PC location of the back-end file.

This has all worked fine, but I would like to automate the process, since
users would really rather not be bothered with having to do anything other
than entering User ID and password on the launcher. So instead of sending out
an e-mail when the back-end file is updated, I would like the launcher
program to be able to determine that the back-end file on the PC is out-of-
sync with the one on the server and then pop up a message alerting the user.
If the user says 'Yes' to the download question, the back-end file is
automatically downloaded (this typically takes 10 minutes for a 75MB file; as
I said, the servers are excrutiatingly slow; but remember, this only occurs
about once a month as opposed to dealing with slow servers daily).

I can handle the coding since I already have similar coding in the launcher
to check for front-end software updates (using version number in a front-end
table). What I need is something to check so that I can tell if the back-end
file on the server is updated relative to the back-end file on the user's PC.
File date/time will change if the back-end files are compacted which doesn't
necessarily mean that any data has been updated.

So, let me reiterate why we can do this in my situation:

1) Most importantly, back-end file is only updated once or twice a month and
only by application administrators. Almost all users of the app only want to
use the data not add/edit data. Again, this is mostly a
calculation/manipulation/analysis application of existing data. If this were
a typical app where data is updated all the time, this "download" strategy
would never work.

2) The servers are excrutiatingly slow and if I didn't have the abiility to
download the back-end file, no one would use the app.

3) IT will not allow us to install SQL Server without a certification process
on the app and SQL Server that takes a minimum of two years. There is no
reasoning with IT on this problem.

Thanks for any suggestions.
Tom van Stiphout wrote:

Yes, changing the requirements after the app has been written is
always relatively expensive.

What were you trying to accomplish in the first place? You wrote you
wanted to know if a data file was updated. Why is that so important?
Perhaps we can suggest alternatives.

In highly secure environments Access is sometimes not allowed, because
it cannot be secured.
SQL Server has better audit trail capabilities, with its Trigger
objects.

-Tom.

>Yes, I'm afraid, I'll have to expand to write events about data updates.
This looks to me like a large task. I tend to code most of my saves because

[quoted text clipped - 23 lines]

>>>>>
>Thanks.

--
Message posted via http://www.accessmonster.com

[rdemyan via AccessMonster.com]

Yes, I currently use a similar method for the front-end, except that I check
the version number of the front end on the local PC against the version
number of any updated front end that might be in the 'Updates' folder on the
server.

I could change the backend version number in a similar manner. But, this
means that the admininistrator s, who are the only ones that can
add/modify/delete data, have to remember to do this or I have to add code to
handle this. There are probably at least 100 coded save routines not to
mention some forms where Access does all the work (while we don't add data
often, when we do, it's a large amount probably of order 20,000 to 50,000
records spread across 8 to different functional areas of the app. The front-
end measure .mde weighs in at almost 20 MB). Honestly, I don't think
administrators will want to remember to do this. This is not within their
experience with other applications.

I'm either going to have to bite the bullet or think some more about this
file date/time issue of the two back-end files (one on the local PC and one
on the the server). The idea would be to compare the two to decide when to
download the backend. If we limit compacting of the backend to once or twice
a month (i.e. after updates/edits), this could work. After all, if the data
is not being changed in the server back-end file why would one need to
compact it. Further, if virtually everyone is using a downloaded back-end
file, then the server back-end file should also be less susceptible to
corruption.

If I decide to try and implement the file date/time method, then I'm going to
write the server back-end file date/time in a table in the backend when the
backend is downloaded. I'll use that date for comparisons and not the
current file date/time of the local PC backend. Why? Because the
administrators are still practicing updating the backend file. They do use
their local version to do this since they can practice and not worry about
screwing things up.

Thoughts, suggestions.

Thanks.

jlepack wrote:

>I use an FE/BE system where the user has a copy of the FE on their PC
that is connected to the BE. I have a table in my BE that has a FE
version number and a table in my FE that has the FE verison number.
Anytime I change the FE I change the verison number for the client.
Everytime the user opens the FE on their PC it checks this table on the
local to make sure the version number in the BE matches. If it doesn't
the program downloads the new FE.

It shouldn't be too difficult to implement this with your situation.

Cheers,
Jason Lepack

>Tom: First, thanks for your help on this issue.

[quoted text clipped - 81 lines]

>>>>>>
>>Thanks.

--
Message posted via AccessMonster.c om
http://www.accessmonster.com/Uwe/For...ccess/200612/1