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.

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 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.

Quote:

Originally Posted by sassy2009

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

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.

Run-time error 2501

re: Run-time error 2501

re: Run-time error 2501

re: Run-time error 2501

re: Run-time error 2501

re: Run-time error 2501

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about