Error on INSERT INTO and .Execute

  • Thread starter Thread starter Greg Maxey
  • Start date Start date
G

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
 
Greg Maxey said:
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
Click to expand...

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')"
 
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.
 
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.
 
Greg Maxey wrote in message said:
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.
Click to expand...


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, "'", "''") &
"',...
 
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 & "')"
 
Van,

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