VB mailmerge with MyODBC sometimes throws 5922 error

Hi,

I wrote some VB to MailMerge a mysql query with a word document. It
does an initial SQL to fill a combo box and the user selects from the
combo box and clicks the command button and iut generates a single form
letter. That part works consistently. But then we needed to be able
to select everything in the combo box and generate the letters en
masse. So I changed the code a little to make a loop.

The problem is, the loop will run a few times, or 5 or 6 or 7 times,
and then kick out this 5922 error, cannot connect to database.

I've included the code inside the loop. Thanks for any insight!

'-------------------------------------------------------------
'--- Sub to create merged docs from
'--- mysql data
'-------------------------------------------------------------
Private Sub CommandButton1_Click()

'--------------------------------------------------------
'-- Dimension variables
'--------------------------------------------------------
Dim oMainDoc As Word.Document
Dim CString As String
Dim SQL1 As String
Dim intListCount As Integer
Dim intLoopIndex As Integer
Dim intDataErr As Integer
Dim strMMMsg As String
Dim intLoopStart As Integer
'--------------------------------------------------------
'-- DONE Dimension variables
'--------------------------------------------------------

'--------------------------------------------------------
'-- Initialize
'--------------------------------------------------------
On Error GoTo ErrorHandler
strMMMsg = ""
intDataErr = 0
'--------------------------------------------------------
'-- DONE Initialize
'--------------------------------------------------------

'--------------------------------------------------------
'-- Set multiple or Single processing
'--------------------------------------------------------
'--- This part lets me loop thru multiple selections
'--- Or just go thru once if its a single selection
If strSelectID = "AllSelects" Then
intListCount = ComboBox1.ListCount - 1
intLoopStart = 1
Else
intLoopStart = ComboBox1.ListIndex
intListCount = intLoopStart
End If
'--------------------------------------------------------
'-- DONE Set multiple or Single processing
'--------------------------------------------------------

'--------------------------------------------------------
'--- Loop thru the combo box, creating the mailmerge docs
'--------------------------------------------------------
For intLoopIndex = intLoopStart To intListCount

'--- Kicks back here if we get a 5922 error
'--- usually lets me do one more doc before crash
retrydata:

'----------------------------------------------
'-- construct the SQL statement
'----------------------------------------------
'--- populate the current strings for the query
strSelectID = ComboBox1.List(intLoopIndex, 1)
strSelectName = ComboBox1.List(intLoopIndex, 0)

'--- this query calls a mysql stored procedure because
'--- the SQL statement is over 800 chars long
'--- beg_date.Value comes from the dialog that contains the
combobox
SQL1 = "call TESTdb.investorccletter('" & strSelectName &
"','" & strSelectID & "',DATEDIFF('" & beg_date.Value &
"','1900-01-01'))"
'----------------------------------------------
'-- DONE construct the SQL statement
'----------------------------------------------


'--- set oMainDoc to the doc with the source running this
script
Set oMainDoc = ActiveDocument

'----------------------------------------------
'--- MailMerge processing
'----------------------------------------------

With oMainDoc.MailMerge

.MainDocumentType = wdFormLetters
'--- TESTData the DSN I created to point to my
'--- MyODBC v3.51 driver
CString = "DSN=TESTData;DATABASE=TESTdb;UID=ODBC;PWD=;" _
& "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 163841

'--- Set up the mail merge data source
'--- this is where the 5922 error occurs
.OpenDataSource Name:="", _
Connection:=CString, _
SQLStatement:=SQL1, _
LinkToSource:=True, _
SubType:=wdMergeSubTypeWord2000, _
OpenExclusive:=True

'--- Perform the mail merge to a new document.
.Destination = wdSendToNewDocument
.Execute Pause:=True

'--- save the merged doc we just created
ActiveDocument.SaveAs strSelectName & " - Investor
Letters - " & UserForm1.beg_date.Value
'--- close the data source
.DataSource.Close
'--- add the last doc name to the message string
strMMMsg = strMMMsg & ActiveDocument.Name & Chr(13) &
Chr(10)
'--- close the merged doc we just created
ActiveDocument.Close False

End With
'----------------------------------------------
'--- DONE MailMerge processing
'----------------------------------------------


Next intLoopIndex
'--------------------------------------------------------
'--- DONE Loop thru the combo box, creating the mailmerge docs
'--------------------------------------------------------


'--- Print out message listing the created merged docs
MsgBox "Mail Merge Complete: " & Chr(13) & Chr(10) & strMMMsg

'--- skip over the error handler
GoTo Done:


ErrorHandler:

Select Case Err.Number
Case 5631:
'--- no result from query
'--- display a msg in the dialog
Err.Clear
Resume EmptyResult

Case 5922:
'--- can't connect to database
'--- try ten times before giving up
'--- doesn't really work
If intDataErr < 10 Then
intDataErr = intDataErr + 1
Err.Clear
GoTo retrydata
Else
Err.Clear
GoTo Done
End If

Case Else:
MsgBox "Error Number: " & Err.Number & " -->" & Err.Description
Err.Clear
Resume EmptyResult

End Select

EmptyResult:
'--- if no result from the query, can't merge, stick msg in the dialog
UserForm1.ErrNote.Caption = "No Data found for that fund and date."
UserForm1.ErrNote.Visible = True

Done:
'--- Unload the dialog
Unload UserForm1
End Sub