DSN-less SQL connection from Word Macro

J

Jason Reichenbach

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
 
J

Jason Reichenbach

Jason Reichenbach said:
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

Answers:
1) Yes
2) Yes
3) Yes. Connect string should look something like:

connectStr = "ODBC;DRIVER={SQL Server};" + _
"SERVER=aServerName;" + _
"DATABASE=aDatabase;" + _
"UID=aUser;" + _
"PWD=aPassword;" + _
"LOGINTIMEOUT=5;"

4) The Name parameter in the OpenConnection call must be an empty string.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top