By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,967 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,967 IT Pros & Developers. It's quick & easy.

Select Permission Error

P: 50
I am trying to connect from an Access 20000 database to a SQL Server 2000 table through VBA.

I am trying to do a simple select: SELECT COUNT(*) FROM Contracts.
The login I am using has every right imaginable (db owner,admin, etc.). Plus on the table in question, it has Select, insert, delete, update and dri (whatever that is).

Whenever I try to run the query, I get "Select permission denied on object contracts" Error number -2147217911

Here is the calling routine:
Expand|Select|Wrap|Line Numbers
  1.      s_SQL ="SELECT (COUNT(*) FROM Contracts"
  2.     Set adoConnection = New ADODB.Connection
  3.     adoConnection.CommandTimeout = 30
  4.     adoConnection.Open gs_ConnectionString
  5.  
  6.     Set rs = New ADODB.Recordset
  7.     Set rs = adoConnection.Execute(s_SQL)
  8.     Set rs = New ADODB.Recordset
  9.     rs.Open s_SQL, adoConnection, adOpenStatic, adLockReadOnly
  10.  
Here is the connection string:
Public Const gs_ConnectionString = "Provider=sqloledb;Data Source=[ip address],1433;" & _
"Network Library=DBMSSOCN;Initial Catalog=MyDatabase;" & _
"User ID=MyLogin;Password=MyPassword;"

Since the login has more than enough rights to do a select on the table, I suspect that this one of those errors that is really about something else.

Any ideas that I can try?
Aug 19 '08 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,709
Line #6 shouldn't be followed by line #7.

Check out the return value of the .Execute.

Which line in your code is highlighted when the error occurs?
Aug 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.