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
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