Unable to modify / create SQL tables using Access 2000 (1 Viewer)

[jgrayek]

I'm just getting started using access projects. I am the owner of a Database on a SQL 2000 server. I can connect to the database and view the tables through access. I can even create and drop tables using SQL CREATE TABLE and DROP TABLE through ADO commands. But I cannot edit anything using the Access visual design tools.
Each time I open a table in design mode I get an error message 'You do not have exclusive access to the database at this time. Your design changes will not be saved.'
I went so far as running an ALTER DATABASE command and SET SINGLE_USER option WITH ROLLBACK IMMEDIATE. This had no effect except to prevent any other ADO connections.
I ran sp_who and it seems as though everytime I open the Access Project (I entered the connection information during the project creation to existing database) it sets two logins, one is sleeping and the other running.

Anybody have any idea what is going on? I'd rather not have to write CREATE and ALTER scripts for every object in database. Also at this time I do not have access to SQL Server management tools.

Thanks
Jesse

 

[gjblackford]

Hi,

Are you sure you are using access ADP's and not MDB's as this sounds like the issues you usally get with MDB's?

If you are using ADP's, the only thing I can think of, is what version of SQL Server are you using, if you are using SQL 2005 are there any new drivers available as from prior experience when using Oracle I have found when using wrong version drivers you get some strange errors.

This is only a thought as I am currently using SQL2000.

Also have you tried doing the same thing on a different PC, as the install of office can cause strange errors at times.

 

[gjblackford]

Sorry just noticed that you are using SQL2000.

 

[jgrayek]

I am using ADP's. Also, I did try 2 separate computers with the exact same results. I'm thinking that it is somehow in my connection string. I just don't see how. I have to qualify that this is my first Access project - SQL attempt so I probably just don't know what I'm doing.

My connection string is:

GetConnectionString = "Provider=SQLOLEDB;" _
                    & "Server=SERVER_NAME_HERE;" _
                    & "Initial Catalog=DATABASE_NAME_HERE;" _
                    & "Integrated Security='SSPI'"

The connection works fine. And I can do anything programatically. Just nothing with the visual design tools (Edit Table, New Table, New View, etc.)

 

[jgrayek]

I figured it out. Its not me but incompatible software versions...http://support.microsoft.com/kb/266277/en-us