Word Help

[agent0047]

The code below is the path we use on the sql server but here is the thing I am trying to figure out how to code this in a ms word doc so that when I open the word doc it will populate the document when it opens with name, address... based on the above criteria?

set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "Provider=SQLNCLI;Server=SERVER\SQLEXPRESS;Database=GE_Data;Trusted_Connection=yes;"
Conn.Open

sql = "SELECT * FROM [DB] WHERE [DB].[DBID]=" & session("dbid")
rs.open sql, conn, 3, 3

 

[SkipVought]

Have you tried any code in Word VBA?

First you'll need to set a reference for Microsoft ActiveX Data Objects n.m Library via Tools > References.

"...populate the document..." What Objects are you populating?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[agent0047]

It will be populating say the name, address, city... based on an id.

 

[SkipVought]

But WHAT OBJECTS are you updating? A contol? A Bookmark? A table?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[MakeItSo]

Just that? Just populate the document with name, address... ?
You might not need any vba code at all.
In Word, click F1, search for "mail merge".

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[agent0047]

It is a table, and I don't want to use mail merge because I don't want it to pull but one record based on and ID.

 

[PHV]

You may filter the data source of a mail merge ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[agent0047]

Thanks PHV isn't there a way to do this by puting the connection string say of SERVER\SQLEXPRESS server and asking it to connect to say TABLE1 and think put the criteria in the string? Much like the code works with asp?

 

[SkipVought]

Sure, did you try it?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[agent0047]

I have tried skip but I can't get it to work, and I can't figure out how to call the field in word have you get the connection. Now I ma trying to do this without mail merge.

Private Sub OnOpen()
set rs = Server.CreateObject ("ADODB.Recordset")
Set conn = Server.CreateObject("ADODB.Connection")
Conn.ConnectionString = "Provider=SQLNCLI;Server=SERVER\SQLEXPRESS;Database=GE_Data;Trusted_Connection=yes;"
Conn.Open

sql = "SELECT * FROM [DB] WHERE [DB].[DBID]=" & session("dbid")
rs.open sql, conn, 3, 3
End Sub

 

[PHV]

There is no Server object in the word object model.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MakeItSo]

Didn't see this at first: it's VBScript, not VBA.
VBA does not contain session cookies either!
You cannot open your Word doc and have a variable value right away - except if you call a word & document object from some other application and pass a parametre.

So, in order to turn this into VBA, do these steps:

1. Set a reference to Microsoft Activex Data Objects, as PHV mentioned

2. dimension your variables:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql as String

3. initialise your variables:

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Conn.ConnectionString =  "Provider=SQLNCLI;Server=SERVER\SQLEXPRESS;Database=GE_Data;Trusted_Connection=yes;"
Conn.Open

This...

sql = "SELECT * FROM [DB] WHERE [DB].[DBID]=" & session("dbid")

...is a no-go. There are no session cookies in VBA. You'll need to pass a variable here.
Once accomplished, this should be fine:

rs.open sql, conn, 3, 3

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[MakeItSo]

Ooops, it was SKIP who mentioned the reference of course.
:-D

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[agent0047]

Thanks guys and can all this be done with an on open when the word doc is opened?

 

[agent0047]

Ok I think I got it to work on open and I know this sounds crazy but how do I input a field that shows say [clientname] in the document? I am very new to this.