Permissions need to link files with code. **Need Help**

[eighthman11]

Hello everyone (using access 2000)

Hopefully someone can help. I have code in my application that
creates a link to an excel spreadsheet on the fly. When the process
is over it deletes the link. I also have code that links a text file
on the fly. When the process is over it deletes the object.

I have to do these links on the fly. So any solutions that remove the
process of linking objects on the fly are not feasible. Also the link
objects will not always have the same name. The name of the link
object will be the same as the CurrentUser().

Everything works fine when I run the processes; signed in with Admins
authority but when a User signs in the code will not create the link
or delete the link.

When I gave one of the users Admins authority the processes ran
without a problem.

I really really really don't want to give the users Admins authority.
Does anyone out there have a solution? Any help appreciated. Can you
give someone temporary authority to an object with code then remove it
when the process is complete?

Thanks

Ray

 

[Clifford Bass]

Hi Ray,

Granting authority to do an admin action requires admin authority to
start with. Otherwise you non-admin users could simply change themselves
into admins. So there will not be any way to do that in code.

Is there a good reason for not doing as I suggested in response to your
other post?

Clifford Bass

 

[eighthman11]

Hi Ray,

     Granting authority to do an admin action requires admin authority to
start with.  Otherwise you non-admin users could simply change themselves
into admins.  So there will not be any way to do that in code.

     Is there a good reason for not doing as I suggested in response to your
other post?

                     Clifford Bass










- Show quoted text -

Hey Clifford: (thanks for the repsponse)

The reason I am doing a link on the fly is because:

1) The excel spreadsheet that can be linked by the user always has
the same format but does not always have the same name. A combo box
is populated with excel spreadsheets and the user selects which one he
wants to import.

2) I can not change the format of the excel spreadsheet or add data
columns to it so the data being inserted to a sql server table is a
combination of the excel spreadsheet and what the user enters on the
form.

3) The application is multiuser and I didn't want to take a chance on
a timing issue that someone elses data gets accidently imported by
another user. So I came up with the crazy idea that I would do a link
on the fly and give the linked object name the same as the user's name
CurrentUser(). And it works great the linked excel is always unique
for the user; the combination of the data in the spreadsheet and the
data the user enters on the form gets sent to a table on a sql server
through an insert statement and then the link is deleted after the
insert.

The only problem I am having is with permissions.

 

[Clifford Bass]

Hi Ray,

You are welcome. Quick question: Are the users using their own copies
of the front end? If so, then you can do the import into a local table in
the front end instead of the back end and it will not clash with other users.

I have a couple other ideas that need testing first if that does not
solve the issues.

Clifford Bass

 

[eighthman11]

Hi Ray,

     You are welcome.  Quick question: Are the users using their own copies
of the front end?  If so, then you can do the import into a local tablein
the front end instead of the back end and it will not clash with other users.

     I have a couple other ideas thatneedtesting first if that doesnot
solve the issues.

                   Clifford Bass                  










- Show quoted text -

Hey Clifford:
The application is on a server. The users access the application
through a terminal server. Which has many benefits. One of which is
I only have to worry about maintaining one copy of the application and
the second reason is that our users are all across the country
accessing our intranet with different bandwiths (many of them suck),
but with the terminal server they only get a image of the application
and all the processing is done on the server in the main office.
All the tables in the application reside on the server. There are
no tables located on the users' computers.

I think I'm just going to bite the bullet and secure the
application. I just finished creating a process that hides all
objects in the application, disables the F11 button and locks down the
application when it opens unless it is opened with my user group of
InvAdmin. I'm also going to place the mde on the server instead of
the mdb. This should allow me to give the users Admins privilages
without the possibility of them screwing it up.

I really should have secured the application early but with it
being on a secured company server I didn't think it was to big of a
deal.

 

[Armen Stein]

The application is on a server. The users access the application
through a terminal server. Which has many benefits. One of which is
I only have to worry about maintaining one copy of the application and
the second reason is that our users are all across the country
accessing our intranet with different bandwiths (many of them suck),
but with the terminal server they only get a image of the application
and all the processing is done on the server in the main office.

