I am working on an Access project that is connected to an MSSQL Server 
2000 db. I can use the objects well enough but anytime I try to go into 
design view or create any new tables or views I get the message, "You do 
not have exclusive access to this database". I don't understand. Do I need 
exclusive access just to view designs? It says my changes will not be 
saved but then the view design grid is empty. I know the connection is 
working becuase I tested it okay. I tried it with password security and NT 
integrated security. I know that nobody else is connected to the db. What 
else can I try to do?

What this means is that someone else is using the DB and therefore you will not be able to make any
modifications until you are the 'exclusive' person logged in. There are 2 ways to correct this: 1. Have the person log out and then
open the DB with the exclusive option in the open dialog box. 2. Make what I call a working copy of the DB (separate from the
production DB) and put it somewhere that no one else can access it so you will always have exclusive access for modifications. A
note on option 2 once you have saved your changes and want to update the production DB you will still need everyone to log off
otherwise they will encounter unexpected problems .

Hope this helps....


Kenny

----- Original Message -----
From: Kevin Gooss
Sent: Thursday, December 19, 2002 10:14 PM
To: Access
Subject: [access] Exclusive Access?

I am working on an Access project that is connected to an MSSQL Server  
2000 db. I can use the objects well enough but anytime I try to go into  
design view or create any new tables or views I get the message, "You do  
not have exclusive access to this database". I don't understand. Do I need  
exclusive access just to view designs? It says my changes will not be  
saved but then the view design grid is empty. I know the connection is  
working becuase I tested it okay. I tried it with password security and NT  
integrated security. I know that nobody else is connected to the db. What  
else can I try to do?
to unsubscribe send a blank email to Get more from the Web.  FREE MSN Explorer download : http://explorer.msn.com

This is not possible. I setup the server. Others have access to it but I 
am positive that no one else is using it. I get this message on every 
table in every database on the server. Also I can not create new objects, 
like tables, on the server from within an Access project. I get an error 
message saying I need to update my server software for SQL-DMO or 
something or other. That's why this problem is so confusing, I know nobody 
else is using the database.

Kevin

This sounds like the incompatibility problem between Access 2000 and SQL
Server 2000. To fix, apply Service Pack 2 to your Access installation.

Brian

-----Original Message-----
From: Kevin Gooss [mailto:gooss22@y...] 
Sent: 20 December 2002 22:27
To: Access
Subject: [access] Re: Exclusive Access?

This is not possible. I setup the server. Others have access to it but I

am positive that no one else is using it. I get this message on every 
table in every database on the server. Also I can not create new
objects, 
like tables, on the server from within an Access project. I get an error

message saying I need to update my server software for SQL-DMO or 
something or other. That's why this problem is so confusing, I know
nobody 
else is using the database.

Brian,

Now that sounds like what I need to do. Let me ask you a few questions so 
I can get this done right. I'm in a large company and I know that the IT 
folks aren't just going to let me update anything I want so I have to have 
a good case when I go to them! 
Are you saying I need to upgrade my whole Office Set, or just Access? How 
can I find out if I already have Service Pack 2? When I go to the About 
menu it says I have version 9.0.3821 SR-1? Does that last 1 mean I don't 
have service pack 2? Is this something I could download and do on my own? 
That would be great if I thought that it would work.

Thanks for your help. I knew it had to be something to do with WinDoze!

I did one better and updated to SP-3. It solved the issue! Now I can view and edit designs of forms and 
tables without the annoying error message and the changes actually persist. 
Now if only I could get the Security Tool Wizard to work in an Access project. When I try to fire it up I 
get a message saying that I need to upgrade my SQL Server 2000 to support SQL-DMO. I have no idea 
what that error message is supposed to mean to me.

I had a similar question about ADP security.

My understanding is that I should be able to administer security
on my SQL Server objects from Access as long a I have the necessary
permissions to do so in SQL Server. Apparently selecting Tools ->
Security -> Database Security is supposed to load the SQL Server
Seucrity dialog box. I don't even have the Database Security option on
my menu (Access XP, SQL SERVER 2000 - Personal Edition). It's not even
there. The only enabled option is Set Login Password. The only other
options are Workgroup Administrator and Encrypt/Decrypt Database, both
disabled. If you get SQL Server security working from Access I'd like
to know how you did it.

Bob






>From: "Kevin Gooss" <gooss22@Y...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Exclusive Access?
>Date: Sat, 28 Dec 2002 06:26:29
>
>I did one better and updated to SP-3. It solved the issue! Now I can view 
>and edit designs of forms and
>tables without the annoying error message and the changes actually persist.
>Now if only I could get the Security Tool Wizard to work in an Access 
>project. When I try to fire it up I
>get a message saying that I need to upgrade my SQL Server 2000 to support 
>SQL-DMO. I have no idea
>what that error message is supposed to mean to me.


_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 3 months FREE*. 
http://join.msn.com/?page=features/virus&xAPID=42&PS=47575&PI=7324&DI=7474&SU= 
http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_virusprotection_3mf

I just ran across this:

"In order to open the SQL Server Security dialog, MSDE must be installed
on the computer - even if the Access project connects to a SQL Server
database (whether local or remote)."

Does this mean that SQL Server security can't be administered through
an Access ADP user interface if a version of SQL Server other than
MSDE (now the Desktop Engine edition) is installed? Im using the
Personal Edition.






>From: "Bob Bedell" <bobbedell15@m...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: Exclusive Access?
>Date: Sat, 28 Dec 2002 17:44:16 +0000
>
>I had a similar question about ADP security.
>
>My understanding is that I should be able to administer security
>on my SQL Server objects from Access as long a I have the necessary
>permissions to do so in SQL Server. Apparently selecting Tools ->
>Security -> Database Security is supposed to load the SQL Server
>Seucrity dialog box. I don't even have the Database Security option on
>my menu (Access XP, SQL SERVER 2000 - Personal Edition). It's not even
>there. The only enabled option is Set Login Password. The only other
>options are Workgroup Administrator and Encrypt/Decrypt Database, both
>disabled. If you get SQL Server security working from Access I'd like
>to know how you did it.
>
>Bob
>
>
>
>
>
>
>>From: "Kevin Gooss" <gooss22@Y...>
>>Reply-To: "Access" <access@p...>
>>To: "Access" <access@p...>
>>Subject: [access] Re: Exclusive Access?
>>Date: Sat, 28 Dec 2002 06:26:29
>>
>>I did one better and updated to SP-3. It solved the issue! Now I can view 
>>and edit designs of forms and
>>tables without the annoying error message and the changes actually 
>>persist.
>>Now if only I could get the Security Tool Wizard to work in an Access 
>>project. When I try to fire it up I
>>get a message saying that I need to upgrade my SQL Server 2000 to support 
>>SQL-DMO. I have no idea
>>what that error message is supposed to mean to me.
>
>
>_________________________________________________________________
>MSN 8 helps eliminate e-mail viruses. Get 3 months FREE*. 
>http://join.msn.com/?page=features/virus&xAPID=42&PS=47575&PI=7324&DI=7474&SU= 
>http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_virusprotection_3mf
>
>
>---
>Change your mail options at http://p2p.wrox.com/manager.asp or to 
>unsubscribe send a blank email to 


_________________________________________________________________
MSN 8 with e-mail virus protection service: 3 months FREE*. 
http://join.msn.com/?page=features/virus&xAPID=42&PS=47575&PI=7324&DI=7474&SU= 
http://www.hotmail.msn.com/cgi-bin/getmsg&HL=1216hotmailtaglines_eliminateviruses_3mf

This sorta explains the error message that I am getting when I try to 
access the security wizard from the menu:

[Microsoft][ODBC SQL Server Driver][SQL Server] You must upgrade your SQL 
Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000 (SQLDMO) to 
connect to this server.

But I am running SQL Server 2000 Developer edition. Perhaps I need to 
upgrade.