macro stops after closing the active document

I have a Word form that I want to upload to a database after filling it out. This process is done through a VB macro. The MYSQL DB connection is done through ADODB. When I want to upload the file the file has to be closed. I have the macro in a template that I have open during the execution and the form is referencing the macro from the template. This was working in 2003 but now people start using MS Word 2007 and then when the active document closes the execution of the macro stops even when the template is still open.
here is some of the code:

 ActiveDocument.SaveAs FileName:=strNewDocName
 ActiveDocument.Close

  'Create connection to database
 Set conn = New ADODB.Connection
 conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=yyy;DATABASE=table;UID=xxx;PWD=xxx;OPTION=16427"

  conn.CursorLocation = adUseClient
  conn.Open
   
   
  Set rs = New ADODB.Recordset
  Set mystream = New ADODB.Stream
  mystream.Type = adTypeBinary
  rs.Open "SELECT * FROM log WHERE 1=0", conn, adOpenStatic, adLockOptimistic
  rs.AddNew
  mystream.Open
  mystream.LoadFromFile strNewDocName
       
  ' Upload data to database
  rs!user = Application.UserName
  rs!Date = mydate & mytime
  rs!status = status
  rs!moduleTested = strDocName
  rs!project = project
  rs!Document = mystream.Read
  ' need to translate "\n" into something else...
  rs!Comments = comment
  rs!operating_system = os
  rs.Update
  mystream.Close
  rs.Close
  conn.Close


Please let me know if you need more information.

Thanks for the kind help

Bernd
Start Free Trial