INSERT INTO, eliminating confirmation prompt (2002, SP2)

Topic

New Reply

When I use the “INSERT INTO” SQL statement in combination with DoCmd.RunSQL to add a row to a table, for each row I add, I get the confirmation prompt “You are about to append 1 row(s) … Are you sure you want to do this?”

How do you get rid of the prompt? I seem to remember you can do it, but I’ve not been able to find out how to do it. I don’t want the database users to have to respond Yes to every row insertion I need to perform.

I’m using the following INSERT INTO statement:

SQL1 = “INSERT INTO tblCylinders(SampleID, CylinderNumber) VALUES([SampleID],’A’);”

Thanks,
Jim.

Reply | Quote

Replies

My original answer is below. Probably, you can accomplish the same task by just issuing a “docmd.setwarnings = false” before you go into your loop, and “docmd.setwarnings = true” after you’re finished.
thx
Pat

You are going to want to set confirm off for action queries. You can set that outside of VB (in your interface) by going to Tools-Options-Edit/Find Tab – Confirm – and click the “Action Queries” radio button. But you won’t want to do that for a regular program or for a program you give somone else, and you’ll certainly want to turn the action query confirm back on, too.

There are VB docmds that can set/reset the Tools-Options settings. I’m not sure exactly of the exact phrase to set action queries on and off (it was hard to find this consistently and easily in Access 97, much, much harder in Access XP) _but_ you can find it with a little looking for “constants.” Sorry I’m not more helpful, just pointing you in the general direction.

Reply | Quote

This works (I had to omit the equals sign). I’ll use this method for now, since I’ve not been able to figure out the Execute method.

Thank you.

Reply | Quote

Well, I’ve used it successfully for a long time, so, I’m glad you have found it useful! I also am looking at the help files for the execute method (DAO) right now (in my Access 97, which I keep for the Help Files). That just seems so classy. (You can look in the Help Files for “Set Options from Visual Basic,” too, if you want to get the full range of options you can set, beyond turning warnings on and off by “setwarnings.”)
thx,
Pat

Reply | Quote

Well, I’ve used it successfully for a long time, so, I’m glad you have found it useful! I also am looking at the help files for the execute method (DAO) right now (in my Access 97, which I keep for the Help Files). That just seems so classy. (You can look in the Help Files for “Set Options from Visual Basic,” too, if you want to get the full range of options you can set, beyond turning warnings on and off by “setwarnings.”)
thx,
Pat

Reply | Quote

This works (I had to omit the equals sign). I’ll use this method for now, since I’ve not been able to figure out the Execute method.

Thank you.

Reply | Quote

My original answer is below. Probably, you can accomplish the same task by just issuing a “docmd.setwarnings = false” before you go into your loop, and “docmd.setwarnings = true” after you’re finished.
thx
Pat

You are going to want to set confirm off for action queries. You can set that outside of VB (in your interface) by going to Tools-Options-Edit/Find Tab – Confirm – and click the “Action Queries” radio button. But you won’t want to do that for a regular program or for a program you give somone else, and you’ll certainly want to turn the action query confirm back on, too.

There are VB docmds that can set/reset the Tools-Options settings. I’m not sure exactly of the exact phrase to set action queries on and off (it was hard to find this consistently and easily in Access 97, much, much harder in Access XP) _but_ you can find it with a little looking for “constants.” Sorry I’m not more helpful, just pointing you in the general direction.

Reply | Quote

A simple way to do it that doesn’t require changing the SetWarnings setting is to not use DoCmd.RunSQL. Instead do something like this:

CurrentDB.Execute SQL1

Reply | Quote

>>CurrentDB.Execute SQL1

I can’t seem to get the syntax (or something else) right…

I get Run-time error ‘3601’, Too few parameters. Expected 1.

I found the “Execute Method Example” in help … it’s kind of curious to me. There are two SQL strings in that example … Change … and … Restore.

For the Change string, this is done:

strSQLChange = “UPDATE Employees SET Country = ” & _
“‘United States’ WHERE Country = ‘USA'”
[…]
Set qdfChange = dbsNorthwind.CreateQueryDef(“”, _
strSQLChange)
[…]
ExecuteQueryDef qdfChange, rstEmployees

But for the Restore string, only this is done (which matches the example you gave, i.e., no creating a query def first):

strSQLRestore = “UPDATE Employees SET Country = ” & _
“‘USA’ WHERE Country = ‘United States'”
[…]
dbsNorthwind.Execute strSQLRestore, dbFailOnError

Do I need to create that query def for this to work? But they don’t show a query def for the Restore SQL string, above. Hmmm.

Reply | Quote

Try this:

Dim dbsNorthwind As DAO.Database
Dim strSQLRestore As String

Set dbsNorthwind = CurrentDb

