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
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