Using W2K Pro, W2K Server Small Business, SQL Server 2K, Outlook 2002

Would like to connect to the SQL Server database from a Outlook macro
using VBA. My own research indicated using SQLOLEDB as provider would
be preferred method. However, I can't get it to work. The error says
provider not installed (but it is). I can get an ODBC-based connection
to work. I suspect Outlook VBA doesn't support SQLOLEDB provider, but
I can't find any documentation that says as much directly. So, the
question is.... Does Outlook VBA support using SQLOLEDB as Provider or
do I stay with ODBC connection? Or, is there a better way? Thanks.

Hi, 

Looks like this descibes your answer:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q216519

Apparently it doesn't work "by design", there are some suggestions
there to help work around the "design".

If you are going to be doing a lot of this type of programming I can
suggest a book for you :

"Outlook 2000 VBA Programmer's Reference," by Dwayne Gifford is a
short introduction to VBA itself, and the rest of the book documents
aspects of programming Outlook through that object model. This book is
arranged in three broad sections: the first part introduces Outlook
and VBA, the second offers interesting, thematic discussions of the
workings of the Outlook 2000. The third and final part offers a full
reference to the Outlook object model.

http://search.barnesandnoble.com/textbooks/booksearch/isbnInquiry.asp?userid=4XW6YIZWUQ&sourceid=00399098076431662994&bfdate=01%2D02%2D2003+16%3A42%3A32&isbn=186100253X&TXT=Y&itm=1

---

Request for Answer Clarification by drgus87-ga on 02 Jan 2003 14:00 PST

Thanks for the info so far.

I was asking more specifically about the Outlook VBA ---> SQLOLEDB
connection rather than the SQLOLEDB ---> SQL Server 2000 (SQL Server
8.0) connection, the latter being what the article speaks about. Any
further info on Outlook VBA / SQLOLEDB connection?

Thanks again.

---

Clarification of Answer by webadept-ga on 02 Jan 2003 14:31 PST

Yes, I'll find some for you and post it here. Thanks for the clarification. 

webadept-ga

---

Clarification of Answer by webadept-ga on 02 Jan 2003 14:43 PST

I'm still looking around for good examples of how to do this, but this
one looked so much like what you are trying to do I decided to see if
this works for you while I'm looking. If this doesn't work, then I
need to find out why, so please send me whatever error codes pop up.

http://www.databasejournal.com/features/mssql/article.php/1441931

Thanks, 

webadept-ga

---

Clarification of Answer by webadept-ga on 02 Jan 2003 14:48 PST

Other than that first article I found for you everything is suggesting
that it should be possible and should work. The following is an
example of how to open a connection to the Microsoft SQL Server
Northwind database on my server named Batman.
 
  Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

With conn
    .Provider = "SQLOLEDB"
    .ConnectionString = _
        "data source=Batman;user id=sa;initial catalog=Northwind"
    .Open
End With

When using the SQL Server OLE DB provider, the data source must equal
the computer name and the initial catalog must equal the name of the
database that you want to access.

http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/accessdev/html/ODC_MicrosoftOfficeDeveloperForumAccessAdvancedADOfortheVBAProgramme.asp

If this doesn't help solve your problem please let me know. 

thanks, 

webadept-ga

---

Request for Answer Clarification by drgus87-ga on 02 Jan 2003 15:18 PST

Thanks, I will work with what you have given me. The additional
example is running under Windows Script Host, not Outlook VBA, but
using that code should test the possibility that my SQLOLEDB provider
is _not_ properly installed, at which point my question will be
different, but I will post that one separately to be fair, of course.
You are finding what I found, that it is that it should work, but...

I will play with code you have given and me and see what happens.

Thanks.

---

Clarification of Answer by webadept-ga on 02 Jan 2003 15:40 PST

Okay, well if it doesn't work at all, try to give me some error codes.
I tried the VBA just now on my system running MS SQL and got the
connection following those instructions (basic server name changes of
course). So if it doesn't work post back here first before opening
another question and I'll see if I can't help further.

Thanks, 

webadept-ga

---

Request for Answer Clarification by drgus87-ga on 02 Jan 2003 16:15 PST

It works!

Using the Win Script Host code you suggested proved everything was
installed an connected.

In the Outlook VBA module, I changed the ConnectionString to some
different settings, SERVER= instead of Data Source= and used
Integrated Security='SSPI' instead of UID/PWD and it worked!

Thanks for everything, especially your patience (I know just enough
about programming to be dangerous!)

---

Clarification of Answer by webadept-ga on 02 Jan 2003 16:51 PST

Very cool, glad I could help. 

Until next time :-)

webadept-ga

I got some information I needed. We are still attacking the problem,
but the content and effort to this point was well worth the fee.