How can I open an SQL database and be the only one who has access to it?

Hello,
I'm opening an ADODB connection to an SQL database in VB6 using the
adModeShareExclusive mode for the connection object. The connection opens
successfully, but I'm still able to modify records in the database using
Enterprise Manager. Why is this? I want to open the SQL database and lock
out all other connections, i.e. anyone who tries to connect to it after I
have a successful adModeShareExclusive mode connection established should
get an error when they try to connect. At least that's what I'm trying to
accomplish so that I can do a complete rebuild of the database. I don't
want to kick everyone out of their connections when I try to connect. I'll
just keep trying until I finally get a connection when no one else is
connected. Does anyone out there know how I can do this? Maybe I should
simply be using a different mode, but which one?
Thanks,
William Hildebrand
hi William,
"William Hildebrand" <wahilde@.cableone.net> ha scritto nel messaggio
news:10lr3gghg65f074@.corp.supernews.com
> Hello,
> I'm opening an ADODB connection to an SQL database in VB6 using the
> adModeShareExclusive mode for the connection object. The connection
> opens successfully, but I'm still able to modify records in the
> database using Enterprise Manager. Why is this? I want to open the
> SQL database and lock out all other connections, i.e. anyone who
> tries to connect to it after I have a successful adModeShareExclusive
> mode connection established should get an error when they try to
> connect. At least that's what I'm trying to accomplish so that I can
> do a complete rebuild of the database. I don't want to kick everyone
> out of their connections when I try to connect. I'll just keep
> trying until I finally get a connection when no one else is
> connected. Does anyone out there know how I can do this? Maybe I
> should simply be using a different mode, but which one?
> Thanks,
> William Hildebrand
you can perhaps try another way...
setting the database in single user mode
ALTER DATABASE db_name
SET SINGLE_USER
after you established your ADO connection... eventually trap errors that
could be raised and, before closing your connection, execute
ALTER DATABASE db_name
SET MULTI_USER
please see
http://msdn.microsoft.com/library/de...aa-az_4e5h.asp
for ALTER DATABASE statement's synopsis, and the "termination" options that
could grant you further granularity...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply