[Stanley]

I have a report form in a Word document. For the report, I need to
connect to a MSSQL Server to get some personal information and put
them into the report, such as Name, and ID no. I want to use ODBC to
connect to the database and get the information I need.

Right now, I have no idea how to write the code in VBA in the
document.
Can anyone help?

Btw, I'm new to VB and VBA.

Stanley

[Stanley]

I'm using Word 2000 and MS SQL Server 2000.
I have already setup a System DSN in ODBC.
But I don't know what should I do after I setup a connection and where
should I write those code?

Stanley

[Peter Jamieson]

But I don't know what should I do after I setup a connection and where
should I write those code?

You should be able to set up a connection manually, but since you cannot
select your System DSN in the Open Data Source dialog box, you have to click
the MS Query button in that dialog and follow the dialog boxes in there. It
can be tricky. Unfortunately, you can also find that MS Query will return
the data to Word, but that Word then tries to connect again and fails. if
that happens, it is probably either because
a. Word truncates the ODBC connection string that MS Query sends back to
it, making it an invalid string or
b. The login security information is not getting through.

To connect programmatically, this is roughly what you need in VBA

Sub ConnectToSQLServer()

' if necessary, disconnect from the existing source
' (you probably do not need this with SQL Server)
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

' if necessary, set up the merge type abnd/or destination that you want
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.Destination = wdSendToNewDocument

' change your_dsn_name to your SQL server DSN name and
' write the query you need
' ActiveDocument.MailMerge.OpenDataSource _
' Name:="", _
' Connection:="your_dsn_name;Trusted_Connection=Ye s", _
' SQLStatement:="SELECT * FROM Northwind.dbo.Categories Categories", _
' SQLStatement1:=""

' e.g.
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=myserver1;Trusted_Connection=Yes" , _
SQLStatement:="SELECT * FROM Northwind.dbo.Categories Categories", _
SQLStatement1:=""
End Sub

You probably also need to make the registry change described in the
following article:

http://support.microsoft.com/kb/825765/en-us

If you are unfamiliar with VBA, you can find out what to do with this macro
at

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

You may also find the following useful:

http://word.mvps.org/FAQs/MacrosVBA/DocumentEvents.htm

However, the main problem is likely to be "getting your connection string
rand query ight".
Generally speaking your DSN will contain everything you need to get
connected, except security information. If you are using builtin Windows
security, "Trusted_Connection=Yes" should be enough (you may not even need
that. If you are using SQL Security, you cannot store the login/password in
your DSN and you have to put the following in your Connection string, using
your login and password of course:

UID=mylogin;PWD=mypassword;Trusted_Connection=No;

You may find you also need network infromation in the connection string. The
following is fairly typical for a TCP/IP connection:

Network=DBMSSOCN;Address=the_name_of_your_server_m achine,1433;

Also, you can specify a database in the connection string using e.g.

database=Northwind;

Then you may only have to use

SQLStatement:="SELECT * FROM Categories"

and so on.

If you have a long SQL query, construct it using both SQLStatement and
SQLStatement1 so that when the two parts are put together, they form the
exact string you need, e.g.

SQLStatement:="SEL"
SQLStatement1:="ECT * FROM Categories"

not

SQLStatement:="SEL "
SQLStatement1:="ECT * FROM Categories"

If you are trying to connect to a stored procedure, you will need more.

See how you get on with that lot.
Peter Jamieson

"Stanley" wrote in message
ups.com...
I'm using Word 2000 and MS SQL Server 2000.
I have already setup a System DSN in ODBC.
But I don't know what should I do after I setup a connection and where
should I write those code?

Stanley

[Stanley]

Thanks for your information. I'll try it and see if I can get through
it.

On Jan 31, 7:20 pm, "Peter Jamieson"
wrote:
But I don't know what should I do after I setup a connection and where
should I write those code?

You should be able to set up a connection manually, but since you cannot
select your System DSN in the Open Data Source dialog box, you have to click
the MS Query button in that dialog and follow the dialog boxes in there. It
can be tricky. Unfortunately, you can also find that MS Query will return
the data to Word, but that Word then tries to connect again and fails. if
that happens, it is probably either because
a. Word truncates the ODBC connection string that MS Query sends back to
it, making it an invalid string or
b. The login security information is not getting through.

To connect programmatically, this is roughly what you need in VBA

Sub ConnectToSQLServer()

' if necessary, disconnect from the existing source
' (you probably do not need this with SQL Server)
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

' if necessary, set up the merge type abnd/or destination that you want
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.Destination = wdSendToNewDocument

' change your_dsn_name to your SQL server DSN name and
' write the query you need
' ActiveDocument.MailMerge.OpenDataSource _
' Name:="", _
' Connection:="your_dsn_name;Trusted_Connection=Ye s", _
' SQLStatement:="SELECT * FROM Northwind.dbo.Categories Categories", _
' SQLStatement1:=""

' e.g.
ActiveDocument.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=myserver1;Trusted_Connection=Yes" , _
SQLStatement:="SELECT * FROM Northwind.dbo.Categories Categories", _
SQLStatement1:=""
End Sub

You probably also need to make the registry change described in the
following article:

http://support.microsoft.com/kb/825765/en-us

If you are unfamiliar with VBA, you can find out what to do with this macro
at

http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

You may also find the following useful:

http://word.mvps.org/FAQs/MacrosVBA/DocumentEvents.htm

However, the main problem is likely to be "getting your connection string
rand query ight".
Generally speaking your DSN will contain everything you need to get
connected, except security information. If you are using builtin Windows
security, "Trusted_Connection=Yes" should be enough (you may not even need
that. If you are using SQL Security, you cannot store the login/password in
your DSN and you have to put the following in your Connection string, using
your login and password of course:

UID=mylogin;PWD=mypassword;Trusted_Connection=No;

You may find you also need network infromation in the connection string. The
following is fairly typical for a TCP/IP connection:

Network=DBMSSOCN;Address=the_name_of_your_server_m achine,1433;

Also, you can specify a database in the connection string using e.g.

database=Northwind;

Then you may only have to use

SQLStatement:="SELECT * FROM Categories"

and so on.

If you have a long SQL query, construct it using both SQLStatement and
SQLStatement1 so that when the two parts are put together, they form the
exact string you need, e.g.

SQLStatement:="SEL"
SQLStatement1:="ECT * FROM Categories"

not

SQLStatement:="SEL "
SQLStatement1:="ECT * FROM Categories"

If you are trying to connect to a stored procedure, you will need more.

See how you get on with that lot.
Peter Jamieson

"Stanley" wrote in message

ups.com...



I'm using Word 2000 and MS SQL Server 2000.
I have already setup a System DSN in ODBC.
But I don't know what should I do after I setup a connection and where
should I write those code?

Stanley- Hide quoted text -

- Show quoted text -