Using VBA to hit 2008 (Access works, Excel fails)

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

SQL Server 2008 Forums

SQL Server Administration (2008)

Using VBA to hit 2008 (Access works, Excel fails)

Author

Topic

TMR
Starting Member

3 Posts

Posted - 2009-03-30 : 13:31:23

So I have a SQL Server 2008 database up and running, complete with SQL Server logins mapping to db logins mapping to roles, and all the permissions set accordingly. I've set things up to use Windows authentication only. I have an Access 07 front-end which hits the database just fine and with which I can select, update, etc. the appropriate tables. I configure Access using the 'Office' button, then the 'Server/Connection...' tab, set the server name, choose 'Use Windows NT integrated security" and select the appropriate database. Works fine.

However, if I try to use Excel 2007 and VBA/ADO to hit the same tables, using the same PC, the same Windows login, etc. things fail with a "-2147467259 Invalid authorization specification".

The ADO connection string is:
Provider=sqloledb;Server=XXXX;DataBase=YYYY;

The VBA Tools/References are current, i.e.:
Visual Basic For Applications
Microsoft Excel 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Object Library
Microsoft Forms 2.0 Object Library
Microsoft ADO Ext. 2.8 for DDL and Security
Microsoft ActiveX Data Objects 2.5 Library

So what am I missing here? Why can I get to it through Access but not Excel?

= Marchand =

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-30 : 13:59:50

Make sure to add this to your connection string: Integrated Security=SSPI;

In case you still have trouble, try using ADO 2.8 for your Recordset object.

TMR
Starting Member

3 Posts

Posted - 2009-03-30 : 14:06:47

robvolk,

Thanks for the quick reply! The first part is clear; but when you say "try using ADO 2.8" I'm confused. I thought the Tools/References was already pointing to/using ADO 2.8 (MDAC 2.8 has been installed). Is there something beyond that that is needed?

= M =

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-03-30 : 14:07:49

You posted "Microsoft ActiveX Data Objects 2.5 Library", unless that's a typo, just replace it with the 2.8 library.

TMR
Starting Member

3 Posts

Posted - 2009-03-30 : 14:10:24

Loud "Duh!" sound here. As they say, the subtle I get immediately, the obvious takes a little longer.

= M =

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources