p2p.wrox.com Forums

Need to download code?

View our list of code downloads.

  Return to Index  

access thread: RE: OBDC security

Message #1 by "Beverly Usher" <bUsher@h...> on Thu, 14 Jun 2001 09:27:41 +0100
I need to be able to write reports in Access that look at a SQLServer 7.0 d

atabase. I will attach these reports to buttons in a program which we use t

o input and manage the data. I need to set up an OBDC connection to allow u

sers to view the data in these reports. I have currently set up some system

 wide OBDC connections which ask for a password, but really would like the 

reports to run transparently. I have also set up an OBDC connection which u

ses NT authentication to look at some training data in the db, which does n

ot require a password from the user. However, anyone who can log on to our 

system could then look at this data if they set up a blank Access file and 

linked to it.

Is there a solution to this? I have considered this type of connection (no 

password required) as a user DSN but would rather not have to run around se

tting up all those local DSNs. Can anyone shed light on OBDC/DSN security a

nd what exactly it looks at when using NT security? How can I link specific

 logins without having to ask people for their login password?

Sorry this is so long, but I can't think of any other way to explain it.

Thanks for any thoughts, Beverly

Message #2 by "Darron Michael" <darron.michael@h...> on Fri, 15 Jun 2001 15:04:02

I have an Access97 database on our server that is shared by a lot of 

users.  I have a table in that database that has the network login_names 

of the users, their access rights within the database, and their current 

status (in or out of the database).

I have a line in the network login script that captures their login_name 

and machine number and stores them in an environment variable.

When a user opens the database, an autoexec macro opens the "Main Menu" 

form.  Whenever this form is activated, it uses a function to read their 

login_name and machine number from their environment variables, and then 

compares this to the table of users to find their level, and enables or 

disables objects (primarily buttons) on the menu based on their level.

You can program it to react to new / unknown users in one of two ways.  It 

can eject them and close the database or it can create them an account and 

grant them "Guest" access.

I've only recently started working with my new SQL2000 server and I don't 

yet know exactly how I'm going to implement my multiple Access97 DBs into 


I'll send you the functions for this type of "Security" if you're 

interested.  It would let you handle the security issues within Access by 

keeping them captive in forms, but you would still have the same problem 

of having to have an ODBC connection to SQL set up on EACH machine that 

will run this database.  The only solution I know how to implement would 

involve moving the data back out of SQL and into Access.  Then you could 

mirror the data (once an hour?) up to the SQL server using an automated 

import function in SQL's Data Transformation Services.

Email me at darron.michael@h... if you're interested in this 

type of solution.


Message #3 by "Beverly Usher" <bUsher@h...> on Fri, 15 Jun 2001 15:37:54 +0100
Thanks for the reply. I'll take it home to have a serious look at this week

end. I already have a method for capturing logins in an existing system whi

ch I may be able to use, but will take advantage of your kind offer if I ne

ed further help.

Thanks for the ideas!!!


  Return to Index