transferspreadsheet aclink permissions

E

eighthman11

I know this is going to be long winded but here it goes.

I have an application that needs to take the data from an excel
spreadsheet with the variables the user enters on the application form
and place the data with the variables in a table on a sql server.

1) I have a multiuser Access (2003) application on a server.

2) The users can save an excel spreadsheet to their "C" drive in a
folder called "Inventory". They always name the spreadsheet
"toollist"

3) The application on the server has a link to their spreadsheet
"toollist" on their "C" drive.

4) This worked great because being a multiuser application I didn't
have to worry about users interfering with each other data because the
link was always to the individual user's "C" drive.

5) Having the spreadsheet linked allowed me to insert the linked
excel data to a table with the variables from the import form of the
application.

6) THE PROBLEM - This process worked beautifully in the main office
with plenty of bandwidth. But when I sent the application out to our
field jobs and to other offices it took forever to run (because of
having an application on a server linked to an excel spreadsheet on a
person's "C" drive)

THE SOLUTION:
1) I changed the process so the first thing the application does is
copy the user excel spreadsheet to the server. (This works very
quickly)

2) When I copy the spreadsheet to the server I rename the spreadsheet
with the "currentuser()" name so the spreadsheet is unique to that
user on the server.

3) I do a "transferspreadsheet aclink" and link to a table I create
on the fly with the aclink. The name of the table is also the
currentuser() name. This way I don't have to worry about other users
data interfering with each other.

4) Now I have a link table for the specific user and I can insert the
data to a table with the variables coming from the import form of the
application.

5) This process works extremely fast.

6) THE PROBLEM: While doing the testing as ADMINS this process works
great but when I tried it as a normal user I got a permission problem
with the "transferspreadsheet aclink". I played around with the
permissions forever but the only way I can allow the users to do the
"transferspreadsheet aclink" where a table is created with the name of
the currentuser() is to place them in the ADMINS group.

7) I hate having the users in the ADMINS group.

Like I said pretty long winded. If I could find a way around giving
people ADMINS Privileges I would be a happy camper. Or if I could
insert the data from the spreadsheet to a table with variables from
the import form of the application I wouldn't need to do the aclink
and create a table on the fly

Any help appreciated. Thanks Ray
 
D

Douglas J. Steele

Your first mistake is having a multiuser Access application on a server.

Multiuser Access applications should always be split into a front-end
(containing the queries, forms, reports, macros and modules) and a back-end
(containing the tables and relations). Only the back-end should be on the
server: each user should have his/her own copy of the front-end, ideally on
his/her hard drive.

Correct that problem, and see whether you still have issues.
 
E

eighthman11

Your first mistake is having a multiuser Access application on a server.

Multiuser Access applications should always be split into a front-end
(containing the queries, forms, reports, macros and modules) and a back-end
(containing the tables and relations). Only the back-end should be on the
server: each user should have his/her own copy of the front-end, ideally on
his/her hard drive.

Correct that problem, and see whether you still have issues.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)






















- Show quoted text -

Hey Doug thanks for the response

The application is split from the data. The application is on a
terminal server. The application does not have any queries or local
tables. All the tables being used or linked to a sql server. All
processing is done with stored procedures on the sql server.
It was necessary to have the application on a terminal server
because we have people using the application at remote sites with poor
bandwidth. Having the application on a terminal server allows all the
processing to be done locally and the only thing transmitted to the
user is the images of the application. This allows the application to
run extremely fast.
Also it nice having the application in one location. We
currently have 82 people using the application so when I make a change
I don't have to worry if all 82 people have the new version. That
could end up being a logistic nightmare.
And the nicest thing about having the application on the terminal
server is I don't have to worry about what version of Access the users
have on their personal computer because I use the the Access
executable from the server.
Anyway, has hoping someone would have some ideas about the original
problem I submitted. Like before any help appreciated. Ray
 
D

Douglas J. Steele

You may not like the answer, but what I said is the case.

Users should never share the same front-end. Using Terminal Server does not
change that fact.

To ensure that each user is using the current application, see the free Auto
FE updates Tony Toews has at http://www.autofeupdater.com/

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Your first mistake is having a multiuser Access application on a server.

Multiuser Access applications should always be split into a front-end
(containing the queries, forms, reports, macros and modules) and a
back-end
(containing the tables and relations). Only the back-end should be on the
server: each user should have his/her own copy of the front-end, ideally
on
his/her hard drive.

Correct that problem, and see whether you still have issues.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)






















- Show quoted text -

Hey Doug thanks for the response

The application is split from the data. The application is on a
terminal server. The application does not have any queries or local
tables. All the tables being used or linked to a sql server. All
processing is done with stored procedures on the sql server.
It was necessary to have the application on a terminal server
because we have people using the application at remote sites with poor
bandwidth. Having the application on a terminal server allows all the
processing to be done locally and the only thing transmitted to the
user is the images of the application. This allows the application to
run extremely fast.
Also it nice having the application in one location. We
currently have 82 people using the application so when I make a change
I don't have to worry if all 82 people have the new version. That
could end up being a logistic nightmare.
And the nicest thing about having the application on the terminal
server is I don't have to worry about what version of Access the users
have on their personal computer because I use the the Access
executable from the server.
Anyway, has hoping someone would have some ideas about the original
problem I submitted. Like before any help appreciated. Ray
 