strSQLRestore = “UPDATE Employees SET Country = ” & _
“‘USA’ WHERE Country = ‘United States'”
[…]
dbsNorthwind.Execute strSQLRestore, dbFailOnError

Reply | Quote

Same thing. I get RTE 3061. “Too few parameters. Expected 1.”

Reply | Quote

Same thing. I get RTE 3061. “Too few parameters. Expected 1.”

Reply | Quote

Here’s the exact code I used:

Set dbsNorthwind = CurrentDb
SQL1 = “INSERT INTO tblCylinders(SampleID, CylinderNumber) VALUES([SampleID],’A’);”
dbsNorthwind.Execute SQL1, dbFailOnError

Reply | Quote

Create the query in the query grid. If it works without any errors copy the SQL from the View menu and paste it after SQL1 =

Reply | Quote

Create the query in the query grid. If it works without any errors copy the SQL from the View menu and paste it after SQL1 =

Reply | Quote

Tell me, what is [SampleID], where does this come from? If it’s a control on the form then you will have to use it like:

SQL1 = “INSERT INTO tblCylinders(SampleID, CylinderNumber) VALUES(” & [SampleID] & “,’A’);”

Reply | Quote

Tell me, what is [SampleID], where does this come from? If it’s a control on the form then you will have to use it like:

SQL1 = “INSERT INTO tblCylinders(SampleID, CylinderNumber) VALUES(” & [SampleID] & “,’A’);”

Reply | Quote

Here’s the exact code I used:

Set dbsNorthwind = CurrentDb
SQL1 = “INSERT INTO tblCylinders(SampleID, CylinderNumber) VALUES([SampleID],’A’);”
dbsNorthwind.Execute SQL1, dbFailOnError

Reply | Quote

Try this:

Dim dbsNorthwind As DAO.Database
Dim strSQLRestore As String

Set dbsNorthwind = CurrentDb

strSQLRestore = “UPDATE Employees SET Country = ” & _
“‘USA’ WHERE Country = ‘United States'”
[…]
dbsNorthwind.Execute strSQLRestore, dbFailOnError

Reply | Quote

>>CurrentDB.Execute SQL1

I can’t seem to get the syntax (or something else) right…

I get Run-time error ‘3601’, Too few parameters. Expected 1.

I found the “Execute Method Example” in help … it’s kind of curious to me. There are two SQL strings in that example … Change … and … Restore.

For the Change string, this is done:

strSQLChange = “UPDATE Employees SET Country = ” & _
“‘United States’ WHERE Country = ‘USA'”
[…]
Set qdfChange = dbsNorthwind.CreateQueryDef(“”, _
strSQLChange)
[…]
ExecuteQueryDef qdfChange, rstEmployees

But for the Restore string, only this is done (which matches the example you gave, i.e., no creating a query def first):

strSQLRestore = “UPDATE Employees SET Country = ” & _
“‘USA’ WHERE Country = ‘United States'”
[…]
dbsNorthwind.Execute strSQLRestore, dbFailOnError

Do I need to create that query def for this to work? But they don’t show a query def for the Restore SQL string, above. Hmmm.

Reply | Quote

And, here I was going to come back with further information on “getoption” and “setoption.”
Thanks for the exquisite line, “currentdb.execute sql1.” I’ll definitely start using that one, rather with dealing with the kludgey (in comparison) “setwarnings” wrapper!
thx
Pat

Reply | Quote

Actually, I usually create a temporary querydef and execute that, since the querydef object has a RecordsAffected property that you can test afterward to see whether the query was successful. In this case, that didn’t seem to be necessary.

Reply | Quote

Thank you, Charlotte!
Yes, since going into Help to get “edumacated” about the “execute” method, I found & read about that property, too. Recently, I decided I was getting too stuck in my Access ways, I had stopped learning, so have been visiting this forum a little more frequently. I am appreciative of all the things I have learned here!
thx
Pat

Reply | Quote

Thank you, Charlotte!
Yes, since going into Help to get “edumacated” about the “execute” method, I found & read about that property, too. Recently, I decided I was getting too stuck in my Access ways, I had stopped learning, so have been visiting this forum a little more frequently. I am appreciative of all the things I have learned here!
thx
Pat

Reply | Quote

Actually, I usually create a temporary querydef and execute that, since the querydef object has a RecordsAffected property that you can test afterward to see whether the query was successful. In this case, that didn’t seem to be necessary.

Reply | Quote

And, here I was going to come back with further information on “getoption” and “setoption.”
Thanks for the exquisite line, “currentdb.execute sql1.” I’ll definitely start using that one, rather with dealing with the kludgey (in comparison) “setwarnings” wrapper!
thx
Pat

Reply | Quote

A simple way to do it that doesn’t require changing the SetWarnings setting is to not use DoCmd.RunSQL. Instead do something like this:

CurrentDB.Execute SQL1

Reply | Quote