Solved: sending data to access

[phill952000]

Hi,

i have a situation where i have some form fields in a word document and the information that is inserted into the form fields i want transfered into an existing database and the information to go into the correct fields in the database.

this is what i have so far and i will highlight the line of code that is throwing an error at runtime;


Sub Transfer()

'Transfer new shipping company record to

'Shippers table in Northwind database.

Dim connect As ADODB.Connection

Dim strConnection As String

Dim strexc As String

Dim strPath As String

Dim doc As Word.Document

Dim strCompany As String

Dim strContact As String

Dim strJobTitle As String

Dim strWorkNumber As String

Dim strMobile As String

Dim strFaxNumber As String

Dim strEmail As String

Dim strWebsite As String

Dim strDirectLine As String

Dim strHomeNumber As String

Dim strAddress1 As String

Dim strAddress2 As String

Dim strAddress3 As String

Dim strTown As String

Dim strCounty As String

Dim strPostalCode As String

Dim strSupply As String

Dim strPhoneSpeedDialNo As String

Dim strFaxSpeedDialNo As String

Dim bytContinue As Byte

Dim lngSuccess As Long

Set doc = ThisDocument

'On Error GoTo ErrHandler

strCompany = ActiveDocument.FormFields("Company").Result

strContact = ActiveDocument.FormFields("Contact").Result

strJobTitle = ActiveDocument.FormFields("JobTitle").Result

strWorkNumber = ActiveDocument.FormFields("WorkNumber").Result

strMobile = ActiveDocument.FormFields("Mobile").Result

strFaxNumber = ActiveDocument.FormFields("FaxNumber").Result

strEmail = ActiveDocument.FormFields("Email").Result

strWebsite = ActiveDocument.FormFields("Website").Result

strDirectLine = ActiveDocument.FormFields("DirectLine").Result

strHomeNumber = ActiveDocument.FormFields("HomeNumber").Result

strAddress1 = ActiveDocument.FormFields("Address1").Result

strAddress2 = ActiveDocument.FormFields("Address2").Result

strAddress3 = ActiveDocument.FormFields("Address3").Result

strTown = ActiveDocument.FormFields("Town").Result

strCounty = ActiveDocument.FormFields("County").Result

strPostalCode = ActiveDocument.FormFields("PostalCode").Result

strSupply = ActiveDocument.FormFields("Supply").Result

strPhoneSpeedDialNo = ActiveDocument.FormFields("PhoneSpeedDialNo").Result

strFaxSpeedDialNo = ActiveDocument.FormFields("FaxSpeedDialNo").Result

'Confirm new record.

bytContinue = MsgBox("Do you want to insert this record?", vbYesNo, "Add Record")

Debug.Print bytContinue

'Process input values.

If bytContinue = vbYes Then

strexc = "insert into MGM Address Book " _
& "(Company, Contact, JobTitle, Work Number, Mobile, Fax Number, E-mail, Website, " _
& "Direct Line, Home Number, Address1, Address2, Address3, Town, County, PostalCode, " _
& "Supply, Phone Speed Dial No, Fax Speed Dial No)" _
& "VALUES (" _
& strCompany & ", " _
& strContact & ", " _
& strJobTitle & ", " _
& strWorkNumber & ", " _
& strMobile & ", " _
& strFaxNumber & ", " _
& strEmail & ", " _
& strWebsite & ", " _
& strDirectLine & ", " _
& strHomeNumber & ", " _
& strAddress1 & ", " _
& strAddress2 & ", " _
& strAddress3 & ", " _
& strTown & ", " _
& strCounty & ", " _
& strPostalCode & ", " _
& strSupply & ", " _
& strPhoneSpeedDialNo & ", " _
& strFaxSpeedDialNo & ")"

Debug.Print strexc

'Substitute path and connection string with DSN if available.

strPath = "C:\Documents and Settings\Paul.MGMPLC\My Documents\word to access transfer\MGM ADDRESS BOOK.mdb"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & strPath

Debug.Print strConnection

Set connect = New ADODB.Connection

connect.Open strConnection

connect.Execute strexc, lngSuccess "this line is throwing an error"

connect.Close

MsgBox "You inserted " & lngSuccess & " record", vbOKOnly, "Error Added"

End If

Set doc = Nothing

Set connect = Nothing

Exit Sub

'ErrHandler:

'MsgBox Err.Number & ": " & Err.description, _
'vbOKOnly, "Error"

'On Error GoTo 0

'On Error Resume Next

'cnn.Close

'Set doc = Nothing

'Set cnn = Nothing

End Sub

i have been over this code a few times and cannot devise what is causing this error.

any help is much appriciated

regards

phill952000

EDIT: Tommy Added VBA code tags.

[CreganTur]

Please wrap your code in VBA tags- click the green VBA button. This will format the code according to VBIDE and make it much easier to read. Please do this if you don't want people to skip your question because it's not as easy to read the code.

Also, please tell us exactly what error message you are getting.

[phill952000]

Hi,

i have a situation where i have some form fields in a word document and the information that is inserted into the form fields i want transfered into an existing database and the information to go into the correct fields in the database.

this is what i have so far and i will highlight the line of code that is throwing an error at runtime;

Sub Transfer()

'Transfer new shipping company record to

'Shippers table in Northwind database.

Dim connect As ADODB.Connection

Dim strConnection As String

Dim strexc As String

Dim strPath As String

Dim doc As Word.Document

Dim strCompany As String

Dim strContact As String

Dim strJobTitle As String

Dim strWorkNumber As String