Even using Terminal Server, each user could have their own copy of the
front-end in their own personal folder or desktop. Then you could do
the importing as suggested. You would need to manage the distribution
of new versions, but there are many techniques for this.

Also, sharing a front-end can lead to corruption and performance
issues anyway.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[eighthman11]

Even using Terminal Server, each user could have their own copy of the
front-end in their own personal folder or desktop.  Then you could do
the importing as suggested.  You would need to manage the distribution
of new versions, but there are many techniques for this.

Also, sharing a front-end can lead to corruption and performance
issues anyway.

Armen Stein
Microsoft Access MVPwww.JStreetTech.com

Hey Armen
The application has been online for two years with no corruptions
or performance issue. It truly is just a front end application with
only link tables to the server; no queries; and all processing is done
with modules calling stored procedures located on the sequel server.
I'd hate to go to multiple copies of the application because of one
permission issue caused by creating a link on the fly for an excel
spreadsheet.
Also our IT people have control of creating new objects on the
server so everytime we get a person who needs access to the
application I would have to go thru the red tape of getting IT to do
the setup.

 

[Clifford Bass]

Hi Ray,

Based on what you have since written about how you have the thing set
up, I have a different suggestion which you can accept or reject as you see
fit. I had been of the impression that you were using tables in an Access
database and then transferring the data into SQL Server; not that there were
no tables in Access; only links. Perhaps my misunderstanding; perhaps due to
your not explaining things completely. Being that you are using SQL Server
as the back end, you can simply use a work table on the server that includes
as part of the primary key a field that will the current user's logon name.
You should be able to set it to default to the user's logon name. The users
will not use the table directly, but will use a view which you will also
create that will restrict each user to his/her own records. This should
solve all of the issues you have raised. I have done similar things in
databases other than SQL Server, so I know it will work. However, as I am
much of a SQL Server user I cannot give you the details for SQL Server.

Clifford Bass

 

[Tony Toews [MVP]]

The application is on a server.

Do the users have read only access to the MDB/MDE on the server? If
so that would explain why they can't create/remove a linked table
programmatically.

The users access the application
through a terminal server. Which has many benefits. One of which is
I only have to worry about maintaining one copy of the application

However you really want to put the FE on each machine or place in a
user specific directory on the server. This will help avoid some
weird error messages when users are changing the same forms record
source, filters and such as well as corruptions. It is also much
easier to implement a new version of the database with changed
queries, forms, reports and VBA code.

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater now
supports creating a directory named after the user on a server. Given
a choice put the FE on the Citrix server to reduce network traffic and
to avoid having to load objects over the network which can be somewhat
sluggish.

and
the second reason is that our users are all across the country
accessing our intranet with different bandwiths (many of them suck),
but with the terminal server they only get a image of the application
and all the processing is done on the server in the main office.

Excellent reasons. Indeed I work on my own system and update one
clients FE via TS and a VPN.

I think I'm just going to bite the bullet and secure the
application. I just finished creating a process that hides all
objects in the application, disables the F11 button and locks down the
application when it opens unless it is opened with my user group of
InvAdmin. I'm also going to place the mde on the server instead of
the mdb. This should allow me to give the users Admins privilages
without the possibility of them screwing it up.

I really should have secured the application early but with it
being on a secured company server I didn't think it was to big of a
deal.

Security is complex and easy to screw up and I think overkill for what
you need to do.

Tony

 

[Tony Toews [MVP]]

The application has been online for two years with no corruptions
or performance issue. It truly is just a front end application with
only link tables to the server; no queries; and all processing is done
with modules calling stored procedures located on the sequel server.
I'd hate to go to multiple copies of the application because of one
permission issue caused by creating a link on the fly for an excel
spreadsheet.

The simplicity of your app is why you've been so fortunate. There
have been other reports of no problems sharing an Access database on a
server but they all seem to be quite simple and use macros with no
code.

Also our IT people have control of creating new objects on the
server so everytime we get a person who needs access to the
application I would have to go thru the red tape of getting IT to do
the setup.

<sigh>

Tony