Okay, I switched the way I was connecting, and started using a DSN
instead. However, I have been unable to use a DSN-Less connection
string for Mail Merge purposes, and I have been unable to
programatically create a SQL dsn that has UID and PWD information.
If I could either create a DSN-less connection string for Mail Merge,
or create a dsn during installation that contains all login
information, then I can call this project done.
Any ideas?
Thanks,
Chris McKenzie
Peter Jamieson wote:
To be honest I'm amazed that your code would work with Word 2003 - if I use
an OpenDataSource call that specifies a dsn file but no connection string,
Word just thinks you're trying to open a text file.
Anyway, a few experiments here suggest that
a. if you are using ODBC to connect, you don't encounter this problem.
b. if you are using OLEDB to connect, you do encounter the problem you
describe. I have always found that the OLEDB provider seems to be more picky
about SQL syntax than the ODBC driver. It's possible that the ODBC driver is
translating the query (ODBC is certainly designed to do stuff like that) and
getting it right, and OLEDB is not, or it's possible that they are passing
different settings to SQL Server that cause Transact-SQL to behave
differently. I don't know.
c. What I generally find is that qualifying table names with an alias (and
possibly even /using/ the alias) makes OLEDB behave - e.g.
SELECT o.* FROM Orders o WHERE o.OrderDate ...
Personally I wouldn't use the 1=1 workaround on the grounds that the query
optimiser might not eliminate the expression (seriously! I don't know what
SQL Server does but I once did this with Oracle to get around some problem
and the query processor examined every candidate record, presumably to
ensure that 1 still equalled 1. Maybe that's gone now).
It's not part of your issue, but personally, I would also tend to use
'YYYY-MM-DD' format for dates, or even the official ISO one
(YYYY-MM-DDTHH:MM:SS, e.g. 2006-09-28T13:06:05 ) if it is supported in SQL
Server 2000, or do something like
myDate CONVERT(DATETIME, '1996-07-06 00:00:00', 102)
(you need to look up CONVERT in SQL Server books online to get the right
number - instead of 102 - for the date format you want to use). Assuming
that's in SQL SERVER 2000.
Peter Jamieson
"crm" wrote in message
ups.com...
I'm developing a Form Letter engine for my customers which pulls data
from their SQL Server 2000 database. The user clicks a button on the
toolbar which brings them to a data selection form. This form builds a
SQL Query behind the scenes based on their selections. I then Mail
Merge against the query. This works perfectly except when the filter
criteria on the query is a single date field.
Sample VBA Code:
Public Sub ExecuteMailMerge(query As String)
On Error GoTo err_handler
Dim dsn As String
dsn = "My DSN"
With ActiveDocument.MailMerge
.OpenDataSource dsn, LinkToSource:=False, _
SqlStatement:=query
.Execute False
End With
Exit Sub
err_handler:
MsgBox ("Failed to execute mail merge: " + Err.Description)
End Sub
Sample Query:
"SELECT * FROM [View Name] WHERE ( DateField = '9/08/2005 12:00:00 AM'
)"
Sample Query 2:
"SELECT * FROM [View Name] WHERE ( DateField = '9/08/2005' )"
When using a query involving a single date field, I get a "Failed to
execute mail merge: Word was unable to open the data source." error.
I have been able to hack my way past this problem by adding "AND 1=1"
to the end of the query. However, this is still a hack, and I hate
hacks. I'd much rather identify and solve the underlying problem. Any
ideas?
Thanks,
Chris McKenzie
http://weblogs.asp.net/taganov