Write to MySQL from MS Word 1

[xentaur]

Hi gurus

I've designed several autoforms in MS Word for our organisation. The question has been posed as to whether client info entered into the forms can be recorded in our case-noting system, which is housed in a MySQL database on a remote server.

The logic of it seems simple:

1. Compile an 'INSERT INTO' SQL string from the various formfields on the document; and then

2. At the point the document is saved, have VBA execute the SQL via the MySQL ODBC connector.

I've achieved 1. easily but most of my VBA experience is in Access, and I'm a little lost without being able to use the same VBA in Word. Needless to say, Docmd.RunSQL and the like do not work in Word VBA.

In the casenote system I used the code provided by MS at

http://support.microsoft.com/kb/112108

to write to MySQL from Access.

Can anyone offer a solution to write to MySQL from MS Word?

Cheers

 

[PHV]

I'd use an ADODB Recordset or Command from the Microsoft ActiveX Data Objects 2.x Library.


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xentaur]

Thanks PHV for responding so quickly.

If you could direct me to some examples of how to use this library in MS Word I'd be grateful - I have not used ADODB before. Anything to give me a kickstart would be great.

Thanks again.

 

[PHV]

Have a look here:

http://msdn.microsoft.com/en-us/library/ms675532(VS.85).aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xentaur]

thanks PHV

Will post back when I find a solution.

Cheers

 

[xentaur]

Hi again

I've used some code that PHV posted in thread705-1550254 and all is working well.

Dim adoConn As ADODB.Connection
Dim adoRs As ADODB.Recordset
Dim strSQL As String

Set adoRs = New ADODB.Recordset

Set adoConn = New ADODB.Connection
adoConn.Open "Driver={MySQL ODBC 3.51 Driver};" & _
           "Server=xxx.xxx.xxx.xxx;" & _
           "Port=0;" & _
           "Database=dbname;", "userid", "password"

  strSQL = "INSERT INTO tblA (field1, field2, field3) " _
         & "SELECT  val1 as as expr1, " _
            & " val2 as expr2, " _
            & " val3 as expr3;"

adoRs.Open strSQL, adoConn
Set adoRs = Nothing
Set adoConn = Nothing

The code writes the values straight into the MySQL DB table from MS Word.

Thanks PHV, stars in both threads!

Cheers