Connecting to MSSQL in VBA?

This is kind of the wrong place to ask VBA questions, but I've been poking around for a day and haven't got the answer, so I am hoping I can get some help here.

I am wondering if I can connect to MSSQL in MS Word Macro. If so, can I have some sample code please?

Thanks

here you go..look VBA section

http://www.programmingmsaccess.com/Samples/VBAProcs/vba_code_sample_menu.htm#VBA_Samples

|||

That's a good VB resource, however, I didn't exactly find what I was looking for.

In Excel, I can open a direct connection to a sql server like this:

ActiveSheet.QueryTables.Add( Connection:="ODBC;DRIVER=SQL Server;SERVER=myserver;UID=myid;PWD=mypass;DATABASE=mydb", _
Destination:=Range("a1"), _
SQL:="mySP")

Theoretically, I can contain a spreadsheet (as intermedia database) in Word, but before heading that direction, I want to try to get a cleaner solution first.

From the code samples I've come across, appearently ADODB or ADO, or even ODBC can only point to database file like .mdb, but not MSSQL server.

I kind of ran out of keywords for Google, so I am hoping I can get some pointers here.

|||

The first thing is Excel since XP can connect to SQL Server and write queries the syntax is Access SQL so SQL Server even version 2000 rejects some queries, the second thing is there is a team writing the type of code you need that is the Visual Studio Office Tools team. I have found two links to get you started. Hope this helps.

http://msdn2.microsoft.com/en-us/library/aa168463(office.11).aspx

http://msdn2.microsoft.com/en-us/office/aa905533.aspx