Results 1 to 5 of 5

Thread: ADO Again

  1. #1
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    Unanswered: ADO Again

    I'm working on an ADO routine that will dump out (into word) all the names of the fields for each table in a database project because the built in documentation routine for Access is overkill. THe code is as follows:

    Sub DocumentTables()
    Dim WordX As New Word.Application
    Dim WordDoc As Word.Document
    Dim dbX As New ADOX.Catalog, tblX As ADOX.Table
    Dim WordTbl As Word.Table, rng As Word.Range
    Dim Flds As Integer, fldX As ADOX.Column
    Dim I As Integer

    Set dbX.ActiveConnection = CurrentProject.Connection
    Set WordDoc = WordX.Documents.Add


    For Each tblX In db.Tables
    I = 2
    Flds = tblX.Columns.Count
    oDoc.Content.InsertParagraphAfter
    oDoc.Content.InsertParagraphAfter
    Set rng = WordX.Paragraphs(oDoc.Paragraphs.Count - 1).Range
    Set WordTbl = WordX.Tables.Add(rng, Flds + 2, 3)
    With WordTbl
    .Cell(1, 1).Range.InsertAfter tblX.Name
    .Cell(2, 1).Range.InsertAfter "Field"
    .Cell(2, 2).Range.InsertAfter "DataType"
    .Cell(2, 3).Range.InsertAfter "Source"
    End With
    For Each fldX In tblX.Columns
    I = I + 1
    WordTbl.Cell(I, 1).Range.InsertAfter fld.Name
    WordTbl.Cell(I, 2).Range.InsertAfter fld.Type
    Next
    Set WordTbl = Nothing
    Next
    WordX.Visible = True
    Set WordDoc = Nothing
    Set WordX = Nothing

    End Sub

    Sub DocumentTablesOriginalCode()
    Dim oWrd As New Word.Application
    Dim oDoc As Word.Document
    Dim db As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim wdTbl As Word.Table
    Dim rng As Word.Range
    Dim Flds As Integer
    Dim fld As ADOX.Column
    Dim I As Integer

    Set db.ActiveConnection = CurrentProject.Connection
    Set oDoc = oWrd.Documents.Add

    For Each tbl In db.Tables
    I = 2
    Flds = tbl.Columns.Count
    oDoc.Content.InsertParagraphAfter
    oDoc.Content.InsertParagraphAfter
    Set rng = oDoc.Paragraphs(oDoc.Paragraphs.Count - 1).Range
    Set wdTbl = oDoc.Tables.Add(rng, Flds + 2, 3)
    With wdTbl
    .Cell(1, 1).Range.InsertAfter tbl.Name
    .Cell(2, 1).Range.InsertAfter "Field"
    .Cell(2, 2).Range.InsertAfter "DataType"
    .Cell(2, 3).Range.InsertAfter "Source"
    End With
    For Each fld In tbl.Columns
    I = I + 1
    wdTbl.Cell(I, 1).Range.InsertAfter fld.Name
    wdTbl.Cell(I, 2).Range.InsertAfter fld.Type
    Next
    Set wdTbl = Nothing
    Next
    oWrd.Visible = True
    Set oDoc = Nothing
    Set oWrd = Nothing
    End Sub

    But I get a User-Defined Type Not Identified message at this line of code
    Dim oWrd As New Word.Application
    What's wrong? Am I missing a reference? If so which one?

    Terry H

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    dumb point have you referenced the (correct) word library?
    Intersting application idea
    I've been mulling on a similar thing for a while, but was considering dumping to an XML document.
    Also wanted to include soemthing that could scan for which queries / forms / reports used a specified table, column etc
    Would like to look at the finished project
    good luck

  3. #3
    Join Date
    Aug 2004
    Posts
    13
    I could be wrong but I believe the reason your having an issue is because of the portion where you are declaring your application, document, setting thier values are in a sub function, they are not declaried as global. So they are not carring thourgh. If you were to do the declorations in the "option explicit," I think it would work. At least, that has been my expericience! Good look!

  4. #4
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    ADO Again

    I should have thought to check the Word Objects reference. I did that and got through the code just fine until the line (about half-way down) that reads:

    Set rng = WordX.Paragraphs(WordDoc.Paragraphs.Count - 1).Range

    Then I got a Method or data-member not found error message (error 461). Not sure what the hang-up here is.

    By the way, I'll be happy to share the result when its finished.

    Terry H

  5. #5
    Join Date
    Jul 2002
    Location
    Billings, MT
    Posts
    29

    ADO Again

    OK... I've got my ADO code cleaned up and it compiles. Code follows:

    Sub DocumentECITables()
    Dim WordX As New Word.Application, WDoc As Word.Document
    Dim WordTbl As Word.Table, rng As Word.Range
    Dim dbX As New ADOX.Catalog, tblX As ADOX.Table
    Dim Flds As Integer, fldX As ADOX.Column
    Dim I As Integer

    Set dbX.ActiveConnection = CurrentProject.Connection
    Set WDoc = WordX.Documents.Add

    For Each tblX In dbX.Tables
    I = 2
    Flds = tblX.Columns.Count
    WDoc.Content.InsertParagraphAfter
    WDoc.Content.InsertParagraphAfter
    Set rng = WDoc.Paragraphs(WDoc.Paragraphs.Count - 1).Range
    Set WordTbl = WDoc.Tables.Add(rng, Flds + 2, 3)
    With WordTbl
    .Cell(1, 1).Range.InsertAfter tblX.Name
    .Cell(2, 1).Range.InsertAfter "Field"
    .Cell(2, 2).Range.InsertAfter "DataType"
    .Cell(2, 3).Range.InsertAfter "Source"
    End With
    For Each fldX In tblX.Columns
    I = I + 1
    WordTbl.Cell(I, 1).Range.InsertAfter fldX.Name
    WordTbl.Cell(I, 2).Range.InsertAfter fldX.Type
    Next
    Set WordTbl = Nothing
    Next
    WordX.Visible = True

    Set WDoc = Nothing
    Set WordX = Nothing

    End Sub

    However, when it reaches the line

    Flds = tblX.Columns.Count

    the code crashes and provides the error message Object or Provider is not capable of performing requested operation. Run-time 3251. Any suggestions or ideas.

    Terry H

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •