MS Access Multiuser - Problem with Tony's Auto FE Updater too

Author

Problem with Tony's Auto FE Updater too

swedbera

2006-01-31, 11:24 am

I am also having trouble with Tony's updater and the encrypted password. The
only way I can get this to work is by making the ini file read-only to
prevent it from adding the additional line and encrypting the password, which
I do not want to do because the only user account that it will work with is
some type of administrator account.

If anyone has gotten this to work, I would appreciate some help with this.
I have a few questions:
1. Exactly what permissions does the user account have to have for this to
work.
2. Does the dba run it once and send all of the clients a copy of the
shortcut that is created on the dba's desktop? This seems to work, but there
is still the problem of leaving the administrator login/password in the ini
file that is used.
3. Can you have another database for development that uses the same
secured.mdw as the production db and just copy over the production db when
changes are made to the development db?

Arlene

Jesper F

2006-02-01, 9:27 am

> I do not want to do because the only user account that it will work with
> is
> some type of administrator account.

Are you sure - I'm experiencing some of the same problems (only on win98
machines) and sometimes I've been able to solve it by replace the username
and password in the ini-file with a different one that it was. Even if the
new username/pass is not an administrator.

swedbera

2006-02-01, 11:25 am

I've got my db locked down pretty good and I finally got this to work, no
matter how many times I run the shortcut that is created on the desktop.

I created a service account that belongs only to the user group. The user
group has open/run exclusive for database access and nothing else - no
permissions on any database objects (FE or BE). I also set the ini file to
read-only and hidden to prevent the password encryption so that the file
works each time it is run.

I'm sure if someone is determined they can find the file and get the
username/password, but even if they do, they won't be able to do anything
once they get into the database. I also removed the admin account from
admins, so the admin account can open the back-end (only) without using the
shortcut, but cannot do anything in the database. The only account that has
full permissions is the dbadmin account that I created and no one should be
able to get that password.

The only thing that I have not done yet, is to add code to refresh the
linked tables when the database is opened. Once I get that in place I can
deploy the database to my users. Currently, they are all accessing my
original database, which is secured, but there is only one copy of it on the
server that everyone runs. I have had so much database corruption this way
that I just had to find a way to split it, secure it, and deploy updates when
necessary (which is once a week because of new requests and changes).

If you can help me with the refresh linked table code, I would appreciate
it. Our network is pretty flaky and most of the users work remotely. Many
times, the user's are unable to connect to the network share, so I'm not sure
how to handle the possibility that they cannot open the database because they
cannot connect to the BE on the server. I don't want them to change the
location of the linked tables, should they get an error when logging in. I'm
also not sure from where or when this refresh table link code should run.

Thanks for replying,

Arlene

"Jesper F" wrote:

>
> Are you sure - I'm experiencing some of the same problems (only on win98
> machines) and sometimes I've been able to solve it by replace the username
> and password in the ini-file with a different one that it was. Even if the
> new username/pass is not an administrator.
>
>
>

Tony Toews

2006-02-02, 3:25 am

"swedbera" < swedbera@discussions
.microsoft.com> wrote:

>I am also having trouble with Tony's updater and the encrypted password. The
>only way I can get this to work is by making the ini file read-only to
>prevent it from adding the additional line and encrypting the password, which

Yes, there's a bug in the encryption logic that seems to have
something to do with Win 98 vs Win 2000 and XP systems. Is that the
case in your environment?

Another approach would be to fall back to V1.65.

>1. Exactly what permissions does the user account have to have for this to
>work.

Just enough to open the MDB to determine what version of Access it is.

>2. Does the dba run it once and send all of the clients a copy of the
>shortcut that is created on the dba's desktop?

Yes. That's one approach. Or put the shortcut in a share on the
server and tell the users to click on the shortcut.

>3. Can you have another database for development that uses the same
>secured.mdw as the production db and just copy over the production db when
>changes are made to the development db?

I have very little experience with Access security but that would seem
to be a reasonable approach.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Tony Toews

2006-02-02, 3:25 am

"swedbera" < swedbera@discussions
.microsoft.com> wrote:

>I created a service account that belongs only to the user group. The user
>group has open/run exclusive for database access and nothing else - no
>permissions on any database objects (FE or BE). I also set the ini file to
>read-only and hidden to prevent the password encryption so that the file
>works each time it is run.

