Run-time error 2501

[sassy2009]

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.....

[topher23]

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.

[NeoPa]

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.

[sassy2009]

Hello,

This is my actual code in excel which is used to add new data to the table.

Private Sub CommandButton1_Click()
 
Dim appaccess As Access.Application
Dim dbstr As String
 
Set appaccess = GetObject("C:\Users\sassy\Documents\BillTracking.accdb", "Access.Application")
appaccess.Visible = False
appaccess.DoCmd.RunSQL "INSERT INTO tblWorkload VALUES ('JEN1','username', 100, #11/03/2009# )"
 
 
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.....

[sassy2009]

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.

Private Sub CommandButton1_Click()
 
Dim con As New ADODB.Connection
Dim sql As String
 
With con
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .connectionString = "Data Source=C:\Users\sassy\Documents\BillTracking.accdb;"
    .Open
End With
 
 
sql = "INSERT INTO tblWorkload VALUES ('JEN1','username', 500, #11/03/2009# )"
con.Execute sql
MsgBox "Values entered", vbInformation
con.Close
Set con = Nothing
 
End Sub

[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.

[rfl62]

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:

Set xApp is Object  'Late binding
Set xApp = CreateObject("Access.Application")
xApp.AutomationSecurity = msoAutomationSecurityLow
 
'your query code goes here
 
xApp.AutomationSecurity = msoAutomationSecurityByUI
'or whatever it was before you started

[NeoPa]

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 :-)