Access security and excel external data

[tmountjr]

I have a database secured with our own .mdw file on the server. I also
have an excel file that needs to access that db for a specific type of
report generated in Excel. The Admin user and Admins group have been
stripped of all access rights on the db for security reasons, but
that's the default user that Excel tries to log in as when it does its
external data retrieval. I found an article that looked promising (it
involved creating a separate workspace programatically and accessing
the db from within that workspace), but the code broke on the Set ws =
CreateWorkspace line with the error "Can't start your application. The
workgroup information file is missing or opened exclusively by another
user."

Ideally, I'd like to supply Excel with a username and password, then
set up that user on the .mdw side so that it has access rights only to
the necessary queries. Can I do this?

 

[onedaywhen]

Ideally, I'd like to supply Excel with a username and password, then
set up that user on the .mdw side so that it has access rights only to
the necessary queries. Can I do this?

CreateWorkspace is DAO. ADO is more popular in these groups. For sample
ADO connection strings using the OLE DB provider for Jet, see:

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForMicrosoftJet

AFAIK as well as granting permissions to the query you have to grant
permissions to the underlying tables, queries, etc the query uses.
Jamie.

--