Using a Macro in MS Word 2003, I need to call a stored procedure on an SQL
server, with the requirement that the host system does not have a DSN entry
for the server (externally applied constraint).

I have server name, database name, user id, password, etc.

The documentation I have found seems to indicate I need to use a Workspace
object to create a Connection object.

The example below (using DSN) works fine:

   Dim odbc As Workspace
   Dim sql As Connection
   Dim connectStr As String
   
   connectStr = "ODBC;" + _
                "DATABASE=aDataBase;" + _
                "UID=aUser;" + _
                "PWD=aPassword;" + _
                "DSN=TestSql;" + _
                "LOGINTIMEOUT=5;"

   Set odbc = CreateWorkspace("Test Sql", "aUser", "aPassword", dbUseODBC)
   Set sql = odbc.OpenConnection("TestSql", dbDriverCompleteRequired, True, _
                                   connectStr)

' Do work

 sql.Close
 odbc.Close

However, as stated, I have the constraint of not being able to use a DSN. I
have so far not been able to construct a connection string that results in a
successful connection based on server name, etc. instead of DSN.

One connect string of many that I have tried, which does not work is:

   connectStr = "ODBC;" + _
                "Provider=SQL;" + _
                "DATABASE=aDataBase;" + _
                "Server=aServerName;" + _
                "UID=aUser;" + _
                "PWD=aPassword;" + _
                "LOGINTIMEOUT=5;"

The error I get is "ODBC Call Failed", which doesn't tell me much.

Questions:
1) Is this even possible in a Word Macro?
2) If it is possible, are the Workspace / Connection objects the correct
objects to use?
3) Am I just using the wrong connect string contents?
4) ???

Any input will be very appreciated. I am a VERY new VBA user.

Regards,
Jason