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

Connecting SQL server 2000 from different domain.

Please help to connect SQL server 2000 on different domain.

Please correct if I m wrong.

System details as follows

Client

Domain : DomainA

System: System1

IP Address : 10.10.225.19

SQL server 2000 installed

SQL Server

Domain : DomainB

System: System2

IP Address : 10.108.22.19

SQl Server : System2\inst2 ( My database is under inst2 instance)

SQL login : TestUser1

Password : Users123

I have Opened firewall port to access 10.108.22.19 from 10.10.225.19

While connecting the SQL server “System2\inst2” from the client machine enterprise manager with the SQL authentication, I am getting error Login failed for user “TestUser1”

And I have noticed in the SQL server log the login attempt shows under System2 default instance ( no instance). Actually I want to connect system2\inst2.

Please let me know the steps to connect.

Thanks in advance.

For named instance, you also need to open UDP port 1434 for firewall on 10.108.22.19(the server). This is requried to find named instances. You said "default instance (no instance)", which errorlog were you checking?|||

Thanks for your reply,

I got solution and i am able to connect all my SQL servers from my domain.

It is a firewall issue , buy default they opened for 1433 it is goes to Default instance , but i need to connect a different SQL server instance.

then i opened firewall for that port it is connecting .

Thanks