Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Write to MySQL from MS Word 1

Status
Not open for further replies.

xentaur

Programmer
Nov 17, 2003
35
AU
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 to write to MySQL from Access.

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

Cheers
 
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
 
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.

 
thanks PHV

Will post back when I find a solution.

Cheers

 
Hi again

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

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top