Prev: Default Printer
Next: newbie needs examples of creating a recordset and inserting data using it in Access 2007?
From: James A. Fortune on 31 May 2010 21:43
On May 31, 7:21 pm, r <r...(a)r.com> wrote:
> I open Access 2007, open the mydatabase.accdb database, run the below
> code as a macro and it runs fine.
>
> But, if I open a Word 2007 document, try to run this as a macro, I get
> the error "Run Time Error 429 ActiveX component can't create object or
> return reference to this object". on the line where I am trying to open
> a handle to the database at OpenDatabase("C:\mydatabase.accdb").
>
> I want the below part to run in a Word document so it can read the data
> in the last column of every row of all the tables in a document,
> populate a recordset with the values and then insert it in a Access 2007
> table(Table1).
>
>   Sub ReturnTableText()
>
>   Dim oTable As Table
>   Dim oRow As Row
>   Dim oRng As Range
>   Dim sText As String
>   Dim count As Integer
>   Dim dbMyDB As DAO.Database
>   Dim myRecordSet As DAO.Recordset
>
>   Set dbMyDB = OpenDatabase("C:\mydatabase.accdb") ' this line throws an
> ' error Run Time Error 429 ActiveX component can't create object or
>   ' return reference to this object
>
>   Set myRecordSet = dbMyDB.OpenRecordset("Table1", dbOpenDynaset)
>
>   sText = ""
>   count = 0
>
>   For Each oTable In ActiveDocument.Tables
>
>   For Each oRow In oTable.Rows
>
>   If oRow.Cells.count  1 Then
>   Set oRng = oRow.Cells(oRow.Cells.count).Range
>   oRng.End = oRng.End - 1
>   myRecordSet.Fields(count).Value = oRng.Text & Chr(44)
>   MsgBox myRecordSet.Fields(count).Value
>   count = count + 1
>
>   End If
>
>   Next oRow
>
>   Next oTable
>
>   myRecordSet.Close
>   dbMyDB.Close
>
>   End Sub
>
>   I checked the documentation at
>  http://msdn.microsoft.com/en-us/library/aa231060%28VS.60%29.aspx
>   but could not solve it. The registry seems proper and the required
> References are present, there is no missing reference.
>
>   Any advice would be appreciated on how to solve this error.

In:

http://groups.google.com/group/microsoft.public.access/browse_frm/thread/80e2331aecd64658

I said:

"I vaguely recall that in earlier versions of Word, I think I had to
use the Workspace object within Word VBA in order to interact with
Access data correctly."

In the same post I also said:

"I haven't used Word 2007 yet so I'll refrain from making
recommendations about the best course of action when ignorance is
present."

I'll leave it at that.

James A. Fortune
CDMAPoster(a)FortuneJames.com
From: James A. Fortune on 2 Jun 2010 15:15
On Jun 1, 9:12 am, r <r...(a)r.com> wrote:

>
> Can you please explain how to do that?
>
> I tried
> Set dbMyDB = DBEngine.Workspaces(0).OpenDatabase("C:\mydatabase.accdb")
>
> But, it did not work.
>
> > In the same post I also said:
>
> > "I haven't used Word 2007 yet so I'll refrain from making
> > recommendations about the best course of action when ignorance is
> > present."
> > I'll leave it at that.
>
> I saved the Word 2007 document as a Word 2003 document having extension
> ".doc" but it still did not work.
>
> Am I missing something?
>
> Thanks for your reply.
>
> > James A. Fortune
> > CDMAPos...(a)FortuneJames.com

I don't remember the exact details and the original post is too old
for Google to bring up.

Perhaps look at the following example (VBA in Excel or Word should be
similar to each other):

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q146607&

or try using ADODB.

If I find an example of old Word VBA code that reads or writes to an
Access database I'll post back.

James A. Fortune
CDMAPoster(a)FortuneJames.com
 | 
Pages: 1
Prev: Default Printer
Next: newbie needs examples of creating a recordset and inserting data using it in Access 2007?