Error on INSERT INTO and .Execute

[Greg Maxey]

I am very green at this stuff so if the answer should be obvious then
please be understanding ;-)

I am try to run the following bit of code in a Word macro. I am
getting an error on the line"

vConnection.Execute pSQL

The error states "No value given for one or more required parameters.

What am I missing? I have a simple database in Access with three
fields "Test1, Test2, and Test3." I am simply trying to write the
TestText1, 2, 3 to the fields. Ultimately I want to write Word field
code in the database fields, but I can't get passed this simple test.

Thanks.

Sub Testing()
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim pSQL As String
Dim oTest As String
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=E:\My
Documents\Batch\TestDataBase2.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
vConnection.Execute "DELETE * FROM MyTable"

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(TestText1,
TestText2, TestText3)"
vConnection.Execute pSQL
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub

 

[Dirk Goldgar]

I am very green at this stuff so if the answer should be obvious then
please be understanding ;-)

I am try to run the following bit of code in a Word macro. I am
getting an error on the line"

vConnection.Execute pSQL

The error states "No value given for one or more required parameters.

What am I missing? I have a simple database in Access with three
fields "Test1, Test2, and Test3." I am simply trying to write the
TestText1, 2, 3 to the fields. Ultimately I want to write Word field
code in the database fields, but I can't get passed this simple test.

Thanks.

Sub Testing()
'Requires reference to MS ActiveX Data Objects 2.8 Library
Dim vConnection As New ADODB.Connection
Dim vRecordSet As New ADODB.Recordset
Dim pSQL As String
Dim oTest As String
Application.ScreenUpdating = False
'Provide connection string for data using Jet Provider for Access
database
vConnection.ConnectionString = "data source=E:\My
Documents\Batch\TestDataBase2.mdb;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;"
vConnection.Open
vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
vConnection.Execute "DELETE * FROM MyTable"

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES(TestText1,
TestText2, TestText3)"
vConnection.Execute pSQL
vRecordSet.Update
vRecordSet.Close
vConnection.Close
Set vRecordSet = Nothing
Set vConnection = Nothing
Application.ScreenUpdating = True
End Sub

You need to let it know that "TestText1", "TestText2", and "TestText3"
are literal values, not parameters. Since it doesn't recognize these
names as field names, it assumes they must be parameters.

Try this:

pSQL = _
"INSERT INTO MyTable(Test1, Test2, Test3) " & _
"VALUES('TestText1', 'TestText2', 'TestText3')"

 

[Greg Maxey]

Dirk,

Yes that works thank you. Now a new problem, I actually want to insert
results from a document formfield:

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3)
VALUES('ActiveDocument.Formfields("Text1").Result', 'TestText2',
'TestText3')"

I can't seem to work out how to define the "Field result" as a value in the
pSQL statement. If you can help further it would be great. Thanks.

 

[Van T. Dinh]

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3)
VALUES('ActiveDocument.Formfields(""Text1"").Result', 'TestText2',
'TestText3')"

i.e. double the double-quote inside a double-quote-delimted String to get a
double-quote.

 

[RoyVidar]

Dirk,

Yes that works thank you. Now a new problem, I actually want to
insert results from a document formfield:

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3)
VALUES('ActiveDocument.Formfields("Text1").Result', 'TestText2',
'TestText3')"

I can't seem to work out how to define the "Field result" as a value
in the pSQL statement. If you can help further it would be great.
Thanks.

What you are doing now, is to include the *references* into the string,
I think you'll need to include the *values*

pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) " & _
"VALUES ('" & ActiveDocument.Formfields("Text1").Result & "', '" & _
& ActiveDocument.Formfields("Text2").Result & "', '" & _
& ActiveDocument.Formfields("Text3").Result & "')"

Do a debug.print pSQL, and check how the values of the form fiels now
(is hopefully) within the string, not the references.

You may need to do some validation first, and, should the form fields
contain singlw quotes, you'd need to double them up, to have the db
accept them

& replace(ActiveDocument.Formfields("Text1").Result, "'", "''") &
"',...

 

[Greg Maxey]

Roy,

Thanks. This is what seems to work:
pSQL = "INSERT INTO MyTable(Test1, Test2, Test3) VALUES ('" &
ActiveDocument.FormFields("Text1").Result & "', '" &
ActiveDocument.FormFields("Text2").Result & "', '" &
ActiveDocument.FormFields("Text3").Result & "')"

 

[Greg Maxey]

Van,

That got me off the dime and Roy's help pushed me across the finish line.
Thank you.
Greg Maxey