From: r on
On 5/31/2010 9:43 PM, James A. Fortune wrote:
> 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."

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
> CDMAPoster(a)FortuneJames.com

From: r on
On 6/2/2010 3:15 PM, James A. Fortune wrote:
> 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

Actually, don't worry I got it resolved. I had to check 'Microsoft DAO
3.6 Object Library' in Tools->References.

Thanks for your reply.