mySQL and Word mail merge(Resolved)

[PJMAXX]

SEE Bottom of this post...

Can anyone help me out a little....
What I'm tring to do is connect to 1 table in mySQL database
and get a the data for printing mailing labels.....
I get error 5922 Word was unable to open data source.
I did a search and tried everything I could find with no luck...
It does how ever connect maybe once every 10 or 15 tries.

Dim sSQL As String
Dim sConn As String
Dim oApp As Word.Application
Dim oDoc As Word.Document
'Start a new document in Word
Set oApp = CreateObject("Word.Application")
Set oDoc = oApp.Documents.Add
MousePointer = 11
With oDoc.MailMerge
With .Fields
.Add oApp.Selection.Range, "First"
oApp.Selection.TypeText " "
.Add oApp.Selection.Range, "Last"
oApp.Selection.TypeParagraph
.Add oApp.Selection.Range, "Address"
oApp.Selection.TypeParagraph

.Add oApp.Selection.Range, "City"
oApp.Selection.TypeText ", "
.Add oApp.Selection.Range, "State"
oApp.Selection.TypeText " "
.Add oApp.Selection.Range, "Zip"
End With


Dim oAutoText As Word.AutoTextEntry
Set oAutoText = oApp.NormalTemplate.AutoTextEntries.Add("MyLabelLayout", oDoc.Content)
oDoc.Content.Delete 'Merge fields in document no longer needed now
'that the AutoText entry for the label layout
'has been added so delete it.


'Set up the mail merge type as mailing labels and use
'a tab-delimited text file as the data source.
.MainDocumentType = wdMailingLabels

sSQL = "SELECT * FROM S1"
sConn = "Driver={MySQL ODBC 3.51 Driver};" & _
           "Server=" & ADDY & ";" & _
           "Database=Scouts;" & _
           "Uid=" & UID1 & ";" & _
           "Pwd=" & Pas1 & ";"


'sConn = "DSN=;DATABASE=Scouts;uid=" & UID1 & ";pwd=" & Pas1 & ";"
.OpenDataSource Name:="C:\Scout1.dsn", Connection:=sConn, SQLStatement:=sSQL     <---My problem seems to be here....

'Create the new document for the labels using the AutoText entry
'you added -- 5160 is the label number to use for this sample.
'You can specify the label number you want to use for the output
'in the Name argument.
oApp.MailingLabel.CreateNewDocument Name:="5160", Address:="", _
AutoText:="MyLabelLayout", LaserTray:=wdPrinterManualFeed

'Execute the mail merge to generate the labels.
.Destination = wdSendToNewDocument
.Execute

'Delete the AutoText entry you added
oAutoText.Delete

End With

'Close the original document and make Word visible so that

'the mail merge results are displayed
oDoc.Close False
oApp.Visible = False
oApp.PrintOut True
Sleep 3000
'Prevent save to Normal template when user exits Word
oApp.NormalTemplate.Saved = True
oApp.Quit False
Set oApp = Nothing
Set oDoc = Nothing
Set oAutoText = Nothing
MousePointer = 0
Exit Sub
err1:
Set oApp = Nothing
Set oDoc = Nothing
Set oAutoText = Nothing
MousePointer = 0

The connection string was the kicker
I have office XP and for some reason mail merge don't like it...
So to fix or work around is to force it to Word2000.
Maybe someone else can use this info.....

.OpenDataSource Name:="", _
  Connection:="DSN=Scout1", SQLStatement:=sSQL, SubType:=wdMergeSubTypeWord2000

[SkiNLaB]

I myself use mysql to mail merge with Word, and it has worked ok so far for me ( i havent given it a huge work over or anything)

make sure ur sql statement is less than 250 characters, if it is over u need to pass the rest in a second parameter, sqlstatement1

are u trying the SAME sql each time u give this a try?

if all the conditions are the same for each of those 10 or 15 tries, and only one works, i'd say ur going to have a hard time fixing this one...

[PJMAXX]

Yes I'm using the same sql statement....Very short....

[SkiNLaB]

In your connection string chuck this in there

"OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 & ";"

so the string would contain the some of all those numbers.

[PJMAXX]

Originally posted by SkiNLaB
In your connection string chuck this in there

"OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 & ";"

so the string would contain the some of all those numbers.

Just to understand what this does please explain....
I'm new to the mail merge thing....
My wife is a Girl scout leader and I'm just tring to help here out with the mailing label thing....
But I can find a few more things to use mail merge with I'm sure...
Every let bit counts....lol

[SkiNLaB]

the title says resolved??

anyway,

replace

sConn = "Driver={MySQL ODBC 3.51 Driver};" & _
           "Server=" & ADDY & ";" & _
           "Database=Scouts;" & _
           "Uid=" & UID1 & ";" & _
           "Pwd=" & Pas1 & ";"

WITH

sConn = "Driver={MySQL ODBC 3.51 Driver};" & _
           "Server=" & ADDY & ";" & _
           "Database=Scouts;" & _
           "Uid=" & UID1 & ";" & _
           "Pwd=" & Pas1 & ";" & _
           "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384 & ";"

Good luck

[SkiNLaB]

oi tell me how u resolved it! i wanna mail merge without using a DSN.

get back here!

[PJMAXX]

I used a DSN, But after trying it out I just coded a print function to do the labels.....