I'm trying to insert snippets of a word document into a MySQL database. I did this in Java, but I lost all formatting of the Microsoft document, so I'm now trying to do the same in VBA (I'm hoping that this will keep the formatting?!). But to find out if it will, I need to try.. I've set up the connection and the table in MySQL but I can't insert any values. It throws up a syntax error on the "conn.Execute" line.
Sub ConnectToDataBase()
'
'
Dim conn As New ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
'
Dim i As Long ' counter
Dim SQLStr As String ' SQL to perform various actions
Dim table1 As String, table2 As String
Dim field1 As String, field2 As String
Dim rs As ADODB.Recordset
Dim vtype As Variant
'
Server_Name = "127.0.0.1" ' Enter your server name here -
Database_Name = "rmp" ' Enter your database name here
User_ID = "root" ' enter your user ID here
Password = "Password1" ' Enter your password here
Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver}" _
& ";SERVER=" & Server_Name _
& ";DATABASE=" & Database_Name _
& ";UID=" & User_ID _
& ";PWD=" & Password _
& ";OPTION=16427" ' Option 16427 = Convert LongLong to Int:
strSQL = "INSERT INTO parts(idParts, Part 1, Part 2, Part 3, Part 4,
Part 5) " & _
"VALUES (' " & 2 & " ' , '" & one & "' , '" & two & "' , '" &
three & "' , '" & four & "' , '" & five & "' )"
conn.Execute strSQL
Close connections
On Error Resume Next
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
On Error GoTo 0
End Sub