E

eighthman11

You may not like the answer, but what I said is the case.

Users should never share the same front-end. Using Terminal Server does not
change that fact.

To ensure that each user is using the current application, see the free Auto
FE updates Tony Toews has athttp://www.autofeupdater.com/

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)







Hey Doug thanks for the response

The application is split from the data.  The application is on a
terminal server.  The application does not have any queries or local
tables.  All the tables being used or linked to a sql server.  All
processing is done with stored procedures on the sql server.
     It was necessary to have the application on a terminal server
because we have people using the application at remote sites with poor
bandwidth.  Having the application on a terminal server allows all the
processing to be done locally and the only thing transmitted to the
user is the images of the application.  This allows the application to
run extremely fast.
     Also it nice having the application in one location.  We
currently have 82 people using the application so when I make a change
I don't have to worry if all 82 people have the new version.  That
could end up being a logistic nightmare.
    And the nicest thing about having the application on the terminal
server is I don't have to worry about what version of Access the users
have on their personal computer because I use the the Access
executable from the server.
   Anyway, has hoping someone would have some ideas about the original
problem I submitted.  Like before any help appreciated.  Ray- Hide quoted text -

- Show quoted text -

Thanks for the reponse Doug,
Any chance you can go into more details of the pitfalls of having
multiple users sharing the same front end of the application. I was
under the impression when a user signs into the application from the
terminal server they were getting their own instance of the
application.

In four years I have never had a conflict with the application because
of mutiple users accessing the same front end. On our first test
trials we had over 60 people signed in with no issues.

Now I'm a little worried.

Thanks Ray
 
D

Douglas J. Steele

I think Tony has details at his site.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


You may not like the answer, but what I said is the case.

Users should never share the same front-end. Using Terminal Server does
not
change that fact.

To ensure that each user is using the current application, see the free
Auto
FE updates Tony Toews has athttp://www.autofeupdater.com/

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)







Hey Doug thanks for the response

The application is split from the data. The application is on a
terminal server. The application does not have any queries or local
tables. All the tables being used or linked to a sql server. All
processing is done with stored procedures on the sql server.
It was necessary to have the application on a terminal server
because we have people using the application at remote sites with poor
bandwidth. Having the application on a terminal server allows all the
processing to be done locally and the only thing transmitted to the
user is the images of the application. This allows the application to
run extremely fast.
Also it nice having the application in one location. We
currently have 82 people using the application so when I make a change
I don't have to worry if all 82 people have the new version. That
could end up being a logistic nightmare.
And the nicest thing about having the application on the terminal
server is I don't have to worry about what version of Access the users
have on their personal computer because I use the the Access
executable from the server.
Anyway, has hoping someone would have some ideas about the original
problem I submitted. Like before any help appreciated. Ray- Hide quoted
text -

- Show quoted text -

Thanks for the reponse Doug,
Any chance you can go into more details of the pitfalls of having
multiple users sharing the same front end of the application. I was
under the impression when a user signs into the application from the
terminal server they were getting their own instance of the
application.

In four years I have never had a conflict with the application because
of mutiple users accessing the same front end. On our first test
trials we had over 60 people signed in with no issues.

Now I'm a little worried.

Thanks Ray
 
E

eighthman11

I think Tony has details at his site.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)









Thanks for the reponse Doug,
Any chance you can go into more details of the pitfalls of having
multiple users sharing the same front end of the application.  I was
under the impression when a user signs into the application from the
terminal server they were getting their own instance of the
application.

In four years I have never had a conflict with the application because
of mutiple users accessing the same front end.  On our first test
trials we had over 60 people signed in with no issues.

Now I'm a little worried.

Thanks Ray- Hide quoted text -

- Show quoted text -

Thanks, I will check it out.
Ray
 
P

Paul Shapiro

Your first mistake is having a multiuser Access application on a server.

Multiuser Access applications should always be split into a front-end
(containing the queries, forms, reports, macros and modules) and a
back-end
(containing the tables and relations). Only the back-end should be on the
server: each user should have his/her own copy of the front-end, ideally
on
his/her hard drive.

Correct that problem, and see whether you still have issues.

Hey Doug thanks for the response

The application is split from the data. The application is on a
terminal server. The application does not have any queries or local
tables. All the tables being used or linked to a sql server. All
processing is done with stored procedures on the sql server.
It was necessary to have the application on a terminal server
because we have people using the application at remote sites with poor
bandwidth. Having the application on a terminal server allows all the
processing to be done locally and the only thing transmitted to the
user is the images of the application. This allows the application to
run extremely fast.
Also it nice having the application in one location. We
currently have 82 people using the application so when I make a change
I don't have to worry if all 82 people have the new version. That
could end up being a logistic nightmare.
And the nicest thing about having the application on the terminal
server is I don't have to worry about what version of Access the users
have on their personal computer because I use the the Access
executable from the server.
Anyway, has hoping someone would have some ideas about the original
problem I submitted. Like before any help appreciated. Ray
This sounds like a plain permissions problem, but maybe I'm missing
something? Where are you copying the documents on the server? Is there a
shared folder on the server? Is that folder somewhere outside any of the
protect locations, like Program Files? Do users have the necessary
permissions on the folder? Does the user become the file copy owner? Did you
verify that currentUser is returning the name you expect?

How are you doing the file copy?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top