SQL server and xls VBA - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

SQL server and xls VBA

Author

Topic

Gerry.Perry@Avnet.com
Starting Member

6 Posts

Posted - 2004-03-01 : 17:26:43

Hello all
I use VBA in excel with an ODBC connection but ask for help in an area of the SQL server ID
Consider 2 id profile as
Id 'IOD'
Server Roles : none
Access to database : EM2
Permit in Database Role : public, db_datareader, db_datawriter

Id 'bizinfo'
Server Roles : none
Access to database : EM2

I want the tool to add and delete records and one area of code is
strSQL = "Delete from Susi_search " & whereclause
rst.Open source:=strSQL, ActiveConnection:=cnn, CursorType:=adOpenKeyset, LockType:=adLockOptimistic

where cnn = Provider=SQLOLEDB.1;Password=xxxx;User ID=IOD;Initial Catalog=EM2;Data Source=ip;Network Library=dbmssocn;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=xxx;Use Encryption for Data=False;Tag with column collation when possible=False.

obviously I've changed the IP and password.
I get an error of "Method of open Object Recordset failed.
the same code WILL work for the more powerful "bizinfo" ID.

I think both should work but .....

Ideas why both wont ??

Gerry

Permit in Database Role : public, db_owner

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-01 : 17:39:22

So what permissions does bizinfo have? In the thread, it appears as if none. Does that account have sysadmin or something? Do you know that the other user is failing on that DELETE statement?

You can run SQL Profiler to determine if a permission problem is occurring. You would want to add the success column and the Errors and warning events.

Tara

Gerry.Perry@Avnet.com
Starting Member

6 Posts

Posted - 2004-03-01 : 17:48:39

Sorry:- bizinfo has Permit in Database Role : public, db_owner
I shoudl have said that on SQL server directly I can do want I need: run deletes etc. So its something on the VB side. Perhpas the
CursorType:=adOpenKeyset, LockType:=adLockOptimistic

quote:
Originally posted by tduggan

So what permissions does bizinfo have? In the thread, it appears as if none. Does that account have sysadmin or something? Do you know that the other user is failing on that DELETE statement?

You can run SQL Profiler to determine if a permission problem is occurring. You would want to add the success column and the Errors and warning events.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-03-01 : 17:54:19

I don't know what permissions are needed for those. Could you explain what those two options do? With the explanation, I can probably tell you what is needed for permissions.

Tara

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-04 : 13:46:27

Rather than using a Recordset object to issue a Delete statement, I usually just use the .Execute method of my Connection object. You don't need to worry about the cursor type or lock type that way.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources