Acces 2003 - Word 2003 - SQL Server 2000 - Mailmerge

[jwr4]

I create a view in SQL, an ODC file that makes a trusted connection to SQL
Server and a Word Doc that uses the ODC file as a merge datasource.

The view is tested and returns data.
The ODC File is opened in Excel and returns data.
The Word Doc is opened and all records are available for merge.

Now the idea is to select from the view in Access to print.
Here is the code:
With objWord
.Visible = True
Dim SQL As String
SQL = "Select * from vwUnionLettter where strjobno=" & Me.JobNumber
Debug.Print SQL
.Documents.Open ("S:\operations\Templates\UnionLetter.doc")
.ActiveDocument.MailMerge.OpenDataSource
Name:="S:\operations\Templates\UnionLetter.odc", SQLStatement:=SQL
.ActiveDocument.MailMerge.Destination = wdSendToNewDocument
.ActiveDocument.MailMerge.Execute
.Documents(2).Close False
End With

The code invariable fails @ OpenDataSource... with a dialog: 5922 - Word was
unable to open the datasource.

The SQL string is correct and runs in SQL QA. If I comment out the
'SQLStatement:=' parameter the merge runs, albeit with all the records from
the view.

 

[Norman Yuan]

it seems data type of the "strjobno" column(field) in the view
"vwUnionLetter" is TEXT, not NUMBER (the prefix "str" implies it). If so,
the SQL statement should be:

SQL = "Select * from vwUnionLettter where strjobno='" & Me.JobNumber & "'"

 

[jwr4]

Unfortunately, I inherited this application from the business owner. strJobNo
is actually an integer.

After much googling I found a work around for the SQLStatement:

Was: Select * from vwXXX where fieldname=NNN
Now: Select V.* from vwXXX V where V.fieldname=NNN


It is a royal PITA to convert my code, but whatever ... SQL that runs in QA,
should be parsed AOK by OLEDB, don't you think?