Class does not support Automation or does not support expected interface

I have a dB in SQL Server with Access 2000 front end. All is well, until I recently added some more users in SQL Server. Now those new users get this error when the application starts, specifically at a line that reads "Set cnnConnection = CurrentProject.Connection". Now this code works fine for everyone else, and it is not all new users that get the problem. But if they have the problem, no amount of removing or recreating the SQL Server user makes any difference.

One difference is that the users affected are in a new AD child domain from previous users (although not all users in the new child domain exhibit teh fault).

Everyone runs the application via a single Terminal Server machine, so it cannot be a problem of different s/w as they are all running the same thing.

Anyone have any ideas?

Thanks. Dr_Dudd.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I would be inclined to think that the issue isn't the SQL Server user as such, its more likely that windows users rights on the terminal server are different. Are you absolutely sure that all the windows users have the same rights on the terminal server?

What happens if you run it under an administrator user?

Is it possiblt that the user profile is slightly different on the terminal server?

If you open the ADP (I assume), go to the VBA editor and press F2, can you find all the libraries that you expect to see there?
dr_duddAuthor Commented:
Authentication is by AD username. I cannot find any differences in the user details between those that can use the application through TS and those that cannot. Also I have managed to log in locally to a PC here as one of the problem users and run the application locally - it has the same problem.

I am fairly certain that this is a rights issue to do with the AD user or SQL Server roles, but I can't find any differences between those that work and those that don't.
'Class does not support Automation or does not support expected interface'

It sounds like an application issue, I really don't think it has anything to do with the SQL Server role.

In fact since it hasn't got past the  Set cnnConnection = CurrentProject.Connection  line, it hasn't even connected to the database yet.

So on the non functional users local PC, after you hit debug, then hit stop, then press F2, does it brnig an object viewer?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

dr_duddAuthor Commented:
You are on to something there. As you say it is not connecting to the SQL Server database, it just happens that the 'Set cnnConnection = CurrentProject.Connection' is the first attempt by the app to use the dB. If I go to 'Connection' and click 'Test Connection' it returns 'Test connection failed because of an error initialising provider. Login failed for user DOMAIN\User'.

It looks like the user is not granted dB access. However, in SQL Server the user has 'Database Access' permission and all the same group memberships as other users that work ok.
error initialising provider... hmmmm

try this:

1. Create a new text file
2. Rename the extension to .UDL
3. Double click it and fill in the connecton parameters
4. Hit Test

Try it for the functional and non functional users.

If you see the same behaviour then that narrows it down to the ole-db provider (ie drivers)
and if thats the issue you need to register some DLL's in these folders:

C:\Program Files\Common Files\System\Ole DB

try registering these files and see if it fixes it:


if that doesn't work then try registring this file:

C:\Program Files\Common Files\System\ado\msado15.dll
PS regsvr32 is what you want to register files
dr_duddAuthor Commented:
The plot thickens...

If I log in to the PC as user domain\nogood (the user that does not work) and create the UDL file, set the provider as SQL Server and fill in the details when I get to select the database on the server it says "Invalid login for domain\nogood" then a second error shows "Unable to retrieve catalog". So I can't get a connection to the SQL Server to get a list of dB's on the server.

If I log in to the PC as user domain\good (a user that works) then I can fill in all the connection details, select the dB and test it and it works fine.
Do you have access to SQL Server Query Analyzer?

Cause the next thing I would do is try logging in as domain\nogood through Query Analyzer.

FYI, all that stuff I just posted prior to this does not apply as you didn't get a provider error.

Its possible that the nogoods (perhaps that name will stick with the people in real life ;) ) are members of windows groups that are denied access to SQL Server, so somehow it has inherited a 'deny' through windows groups?

But you're absolutely certain that the windows groups for these people match (as far as windows group membership) as well as their SQL Server memberships?

PS its often easier to add all the windows users to a single windows group then just add that group to the server.... this may complicate or simplify the situation for you, not sure.

Gotta crash, I'm in Australia, good luck, maybe you'll sort it by tommorow!
dr_duddAuthor Commented:
I can't log in to Query Analyser as the user as the only option are for NT or SQL Server authentication. I cannot log in to the Windows 2003 Server as users are not allowed to log in directly.
To log in as domain\nogood in query analyzer, you need to log into the workstation as domain\nogood, then log into QA using NT authentication. Maybe thats not practical though.

I just need to do this to work out whether its an OLE-DB driver installation issue or whether its a SQL Server user access issue.

If you can't do that, then we can assume its a user access issue.

Try temporarily adding the login to the System Administrators Role as well as the particular databases db_owner role.

If that fixes the issue then we know it is a database user issue, not a client driver issue.

Don't forget to take them back out.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dr_duddAuthor Commented:
Sorry for the long delay in a response.

OK, I managed to install SQL Server tools on my PC and log in as the domain\nogood user. When I run QA and try to connect to the SQL Server using NT authentication I get this error....

Server: Msg 18456, level 16, state 1
[Microsoft][ODBC SQL Server driver][SQL Server] login failed for use 'domain\nogood'

I can't see any difference in access rights between a user that exhibits this problem, from a user that works fine. I have logged in to both on my PC, so it can't be a driver version issue.
OK now its down to stabs in the dark

Rereading your original post, it seems the only difference could possbily be in the windows groups side of things?

>> One difference is that the users affected are in a new AD child domain from previous users (although not all users in the new child domain exhibit teh fault).

when you say access rights (in your previous post) does that include windows groups?

dr_duddAuthor Commented:
The problem has now spread to new users in another child domain. Until Tuesday this week all users in the domain worked fine, but I have added two new ones and neither work! I have created a new user in the original problem child domain and it works fine. If I delete the domain user from SQL Server and re-create it it still does not work. I can't delete a domain user from AD as these are real users.

The annoying thing is that I can't seem to create a test user that doesn't work so I can't test things other than on a real user's account.

I could not find any differences in the permissions or group membership between users that work, and those that don't.

There is a reference on the MS websit about a bug that this error happens if there is a semi-colon in the user's password. Of course there are not in my case.
dr_duddAuthor Commented:
I managed to get some of the users working. I have been using a VBA program that calls the system stored procedures to grant dB access, and make them members of the roles I tell it. So I deleted the users that did not work and re-created them using the following from QA...

use mydB
SET @AFMUser = 'gatwick\simon'
EXEC sp_grantlogin @AFMUser
EXEC sp_grantdbaccess @AFMUser
EXEC sp_addrolemember @rolename='AFM_Operations', @membername=@AFMUser

Changing the SET line for each user. Now some can log in.

BUT: When I create the users this way and then view users in SQL Server EM they all have the correct 'Name', such as 'Gatwick\Simon' but some of them have no 'Login Name' and others have a 'Login Name' the same as their 'Name'.
dr_duddAuthor Commented:
I have accepted this answer as it correctly lead me to answer for the original question as to why I was getting the error 'Class does not support Automation or does not support expected interface' - and the reason is that the user does not have login rights to the database.

Thanks nmcdermaid for your help.

I now have to work out why the user doesn't have access to the dB but that is a different question altogether...
Glad to help, I think it was getting to the stage where I couldn't help via newsgroup anymore. Good luck finding an answer.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.