Inserting data from ADO record set.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ImOkYoureNot - 18 Aug 2004 04:01 GMT

I dont want to use the mailmerge abilities for various reasons.
So I created a recordset using the following commands:

=============================================================
' Connect and select record.
ado.Open ("Provider=MSDASQL.1;Persist Security Info=True;Extended
Properties=""DSN=db;DBQ=C:\vsrep\db1.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;"";Initial
Catalog=C:\vsrep\db1")
Call rs.Open("SELECT * FROM `wo` where wo=10", ado, adOpenDynamic)

' Now access the data into variables
wo=rs.field("wo")
client=rs.field("client")
====================================================================

Which field can I add to my document to display the data. I tried
using {DOCVARIABLE wo} and {DOCVARIABLE client} but how do I access
them from my VBA code. I would like to have my code figure out which
fields are on the document and populate only those.

E.g. I would like to do something like this (but of course it doesn
work.)

for i=1 to ActiveDocument.Fields.Count
fldName=ActiveDocument.fields(i).name ' no such property
ActiveDocument.Field(fldName).value = rs.field(fldName)
next

Is there an equivalent way?

Thanks

Reply to this Message

Peter Jamieson - 18 Aug 2004 09:51 GMT

There are various ways you could do this, but the easiest way is probably to
use DOCVARIABLE fields because you do not then need to locate specific
fields and bookmarks in your document - you can just set up the
Document.Variables in VBA, then select the Document (or if necessary, select
each Story in the Document, and refresh all the fields.

The thing you need to know is that DOCVARIABLE doesn't display the value of
a /VBA/ variable. VBA variable values are transient and only exist while the
code is executing. It displays the value of a Variable object in the
Document.Variables collection. So you can do something like:

' Deal with the situation where the variable already exists
On Error Goto x
ActiveDocument.Variables.Add Name:="wo", Value:=CStr(rs.field("wo"))
x:
Err.Clear
On Error Goto 0
ActiveDocument.Variables("wo").Value = CStr(rs.field("wo"))

(ask a VBA expert for suggestions on how to write better VBA).

Then put

{ DOCVARIABLE wo }

or

{ DOCVARIABLE "wo" }

in your document.

You don't have to use a CStr but you may need to pay attention to the data
types you are using and to use format switches in your DOCVARIABLE fields to
get what you need.

Signature

Peter Jamieson

> I dont want to use the mailmerge abilities for various reasons.
> So I created a recordset using the following commands:
[quoted text clipped - 28 lines]
>
> Thanks

Reply to this Message

ImOkYoureNot - 18 Aug 2004 15:08 GMT

Hi Peter,

This is not exactly what I would like. I would like to go to the
document, and add "fields" without having to change my VBA code.
Then, my VBA code would go through all the "fields" on the document,
and populate them from my recordset based on their name. In
otherwords my VBA code would discover which fields or on the document
and use their names to populate them with data from the recordset.

This way if a user decides they want one more "field" they just have
to use the Insert/Field to add one on their template.

Also anytime the document is opened it should reflect the current
values of the record.

Thanks

> There are various ways you could do this, but the easiest way is probably to
> use DOCVARIABLE fields because you do not then need to locate specific
[quoted text clipped - 30 lines]
> types you are using and to use format switches in your DOCVARIABLE fields to
> get what you need.

Reply to this Message

Peter Jamieson - 18 Aug 2004 20:47 GMT

If you want to do that, in essence you have to develop a set of features
rather similar to Mailmerge's - i.e. the user needs to be able to pick from
a list of possible column names in your data source (unless you just want to
let them enter any old name), you need to decide how to represent those
"placeholders" (cf. MERGEFIELD fields), how the user switches from
"placeholder" view to "results" view and back, how to populate your document
on open and preserve various pieces of information such as "current record",
and how exactly to do that in code.

That's a substantial amount of thought, design and coding. You may well find
packages that can already do it (it's clear from some posts in this
newsgroup that some people are using that kind of package as an alternative
to the built-in merge facilities), and you may well be able to find code
that does it, but I certainly don't have a suite of such code on tap,
although I occasionally think of developing something like that.

So all I could really do was point at a way (and I think it's a fairly
simple way) to inject ADO data into a Word document. It's really up to you
to decide whether or not to build on that. For example, if you get users to
put DOCVARIABLE fields in your document, you can fairly easily iterate
through the collection of fields looking for DOCVARIABLE fields, extract the
column name from each such field, see if there is an equivalent column name
in your ADO data source, and if so, create an appropriately named
Document.Variable and assign a value to it. If there isn't an equivalent
value you could create the Variable and assign some sort of error message to
it, or pop up a message box. There are other ways you could achieve the same
objective.

I've assumed you are connecting via ADO for a reason, but maybe all you need
to do is connect via OpenDatSource instead. And if you want to be able to
deal with various types of ADO source that you can't access via
OpenDataSource (because it has various failings) then another possible
approach might be to use ADO to create and populate a source (perhaps a .mdb
file) that can easily be accessed via OpenDataSource.

Just food for thought - sorry, I can't really do more than that.

Signature

Peter Jamieson

> Hi Peter,
>
[quoted text clipped - 47 lines]
> > types you are using and to use format switches in your DOCVARIABLE fields to
> > get what you need.

Reply to this Message