By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Run-time error 2501

sassy2009
P: 15
Hello,

I am running an insert query from xl spreadsheet using the DoCmd.RunSQL to insert values from the spreadsheet into the Access database. When i run this query it gives an error saying " Run-time error 2501 RunSQL action was cancelled".
I know why this is happening and it's because of the message box that appears when an action query is run in Access which asks for a confirmation from the user "yes" or "no" buttons. But when i run the query from Spreadsheet, Access assumes "no" for the message box and cancels the RunSQL. How do I handle this error. If I ran the same query in Access itself it gives me the same "2501" error when I clicked on the "NO" button. Please help.....
Nov 3 '09 #1

✓ answered by topher23

Well, the reason the new code doesn't have the same issue is easy enough to be seen here. With the old code, you actually opened an instance of Access in order to enter your data, which triggered the confirmation dialog for the action query. In the new code, you are referencing your database as a data source without actually opening Access, thus bypassing any protections built into Access. Therefore, the new code doesn't trigger the confirmation dialog, since there is no instance of Access open for it to trigger from.

@sassy2009
Well, strike that stuff about dialog boxes, I just found your answer. When you have an open Excel workbook in Office 2007, and the workbook opens an Access instance, it apparently opens the database as read-only! No one on any other forum had any suggestions of how to make it not do this. So, the code you found is the only code that will work in this case, as it doesn't actually open Access.

Share this Question
Share on Google+
7 Replies


topher23
Expert 100+
P: 234
Your best bet is to turn off confirmation for action queries. Open an Access database, then go to Tools > Options. Click on the Edit/Find tab and, near the top left corner, you'll see the "Confirm" group. Uncheck "Action queries." In all of my projects, I only keep "Document deletions" checked.

If the problem is indeed that dialog box, this should take care of your issue.

Edit: FYI, what you are doing is called "pushing" the data. I generally use "pulling," in which I already know which Excel fields I want, so I run the code from Access and have it "pull" the data from the Excel spreadsheet. That way I don't have to have the Excel spreadsheet open in order to run my code, since I'm going to do my manipulation and reporting from Access anyway.
Nov 3 '09 #2

NeoPa
Expert Mod 15k+
P: 31,186
This is hard to answer as you fail to give much information as to what is running from where.

Generally, you won't see this message unless you've already seen the prompt for allowing the query to run. I suspect something is not as you say, yet what that may be is impossible to tell with so little detail in your question.

As a general rule though, there should be no need to change the settings as you currently choose to have them, for this issue.
Nov 3 '09 #3

sassy2009
P: 15
Hello,

This is my actual code in excel which is used to add new data to the table.
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()
  2.  
  3. Dim appaccess As Access.Application
  4. Dim dbstr As String
  5.  
  6. Set appaccess = GetObject("C:\Users\sassy\Documents\BillTracking.accdb", "Access.Application")
  7. appaccess.Visible = False
  8. appaccess.DoCmd.RunSQL "INSERT INTO tblWorkload VALUES ('JEN1','username', 100, #11/03/2009# )"
  9.  
  10.  
  11. End Sub
But if i run this query in access it works fine without any message box popping as i have unticked the confirm action queries from Access Options. But when i run from excel the 2501 error pops up and says RunSQL action was cancelled.

Please help.....
Nov 4 '09 #4

sassy2009
P: 15
Hello all,

I dont know what was wrong with my previous code which i posted last time. As far as i know if i find a way to handle the confirmation message from Access then my insert SQL statement should work fine. But i was going crazy as i couldnt fix this and jumped on youtube to watch some movie trailers. After a while i just searched for "integrating Excel and Access using VBA" on youtube and found this code which worked absolutely superbbbbbbbbbbb.

This is my new code and the values are getting updated into database without any problems. But still i cant find an answer why my previous insert statement says "RunSQL action was cancelled" and the new code doesnt.
Expand|Select|Wrap|Line Numbers
  1. Private Sub CommandButton1_Click()
  2.  
  3. Dim con As New ADODB.Connection
  4. Dim sql As String
  5.  
  6. With con
  7.     .Provider = "Microsoft.ACE.OLEDB.12.0"
  8.     .connectionString = "Data Source=C:\Users\sassy\Documents\BillTracking.accdb;"
  9.     .Open
  10. End With
  11.  
  12.  
  13. sql = "INSERT INTO tblWorkload VALUES ('JEN1','username', 500, #11/03/2009# )"
  14. con.Execute sql
  15. MsgBox "Values entered", vbInformation
  16. con.Close
  17. Set con = Nothing
  18.  
  19. End Sub
Nov 4 '09 #5

topher23
Expert 100+
P: 234
Well, the reason the new code doesn't have the same issue is easy enough to be seen here. With the old code, you actually opened an instance of Access in order to enter your data, which triggered the confirmation dialog for the action query. In the new code, you are referencing your database as a data source without actually opening Access, thus bypassing any protections built into Access. Therefore, the new code doesn't trigger the confirmation dialog, since there is no instance of Access open for it to trigger from.

@sassy2009
Well, strike that stuff about dialog boxes, I just found your answer. When you have an open Excel workbook in Office 2007, and the workbook opens an Access instance, it apparently opens the database as read-only! No one on any other forum had any suggestions of how to make it not do this. So, the code you found is the only code that will work in this case, as it doesn't actually open Access.
Nov 4 '09 #6

P: 1
I know it is an old thread, but I was happy to find it. Although no solution was mentioned it gave me an idea.

The error is due to the Application.SecurityAutomation that is not set to "low" on forehand.

If you never accessed the file manually then it is not a trusted document for you yet and the run time error appears.

Hence, 2 alternatives:
1. You manually open it, enable content and make it a trusted document and then do your thing in VBA.
2. Or: before you start the query, insert:
Expand|Select|Wrap|Line Numbers
  1. Set xApp is Object  'Late binding
  2. Set xApp = CreateObject("Access.Application")
  3. xApp.AutomationSecurity = msoAutomationSecurityLow
  4.  
  5. 'your query code goes here
  6.  
  7. xApp.AutomationSecurity = msoAutomationSecurityByUI
  8. 'or whatever it was before you started
Apr 11 '15 #7

NeoPa
Expert Mod 15k+
P: 31,186
There's no problem posting alternative or new answers to old threads. The problems only occur when a new poster wants to add their own question to the mix.

I'm pleased to see this was of some help triggering your thought processes :-)
Apr 12 '15 #8

Post your reply

Sign in to post your reply or Sign up for a free account.