Dim strMobile As String

Dim strFaxNumber As String

Dim strEmail As String

Dim strWebsite As String

Dim strDirectLine As String

Dim strHomeNumber As String

Dim strAddress1 As String

Dim strAddress2 As String

Dim strAddress3 As String

Dim strTown As String

Dim strCounty As String

Dim strPostalCode As String

Dim strSupply As String

Dim strPhoneSpeedDialNo As String

Dim strFaxSpeedDialNo As String

Dim bytContinue As Byte

Dim lngSuccess As Long

Set doc = ThisDocument

'On Error GoTo ErrHandler

strCompany = ActiveDocument.FormFields("Company").Result

strContact = ActiveDocument.FormFields("Contact").Result

strJobTitle = ActiveDocument.FormFields("JobTitle").Result

strWorkNumber = ActiveDocument.FormFields("WorkNumber").Result

strMobile = ActiveDocument.FormFields("Mobile").Result

strFaxNumber = ActiveDocument.FormFields("FaxNumber").Result

strEmail = ActiveDocument.FormFields("Email").Result

strWebsite = ActiveDocument.FormFields("Website").Result

strDirectLine = ActiveDocument.FormFields("DirectLine").Result

strHomeNumber = ActiveDocument.FormFields("HomeNumber").Result

strAddress1 = ActiveDocument.FormFields("Address1").Result

strAddress2 = ActiveDocument.FormFields("Address2").Result

strAddress3 = ActiveDocument.FormFields("Address3").Result

strTown = ActiveDocument.FormFields("Town").Result

strCounty = ActiveDocument.FormFields("County").Result

strPostalCode = ActiveDocument.FormFields("PostalCode").Result

strSupply = ActiveDocument.FormFields("Supply").Result

strPhoneSpeedDialNo = ActiveDocument.FormFields("PhoneSpeedDialNo").Result

strFaxSpeedDialNo = ActiveDocument.FormFields("FaxSpeedDialNo").Result

'Confirm new record.

bytContinue = MsgBox("Do you want to insert this record?", vbYesNo, "Add Record")

Debug.Print bytContinue

'Process input values.

If bytContinue = vbYes Then

strexc = "insert into MGM Address Book " _
& "(Company, Contact, JobTitle, Work Number, Mobile, Fax Number, E-mail, Website, Direct Line," _
& " Home Number, Address1, Address2, Address3, Town, County, PostalCode, Supply, Phone Speed Dial No," _
& " Fax Speed Dial No)" _
& "VALUES (" _
& strCompany & ", " _
& strContact & ", " _
& strJobTitle & ", " _
& strWorkNumber & ", " _
& strMobile & ", " _
& strFaxNumber & ", " _
& strEmail & ", " _
& strWebsite & ", " _
& strDirectLine & ", " _
& strHomeNumber & ", " _
& strAddress1 & ", " _
& strAddress2 & ", " _
& strAddress3 & ", " _
& strTown & ", " _
& strCounty & ", " _
& strPostalCode & ", " _
& strSupply & ", " _
& strPhoneSpeedDialNo & ", " _
& strFaxSpeedDialNo & ")"

Debug.Print strexc

'Substitute path and connection string with DSN if available.

strPath = "C:\Documents and Settings\Paul.MGMPLC\My Documents\word to access transfer\MGM ADDRESS BOOK.mdb"

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = " & strPath

Debug.Print strConnection

Set connect = New ADODB.Connection

connect.Open strConnection

connect.Execute strexc, lngSuccess "this line is throwing an error"

connect.Close

MsgBox "You inserted " & lngSuccess & " record", vbOKOnly, "Error Added"

End If

Set doc = Nothing

Set connect = Nothing

Exit Sub

'ErrHandler:

'MsgBox Err.Number & ": " & Err.description, _
'vbOKOnly, "Error"

'On Error GoTo 0

'On Error Resume Next

'cnn.Close

'Set doc = Nothing

'Set cnn = Nothing

End Sub

i have been over this code a few times and cannot devise what is causing this error.

the code asks me if i want to add this record and when i click yes i get the following error message:


run-time error '-2147217900 (80040e14)'
syntax error in INSERT INTO statement.


any help is much appriciated

regards

phill952000

EDIT: TOMMY Fixed the wide page

[CreganTur]

run-time error '-2147217900 (80040e14)'
syntax error in INSERT INTO statement.


As the error states, the problem is a syntax error in your INSERT INTO statement.

At first blush, I believe the error is caused by the fact that you have field names with spaces in them. Whenever you have field names with spaces, you must encase them in brackets [].

If that doesn't fix your issue, then run your code again, and when you get the error message click on the Debug button. They type "?strexc" into your Immediate Window- this will show you your total SQL string. Take a close look at it and see if you can spot the syntax error.

For the future, you need to know that having spaces in your table and field names is bad practice. If you have to have space between words, use the underscore character. For example: "Data Field" is bad practice, but "DataField", "dataField", and "Data_Field" are all variations of the name that conform to best practice.

HTH:thumb

[phill952000]

thanks for your reply CreganTur,

i have encased the field names in brackets[] but the error still persists.

i understand that having spaces in field names in access table are bad practice and unfortunatly i am trying to send data from word to an access database that i did not design so it is a bit frustrating.

any other ideas about this error? as i cannot find anything wrong with my syntax. maybe i cannot see the forest for the trees.

cheers

[phill952000]

i have sorted it now, i needed to put the table name in brackets aswel as there is spaces in that aswel.

thanks alot for your help