Looping thru the records of a database

[Shobha Deepthi]

hello all,
am working on a word document that reads records from
the database and inserts into a word document.

Its something like this,

In the first line of the word document i access a field
(module name) of Module table in SQL database n am
displaying it.
In the second line i access another field(testcase no) of
a different databse table(say testcase) and am displaying
it in this second line.
What I want is ,to display these entries of databse for
all the records.
for eg if there r 4 records of module table n 4 records
in testcase table i want the first record of module table
n first record of testcase table to be printed at once n
then the remaining

plzz help me out in this

 

[Cindy Meister -WordMVP-]

Hi Shobha,

Are we talking about mail merge, or something else? If mail
merge, which version of Word are you working with?

If you're using Automation and not mail merge, then you need
to ask how to loop through the database records in a group
that supports the data-connection method you're using for
the database (for example the data.ado group)

am working on a word document that reads records from
the database and inserts into a word document.

Its something like this,

In the first line of the word document i access a field
(module name) of Module table in SQL database n am
displaying it.
In the second line i access another field(testcase no) of
a different databse table(say testcase) and am displaying
it in this second line.
What I want is ,to display these entries of databse for
all the records.
for eg if there r 4 records of module table n 4 records
in testcase table i want the first record of module table
n first record of testcase table to be printed at once n
then the remaining

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Shobha Deepthi]

Hi Cindy,

Thanx for ur reply.
I am not using mail merge but thru adodb class i
recorded a macro to access the records from database.But
now the problem is I want the result to be displayed in a
table format ,instead its displaying in a paragraph
format.

my code is as follows:
Dim doc As Document
Dim db As New ADODB.Connection
Dim rec As New ADODB.Recordset ' Declaring the
Recordset
Dim SQL As String
Dim i As Integer

Set doc = ActiveWindow.Document

db.Open "Provider=SQLOLEDB.1" & _
"Persist Security Info=True;" & _
"User ID=kumar;" & _
"Initial Catalog=testdb;" & _
"Data Source=blrkec21606d;" & _
"Use Procedure for Prepare=1;" & _
"Auto Translate=True;" & _
"Packet Size=4096;" & _
"Workstation ID=BLRKEC20997D;" & _
"Use Encryption for Data=False;Tag with
column collati"

SQL = "SELECT ""Label"",""ModuleName""
FROM ""TableHeader"" WHERE ((""ModuleId"" = 'MDS/BM'))"

rec.Open SQL, db, adOpenDynamic,
adLockOptimistic ' Opening the Recordset

For i = 0 To rec.Fields.Count - 1
doc.Content.InsertAfter Text:=rec.Fields(i).Value
Next i
rec.Close
db.Close
ActiveWindow.ActivePane.SmallScroll Down:=9





And the version that am using is word 2002.

I'll be obliged if u help me out in this

looking forward for ur mail
shobha deepthi

 

[Cindy Meister -WordMVP-]

Hi Shobha,

I am not using mail merge but thru adodb class i
recorded a macro to access the records from database.But
now the problem is I want the result to be displayed in a
table format ,instead its displaying in a paragraph
format.

OK, I'm with you, now.

Put the data into a delimited string format. Be sure to use
vbCR as the record delimiter, pretty much any character
that's not part of the data can be the field delimiter. If
you use carriage returns in the data, the generate a
comma-delimited string, making sure EVERY field is in
"quotes". (You will, of course, have the usual problems if
the data contains "quotes".)

Anyway, once you have the delimited string, dump it into a
RANGE in the document. Then use the ConvertToTable method to
turn it into a table.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jan
24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[Shobha Deepthi]

Hi CIndy,

lemme first thanku for ur reply.
If I use covertToTable to convert into a table will I be
able to update it from word document?

And one more thing using ADODB class sql query is
generating only one record though there r many other
records that match the query.Heres my code for that




dbmain.Open "Provider=SQLOLEDB.1;Persist Security
Info=True; " & _
"User ID=kumar;Initial Catalog=testdb; " &
_
"Data Source=blrkec21606d;Use Procedure
for Prepare=1; " & _
"Auto Translate=True;Packet
Size=4096;Workstation ID=BLRKEC20997D; " & _
"Use Encryption for Data=False;Tag with
column collati"
sql = "SELECT ""TCaseId"" FROM ""testcase"" WHERE
((""ModuleId"" = 'MDS/BM'))"
recmain.Open sql, dbmain, adOpenDynamic,
adLockOptimistic


My recmain consists of only the first record that
matches the where clause ...what about the reamining
records?

once again thanking u
shobha

 

[Cindy Meister -WordMVP-]

Hi Shobha,

If I use covertToTable to convert into a table will I be
able to update it from word document?

Update what/which? You've lost me, here. There's been no
mention of needing to update anything...

If you want to insert a table into Word that is linked to a
data source, then your only choice is a DATABASE field. And
the database field can't be linked to a recordset, it must
be linked to a table, or in the case of SQLServer, it could
also be linked to a VIEW.

And one more thing using ADODB class sql query is
generating only one record though there r many other
records that match the query.Heres my code for that

You need to pursue this question in the data.ado group.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Jan 24 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail

 

[shobha deepthi]

HI CIndy,

thanx a lot!!!

Yeah i forgot to use recorset.move method to move thru
the records
that was my problem n i have solved it now
\thanx again