Getting data into Excel thru ACCESS security...

[Guest]

I've created a company specific Workgroup with ACCESS 2000 that I use to
implement user level security for all databases in my company. I've taken
the tact of creating shortcuts for each database that includes the path to
the workgroup I created. I did this so that I didn't permanently change the
workgroup on any computer and could do it as necessary.

Now I want to use the MSQuery function in Excel to extract data from one of
the specific databases to create charts and calculate statistics. How do I
specify the workgroup, userID, and password in MSQuery. I get a dialogue box
and enter valid user ID and passwords but I get an error message...

You do not have the necessary permissions to use the database object. Have
your system administrator or the person who created this object establish the
appropriate permissions for you.

I assume I'm getting this because the default workgroup is the supplied -
system.mdw - file and not the one I created for my company.

Is it possible to pass this information through MSQuery?

Thanks

Jeff Harbin

 

['69 Camaro]

Hi, Jeff.

Is it possible to pass this information through MSQuery?

I strongly suspect that you will need to do some VBA programming within
Excel to open the secure database before you can create Recordsets from the
data in the secure tables. I suggest that you post a question in the Excel
News Group for the code needed to open a secure Access database and
manipulate the data without saving that data to an Excel spreadsheet. (You
_do_ want to keep the data in the secure database, don't you? If not, then
joining the secure workgroup before attempting to open the database from
Excel would probably (I've never tried this, though) allow one to log in
with the appropriate User ID and password when prompted, and then save
copies of that data in unsecured files.)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

 

[Jamie Collins]

I want to use the MSQuery function in Excel to extract data from one of
the specific databases to create charts and calculate statistics. How do I
specify the workgroup, userID, and password in MSQuery.

You can do this in advance by creating a DSN (I assume this will
appeal to you if you like shortcuts on desktops <g>). Control Panel,
Administrative tools, Data Sources (ODBC), User DSN, choose MS Access
Database, hit configure. On my machine, on the resulting dialog there
is an Advanced button that brings up a further dialog with Lgin name
and Password fields.

Once created, the DSN should be available on the list of 'Databases'
in Excel's Choose Data Source dialog when you create a new database
query (MS Query).

Jamie.

--