Azk Azk - 6 months ago 20
SQL Question

Invalid SQL statement in VBA, Word form fields to Access db

I get an Invalid SQL statment error, when running this piece of code.

I got the code from here Word Forms to Acces where i have updated pieces to make it work for me.

My problem is where in my code shall i add the SQL statement to make it work correctly.

Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

strDocName = C:\Word file

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=N:\Direction\4-Signalliste\" & _
"Signal_liste.accdb;Jet OLEDB:Database Password=1234"

rst.Open "Order", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!CompanyID = doc.FormFields("CompanyID").Result
!swgNr = doc.FormFields("SWGNr").Result
!SWG_typical = doc.FormFields("SWG_typical").Result
!Signal_niveau = doc.FormFields("Signal_niveau").Result
!protocol = doc.FormFields("Protocol").Result
!Baud_rate = doc.FormFields("Baud_Rate").Result
!casdu = doc.FormFields("CASDU").Result
!casdu1 = doc.FormFields("CASDU1").Result
!casdu2 = doc.FormFields("CASDU2").Result
!IP_A = doc.FormFields("IP_A").Result
!IP_B = doc.FormFields("IP_B").Result
!IP = doc.FormFields("IP").Result
!gate = doc.FormFields("Gate").Result
!Subnet = doc.FormFields("Subnet").Result
!Link_addr = doc.FormFields("Link_addr").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Data Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup

End Sub

Answer

I created a similar situation in my PC and this code worked (just change connection, table name, and fields):

Private Sub CommandButton1_Click()

Dim appWord As Word.Application
Dim doc As Word.Document
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=F:\app\test.accdb"

rst.Open "Table1", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!Field1 = ActiveDocument.FormFields("Text1").Result
!Field2 = ActiveDocument.FormFields("Text1").Result
!Field3 = ActiveDocument.FormFields("Text1").Result
.Update
.Close
End With
'doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Data Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
    & "No data imported.", vbOKOnly, _
    "Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
    & "contain the required form fields. " _
    & "No data imported.", vbOKOnly, _
    "Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup

End Sub
Comments