Re: mailmerge and sql

From: Steven Packard (anonymous_at_discussions.microsoft.com)
Date: 08/21/04 

Date: Sat, 21 Aug 2004 09:46:28 -0700

One more idea:
I am launching my Word Templates from a VB application
that is essentially a database application with a document
launcher. I could execute the long sql statement in the
VB application. After doing that, could I somehow pass
the recordset to the Word Template and use it as if I got
the recordset from within Word (using ADO and pretending
there were no lenth issues)?

I was thinking that if I could pass a recordset like an
argument, I could solve my problem.

Any suggestions?

Thanks,
Steven

>-----Original Message-----
>> Is this similar to
>> using an ADO recordset as a datasource (if it could be
>> done)?
>
>No. It's just a possible way of overcoming the SQL length
constraint in
>OpenDataSource.
>
>I would advise that you proceed with caution as
> a. I'm not an ADO/ADOX expert. In particular, I know
little about ADOX's
>understanding of what constitutes a "Procedure" or
a "View".
> b. ADOX lets you change most features of a Jet
database's structure.
> c. there are various compatibility problems with ADOX
whereby views etc.
>created in ADOX do not appear in the list of queries in
Access 2000, which
>means that you will not be able to see them in a database
you open using the
>DDE connection method. I believe you may have to use DAO
instead of ADO to
>create queries that can be seen by Access 2000.
>
>Anyway, below are two sample routines, - the first uses
ADOX to create a new
>..mdb, and the second creates a table and a primary
index, inserts a few
>records, then creates a View containing a UNION query.
>
>The rest is up to you, but it may be worth pointing out
that there is some
>syntax that allows Jet queries to access tables and
queries in other
>databases, and/or you can create links in Jet to other
tables, so if you do
>need to avoid altering the source database structure, you
may be able to do
>everything you need in a "front-end" database that just
contains links and
>queries. But in all cases you need to test that Word can
actually link
>successfully to your data.
>
>Sub makedb()
>' sample routine to make a Jet database
>Dim oCatalog As ADOX.Catalog
>Dim sDatabasePath As String
>sDatabasePath = "c:\a\adoxtest.mdb"
>
>On Error GoTo DatabaseCreateError
>Set oCatalog = New ADOX.Catalog
>oCatalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & sDatabasePath
>On Error GoTo 0
>Set oCatalog = Nothing
>Exit Sub
>
>DatabaseCreateError:
>If Err.Number = -2147217897 Then
> Err.Clear
> Kill pathname:=sDatabasePath
> Resume
>Else
>' output some rudimentary error info.
> Debug.Print Err.Number, Err.Description
>End If
>
>End Sub
>
>Sub maketableandquery()
>' creates a simple table with a primary key
>' in an existing database and inserts a few records
>' assumes the database already exists but the table,
>' index and query do not
>
>Dim bDeleted As Boolean
>Dim oCatalog As ADOX.Catalog
>Dim oTable As ADOX.Table
>Dim oIndex As ADOX.Index
>Dim oCommand As ADODB.Command
>Dim oProcedure As ADOX.Procedure
>Dim oView As ADOX.View
>Dim sDatabasePath As String
>Dim sConnectString As String
>Dim sTableName As String
>Dim sKeyColumnName As String
>Dim sQueryName As String
>Dim sCommandText As String
>
>sDatabasePath = "c:\a\adoxtest.mdb"
>sConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & sDatabasePath
>sTableName = "mytable"
>sKeyColumnName = "myID"
>sQueryName = "myquery"
>On Error GoTo GeneralError
>
>' Create the catalog object and connect it to the database
>Set oCatalog = New ADOX.Catalog
>oCatalog.ActiveConnection = sConnectString
>
>' Create the table object
>Set oTable = New ADOX.Table
>
>' fill in some properties and create some columns
>With oTable
> .Name = sTableName
> Set .ParentCatalog = oCatalog
> .Columns.Append sKeyColumnName, adInteger
> .Columns.Append "myText", adVarWChar, 50
> .Columns.Append "myDate", adDate
>End With
>
>' Add it to the catalog
>oCatalog.Tables.Append Item:=oTable
>
>' It's good practice to set a primary key...
>' Create the index, set its properties and add the ID
>' column
>Set oIndex = New ADOX.Index
>With oIndex
> .Name = "mytablePrimaryIndex"
> .PrimaryKey = True
> .Unique = True
> .IndexNulls = adIndexNullsDisallow
> .Columns.Append sKeyColumnName
>End With
>
>' add the index to the table
>
>oTable.Indexes.Append oIndex
>
>Set oIndex = Nothing
>Set oTable = Nothing
>
>' Now use ADO to insert some data
>
>' Set up the invariant part of the command text
>sCommandText = " INSERT INTO " & sTableName & _
>" (" & sKeyColumnName & ", myText, myDate) VALUES "
>
>Set oCommand = New ADODB.Command
>With oCommand
> .ActiveConnection = sConnectString
> .CommandType = adCmdText
>' set up and execute some commands to create new records
>' Sometimes the first of these does not appear to work. I
don't know
>' why - ask an ADO expert
> .CommandText = sCommandText
& "(1,'mytext1','01/01/2004');"
> .Execute
> .CommandText = sCommandText
& "(2,'mytext2','01/02/2004');"
> .Execute
> .CommandText = sCommandText
& "(3,'mytext3','01/03/2004');"
> .Execute
> .CommandText = sCommandText
& "(4,'mytext4','01/04/2004');"
> .Execute
>
>End With
>
>' Clean up the ADO object
>Set oCommand = Nothing
>
>'GoTo q1
>
>' now set up a command to create a query
>' and add it to the catalog
>
>Set oCommand = New ADODB.Command
>oCommand.Name = sQueryName
>oCommand.CommandText = " SELECT * FROM " & sTableName & _
> " WHERE " & sKeyColumnName & " =
2" & _
> " UNION" & _
> " SELECT * FROM " & sTableName & _
> " WHERE " & sKeyColumnName & " = 3"
>On Error GoTo AddViewError
>oCatalog.Procedures.Append Name:=sQueryName,
Command:=oCommand
>oCatalog.Procedures.Refresh
>' Clean up the ADO object
>Set oCommand = Nothing
>
>' Clean up the ADOX objects
>q1:
>Set oCatalog = Nothing
>
>Exit Sub
>
>AddViewError:
>' Difficult to know whether it's a "view" or a "procedure"
>' Ask an ADOX expert!
>
>If Err.Number = -2147217816 Then
> ' View or Procedure exists. Remove it and resume
> bDeleted = False
> For Each oView In oCatalog.Views
> If oView.Name = sQueryName Then
> oCatalog.Views.Delete sQueryName
> bDeleted = True
> Exit For
> End If
> Next
> If Not bDeleted Then
> For Each oProcedure In oCatalog.Procedures
> If oProcedure.Name = sQueryName Then
> oCatalog.Procedures.Delete sQueryName
> bDeleted = True
> Exit For
> End If
> Next
> End If
> If bDeleted Then Resume
>End If
>
>GeneralError:
>' output some rudimentary error info.
> Debug.Print Err.Number, Err.Description
>
>End Sub
>--
>Peter Jamieson
>
>"Steven Packard" <anonymous@discussions.microsoft.com>
wrote in message
>news:898a01c48561$4cabb5a0$a401280a@phx.gbl...
>> Thanks for responding to this, as well as my post using
>> ADO recordset as a datasource.
>>
>> You stated "You may also be able to create your
query/view
>> at runtime using ADOX/ADO." I would very much like to
try
>> this. How do I go about doing this? Is this similar to
>> using an ADO recordset as a datasource (if it could be
>> done)?
>>
>> Thanks,
>>
>> Steven
>>
>> >-----Original Message-----
>> >Yes, it's a bummer.
>> >
>> >The stuff about the 255 and 510 limits in Word is
>> correct, but be careful,
>> >because in some cases Word may actually restrict you to
>> 255 (e.g. there may
>> >be a limit of 255 for OLEDB sources in Word 2002 and it
>> may have been
>> >removed in Word 2003.
>> >
>> >There is no SQLStatement2. I don't know what limit MS
>> Query imposes but the
>> >real problem there is that in Word Mailmerge, MS Query
is
>> only really used
>> >to set up an OpenDataSource statement, so even if MS
>> Query can execute
>> >longer queries, you are eventually constrained by
Word's
>> limit.
>> >
>> >All you can do is
>> > a. try to make your SQL as short as possible. If
you're
>> starting from SQL
>> >generated by e.g. MS Query, it's usually pretty easy to
>> shorten, add or
>> >eliminate alias names to reduce the overall query
length,
>> and so on. But I'd
>> >guess you've already done all that stuff.
>> > b. create the data source you really need as a Query
>> (e.g. if it's Access)
>> >or View, and use that instead. Even if the query has to
>> be constructed at
>> >runtime, you might be able to create a query/view that
>> returns all the
>> >columns you need (so your SQL can always start with the
>> short "SELECT * FROM
>> >queryname") and where you save all the space for filter
>> conditions in the
>> >WHERE clause, ORDER BY, etc. You may also be able to
>> create your query/view
>> >at runtime using ADOX/ADO.
>> >
>> >
>> >--
>> >Peter Jamieson
>> >
>> >"Steven" <anonymous@discussions.microsoft.com> wrote in
>> message
>> >news:134f01c48538$6f3f78f0$a301280a@phx.gbl...
>> >> I thought I had this mailmerge thing figured out,
then
>> >> BAM! I got a message saying my sql was too long. I
>> >> crafted an sql that gives me the data I need. It
works
>> in
>> >> MS Access, however, when I use the same sql statement
>> as a
>> >> string placed as an argument for the
>> >> activedocument.mailmerge.opendatasource method, I
get an
>> >> error message stating that I have more than 255
>> characters
>> >> in my sql string. Talk about deflation! Anyway, I
was
>> >> reading up and saw someone talk about it and they
said
>> >> that Word (but not Access) has a 255 character
>> >> limitation. I think they said something about it
being
>> a
>> >> limitation of MS Query. (Does Access use something
>> other
>> >> than what the mailmerge object uses?) I saw that
there
>> >> was a secondary argument I could use (SQLStatement1)
to
>> >> get an additional 255 characters - for a whopping
total
>> of
>> >> 510 characters! I read the help file and it says the
>> same
>> >> thing. That would be great except that my sql
statement
>> >> is approximately 600 characters give or take.
>> >>
>> >> What can I do? Is there another SQLStatement2
argument
>> or
>> >> equivalent?
>> >
>> >
>> >.
>> >
>
>
>.
>

Relevant Pages

  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Requery of Listbox does not display new data
    ... add a record to the database. ... Then the Lisbox control's requery method is ... The ADO command is run using a connection string to the mdb containing the ... Dim cmd As ADODB.Command ...
    (microsoft.public.access.formscoding)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)
  • Re: Is ADO Dead (3)?
    ... The Design of ADO ... ADO uses a single object, the Recordset, as a common representation for ... a forward-only stream of results from a database, ... where data is updated at the data source or cached locally as with the ...
    (comp.databases.ms-access)