Yes, that's exactly what I meant when I stated limited permissions.
Thank you for stating it much better than I did.

>If you can help me with the refresh linked table code, I would appreciate
>it.

Tables: Relink Access tables from code
http://www.mvps.org/access/tables/tbl0009.htm

>Our network is pretty flaky and most of the users work remotely.

Yikes. that alone is a major cause of corruptions.

I'd suggest going to Terminal Server or a SQL Server backend if the
users must work remotely.

>I'm
>also not sure from where or when this refresh table link code should run.

I'd suggest storing the location of the backend table in an INI file
in the same directory as your FE on the client workstation. This way
when they get a new FE they don't lose this share location. Also this
way each workstation can have their own mapping if they so desire.

You run this logic when the user first opens their MDB and before they
open any bound forms. I use a hidden unbound form for this purpose
set in the Startup options and put the code in the OnOpen event. I
also open a recordset against a key table. If it opens then fine no
problems. If it doesn't open then I decide there's a problem and
handle things appropriately.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

swedbera

2006-02-02, 11:24 am

Hi Tony,

Thanks for responding.

We are still working in Windows 2000 here. I do not have your version 1.65
to revert to, but the ini file works as long as it is set to Read-Only and
I've made sure that this account cannot access anything in the database,
should someone snoop and get the login and password. I should never have to
go back in and change the login or password in the ini file with it set this
way.

Drive mapping is another of the many issues we have here at this company,
which is why I always prefer to use the UNC path. I'm not sure I understand
why to use or how the ini file for the UNC path will work with the update
service. Can you clarify?

Currently, my database opens to the main menu(switchboard) and this form
calls a startup module. I could change it so that the startup module calls
the main switchboard after the links have been refreshed with no problems,
couldn't I?

Arlene

"Tony Toews" wrote:

> "swedbera" < swedbera@discussions
.microsoft.com> wrote:
>
>
> Yes, there's a bug in the encryption logic that seems to have
> something to do with Win 98 vs Win 2000 and XP systems. Is that the
> case in your environment?
>
> Another approach would be to fall back to V1.65.
>
>
> Just enough to open the MDB to determine what version of Access it is.
>
>
> Yes. That's one approach. Or put the shortcut in a share on the
> server and tell the users to click on the shortcut.
>
>
> I have very little experience with Access security but that would seem
> to be a reasonable approach.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm
>

swedbera

2006-02-02, 11:24 am

Hi Tony,

OK. I downloaded fRefreshLinks and tested it and it works; however, I have
a couple of concerns.

1. If the network shared drive is unavailable, it won't matter if the user
has the option of selecting a new path to relink the tables and I would
prefer not to even involve the user in this process. What I would like it to
do is to give them an error message that lets them know that the network is
unavailable and then kicks them out of the database. This way, they will
know to call the Help Desk.

2. After implementing the fRefreshLinks in combination with the
AutoFEUpdater, everytime I open the database from the shortcut on my desktop,
it thinks that the FE on the server has been updated (which it has not) and
copies down the FE again. The only thing that has changed is the links to
the BE being refreshed.

Do the links have to be refreshed every time the database is opened if the
location of the BE has not changed?

Thanks again for your help.

Arlene

"swedbera" wrote:
[color=darkred]
> Hi Tony,
>
> Thanks for responding.
>
> We are still working in Windows 2000 here. I do not have your version 1.65
> to revert to, but the ini file works as long as it is set to Read-Only and
> I've made sure that this account cannot access anything in the database,
> should someone snoop and get the login and password. I should never have to
> go back in and change the login or password in the ini file with it set this
> way.
>
> Drive mapping is another of the many issues we have here at this company,
> which is why I always prefer to use the UNC path. I'm not sure I understand
> why to use or how the ini file for the UNC path will work with the update
> service. Can you clarify?
>
> Currently, my database opens to the main menu(switchboard) and this form
> calls a startup module. I could change it so that the startup module calls
> the main switchboard after the links have been refreshed with no problems,
> couldn't I?
>
> Arlene
>
> "Tony Toews" wrote:
>

Tony Toews

2006-02-02, 1:24 pm

"swedbera" < swedbera@discussions
.microsoft.com> wrote:

>We are still working in Windows 2000 here. I do not have your version 1.65
>to revert to,

You can download it from my old versions page.

>Drive mapping is another of the many issues we have here at this company,
>which is why I always prefer to use the UNC path. I'm not sure I understand
>why to use or how the ini file for the UNC path will work with the update
>service. Can you clarify?

I'm not quite sure what you mean here. But you can use UNC paths in
the INI file.

>Currently, my database opens to the main menu(switchboard) and this form
>calls a startup module. I could change it so that the startup module calls
>the main switchboard after the links have been refreshed with no problems,
>couldn't I?

Correct. Although you'd really want to open an unbound form which
examines the links and then, if successful, opens the main menu. Now
if the main menu is unbound then you could put all this logic in that
forms OnOpen event.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Tony Toews

2006-02-02, 1:24 pm

"swedbera" < swedbera@discussions
.microsoft.com> wrote:

>OK. I downloaded fRefreshLinks and tested it and it works; however, I have
>a couple of concerns.
>
>1. If the network shared drive is unavailable, it won't matter if the user
>has the option of selecting a new path to relink the tables and I would
>prefer not to even involve the user in this process. What I would like it to
>do is to give them an error message that lets them know that the network is
>unavailable and then kicks them out of the database. This way, they will
>know to call the Help Desk.

Right. That's why I suggest opening a recordset based on a table in
the backend first. Then depending on the error you can display a
message and exit.

>2. After implementing the fRefreshLinks in combination with the
>AutoFEUpdater, everytime I open the database from the shortcut on my desktop,
>it thinks that the FE on the server has been updated (which it has not) and
>copies down the FE again. The only thing that has changed is the links to
>the BE being refreshed.

No, that shouldn't be happening. I store the date and time of the
MDB file on the server in an INI file on the client workstation. I
then compare the date/time in the INI file against the date/time of
the file on the server and then decide to copy down the file.

Can you email me your INI file? Use the tony *at* granite dot ab dot
ca email address.

>Do the links have to be refreshed every time the database is opened if the
>location of the BE has not changed?

No.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

swedbera

2006-02-02, 8:25 pm

Hi Tony,

Let me try to explain a little better what is happening....

If I use the fRefreshLinks function in conjuction with your AutoFEUpdater,
and try to login with an account having administrator priviledges, it updates
the links and opens with no problems. When I close the local db and reopen
it again from the desktop shortcut, it thinks that there has been a change to
the copy on the server and it copies it back down to the client again - even
though I was only opening the local copy and had not made changes to the
server copy. Unless your ini file gets updated when you open the client
version, which I don't believe is happening, I would think that the database
would just open and not copy down the server version again. I will send you
a copy of my ini file (both client and server versions).

If I use the fRefreshLinks and try to login with an account having very
limited priviledges, I get the following error:

You do not have the necessary permissions to use the 'object'. Have your
system administrator or the person who created this object establish the
appropriate permissions for you. Err # 3033. I don't know what permissions
the user has to have to be able to refresh linked tables - can you tell me?

In reference to my other question about placing the path to the BE DB in an
ini file...
I assume that you are doing this so that you can change the BE path without
changing the location in code (should you move the BE DB). How does that
work?

Arlene

"Tony Toews" wrote:

> "swedbera" < swedbera@discussions
.microsoft.com> wrote:
>
>
> You can download it from my old versions page.
>
>
> I'm not quite sure what you mean here. But you can use UNC paths in
> the INI file.
>
>
> Correct. Although you'd really want to open an unbound form which
> examines the links and then, if successful, opens the main menu. Now
> if the main menu is unbound then you could put all this logic in that
> forms OnOpen event.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems at
> http://www.granite.ab.ca/accsmstr.htm
>

Tony Toews

2006-02-02, 8:25 pm

"swedbera" < swedbera@discussions
.microsoft.com> wrote:

>If I use the fRefreshLinks function in conjuction with your AutoFEUpdater,
>and try to login with an account having administrator priviledges, it updates
>the links and opens with no problems. When I close the local db and reopen
>it again from the desktop shortcut, it thinks that there has been a change to
>the copy on the server and it copies it back down to the client again - even
>though I was only opening the local copy and had not made changes to the
>server copy. Unless your ini file gets updated when you open the client
>version, which I don't believe is happening, I would think that the database
>would just open and not copy down the server version again. I will send you
>a copy of my ini file (both client and server versions).

Note to lurkers. The problem was that the downloadable FE was not
being put in it's own folder.

>If I use the fRefreshLinks and try to login with an account having very
>limited priviledges, I get the following error:
>
>You do not have the necessary permissions to use the 'object'. Have your
>system administrator or the person who created this object establish the
>appropriate permissions for you. Err # 3033. I don't know what permissions
>the user has to have to be able to refresh linked tables - can you tell me?

I have no idea as I've never really used security. I'd suggest asking
in the security newsgroup.

Note that this problem has nothing to do with the Auto FE Updater so I
wouldn't bother mentioning this. Not that I'm trying to pass the
buck or anything. <smile>

>In reference to my other question about placing the path to the BE DB in an
>ini file...
>I assume that you are doing this so that you can change the BE path without
>changing the location in code (should you move the BE DB). How does that
>work?

Now just to be clear this has nothing to do with the Auto FE Updater.

There are some API calls which you can use to set and read INI files.

OTOH if your server name will never change then there's no reason you
couldn't hard code all this in your refresh linking code.

Or for that matter if you have your own copy of the BE MDB then before
you place the FE MDB on the server to be downloaded by the Auto FE
Updater you can do the refresh link code yourself. (Although I'd
never trust myself to remember to do this. <smile> )

However I have some shrink wrap apps which can reside on various
networks over which I have no control. Thus I have more generic code.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Joan Wild

2006-02-02, 8:25 pm

swedbera wrote:
>
> If I use the fRefreshLinks and try to login with an account having
> very limited priviledges, I get the following error:
>
> You do not have the necessary permissions to use the 'object'. Have
> your system administrator or the person who created this object
> establish the appropriate permissions for you. Err # 3033. I don't
> know what permissions the user has to have to be able to refresh
> linked tables - can you tell me?

The security FAQ
http://support.microsoft.com/?id=207793
explains the permissions that are necessary for a user to refresh links,
depending on the method you use.

--
Joan Wild
Microsoft Access MVP

Tony Toews

2006-02-02, 8:25 pm

"Joan Wild" <jwild@nospamtyenet.com> wrote:

>
>The security FAQ
> http://support.microsoft.com/?id=207793
>explains the permissions that are necessary for a user to refresh links,
>depending on the method you use.

Thanks Joan.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

swedbera

2006-02-03, 7:24 am

Thank you Tony and Joan for your help

Arlene

"swedbera" wrote:

> I am also having trouble with Tony's updater and the encrypted password. The
> only way I can get this to work is by making the ini file read-only to
> prevent it from adding the additional line and encrypting the password, which
> I do not want to do because the only user account that it will work with is
> some type of administrator account.
>
> If anyone has gotten this to work, I would appreciate some help with this.
> I have a few questions:
> 1. Exactly what permissions does the user account have to have for this to
> work.
> 2. Does the dba run it once and send all of the clients a copy of the
> shortcut that is created on the dba's desktop? This seems to work, but there
> is still the problem of leaving the administrator login/password in the ini
> file that is used.
> 3. Can you have another database for development that uses the same
> secured.mdw as the production db and just copy over the production db when
> changes are made to the development db?
>
> Arlene

Joan Wild

2006-02-03, 11:24 am

Tony Toews wrote:
> "Joan Wild" <jwild@nospamtyenet.com> wrote:
>
> Thanks Joan.

You're welcome. One of these days, I'm going to test out your utility.
Folks seem to have problems with a secure mdb.

--
Joan Wild
Microsoft Access MVP

Tony Toews

2006-02-03, 1:24 pm

"Joan Wild" <jwild@nospamtyenet.com> wrote:

>You're welcome. One of these days, I'm going to test out your utility.
>Folks seem to have problems with a secure mdb.

Yes, in a few situations. Trouble is I can't get a repro scenario at
my end.

TOny
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Jesper F

2006-02-03, 8:24 pm

>>You're welcome. One of these days, I'm going to test out your utility.
>
> Yes, in a few situations. Trouble is I can't get a repro scenario at
> my end.

I'm lurking here. Tony I've written you previously about this.
In my setup it only occurs on win98 machines (we have a mixed environment).
Most of the time I've been able to get it to work again by changing the
username and password in the ini-file.
I'll return when I can supply more details.

Jesper Fjølner

Sponsored Links