How to retrieve records from a database?

[Yoyo]

Hi, I need to retrieve records from database by clicking a command button on
Word document. Could anyone tell me how I can do that? I tried to insert
database and record the moves in a macro. But when I put the coding in the
commandbutton_click and tried to run it, a dialog box always opened and ask
for data source, why?

I really appreciate if someone could help me. Thanks.

 

[Peter Jamieson]

I've never used the Insertdatabase method that Word records in this case,
but it's clearly broken.

If you want to use the Insert|Database approach, there are two ways to do
it:
a. insert the results of a database query as a table
b. insert the results of a database query as a { DATABASE } field that
results in a table.

When you record database insertion, the Insertdatabase method uses
LinkToSource:=False for (a) and LinkToSource:=True for (b). However, when
you use method (b), Word inserts a DATABASE field but fails to insert the \d
"the pathname to the database" switch, so the field cannot possibly work.

In other words, Insertdatabase does not appear to work for case (b). If you
want to do that, the simplest thing to do is insert a DATABASE field with
all the correct switches/parameters, then execute it. This may or may not be
the best approach to doing whatever it is you want to do.

Recording a macro that does (a) and executing it seems to work OK, but
notice that the connection string that Word builds for some database types
(e.g. Access) can be longer than 255 characters. Word will insert the
correct connection string when it inserts a DATABASE field, but truncates
the string to 255 characters in the recorded macro, and in that case you may
need to fix the connection string before the macro will work.

Other approaches to getting data into Word include:
a. set up the document as a Mail merge Main Document, and use VBA and the
facilities of the MailMerge.DataSource document to read the data from the
source and insert it into the document however you need. I would try to
avoid this because turning your document into a Mail merge main document is
likely to create plenty of difficulties, but the rason I mention it is
because the Mail merge Datasource object allows you to get data from any
data source that Word knows how to use, including text files, word
documents,Access, Excel, ODBC and OLEDB data sources.
b. use VBA and ADO, assuming your data source has a suitable OLEDB
provider.

Peter Jamieson

 

[Doug Robbins - Word MVP]

Use the following code:

Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Long
Dim dtable As Table, drow As Row
'Open a database
Set myDataBase = OpenDatabase("c:\Access\Procurement Plan.mdb")
'Access the first record from a particular table
Set myActiveRecord = myDataBase.OpenRecordset("Currencies",
dbOpenForwardOnly)
'Add a table to the document with one row and as many fields as there are in
the database table
Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range, NumRows:=1,
numcolumns:=myActiveRecord.Fields.Count)
Set drow = dtable.Rows(1)
'Loop through all the records in the table until the end-of-file marker is
reached
Do While Not myActiveRecord.EOF
'Populate the cells in the Word table with the data from the current
record
For i = 1 To myActiveRecord.Fields.Count
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1)
Next i
'Add a new row to the Word table and access the next record
Set drow = dtable.Rows.Add
myActiveRecord.MoveNext
Loop
'The last row will be empty, so delete it
drow.Delete
'Then close the database
myActiveRecord.Close
myDataBase.Close

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Greg Maxey]

Doug,

Maybe you can explain this. I have a Excel spreadsheet with a defined
range "mydatabase"

It spans A1:C4 and contains the following data:

Name Age Address
Joe 25 PA
Bill 30 NC
Mary 35 NY

When I run a modified version of your code below, the table is created
but the first row of the range (i.e., A1:C1) "Name Age Address"
is not included. It seems like the macro always just skips the first
row in the spreadsheet. Can you explain why and how to fix?

Thanks.

Sub Testing()
Dim myDB As Database
Dim myActiveRecord As Recordset
Dim i As Long
Dim dtable As Table, drow As Row
Set myDB = OpenDatabase("C:\myBook1.xls", False, False, "Excel 8.0")
Set myActiveRecord = myDB.OpenRecordset("mydatabase",
dbOpenForwardOnly)
Set dtable = ActiveDocument.Tables.Add(Range:=Selection.Range,
NumRows:=1, numcolumns:=myActiveRecord.Fields.Count)
Set drow = dtable.Rows(1)
Do While Not myActiveRecord.EOF
For i = 1 To myActiveRecord.Fields.Count
drow.Cells(i).Range.Text = myActiveRecord.Fields(i - 1)
Next i
Set drow = dtable.Rows.Add
myActiveRecord.MoveNext
Loop
drow.Delete
myActiveRecord.Close
myDB.Close
End Sub