Re: mailmerge and sql

From: Peter Jamieson (pjj_at_KillmapSpjjnet.demon.co.uk)
Date: 08/19/04 

Date: Thu, 19 Aug 2004 13:31:39 +0100

> 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

  • 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: DAO takes too much time to link tables
    ... I am trying to link some tables to a back-end database. ... 'Link the tables contained at the given query. ... Dim rst As DAO.Recordset ... Dim success As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • Re: HELP WITH AUTO EXE PROGRAMMING/CODE
    ... Create a separate database for those times when you won't have your database ... create a Query that returns only the Records about which you ... Dim strsubject As String ...
    (comp.databases.ms-access)
  • Re: mailmerge and sql
    ... that is essentially a database application with a document ... the recordset to the Word Template and use it as if I got ... >> using an ADO recordset as a datasource (if it could be ... >Dim oCatalog As ADOX.Catalog ...
    (microsoft.public.word.mailmerge.fields)
  • RE: ODBC query in VB code Need HELP
    ... Everything for ADO is in the first 2 messages that I gave you. ... Since your pass-through query already exists (including the ... Dim STRSQL As String ...
    (microsoft.public.access.formscoding)