2501 error on DoCmd.OpenQuery (1 Viewer)

Morten · Feb 17, 2014

I have an Excel template with some macros, which is executing different queries in an Access database stored on a network shared drive.

The first macro creates a table in the Access database based on a linked table. It has always worked fine, but suddenly one of my colleagues gets this error:

Run-time error '2501':
OpenQuery-handling was cancelled.

I then click debug and I can see the vba script stops, when it's trying to run this part of the script.

'Run Query
.DoCmd.OpenQuery ("q_tblUdtræk2")

Code:

Global Const sBaseFile As String = "\\rghfi001\region\SAP-REFLEX\System\TabelKilder\dbMDMDubletter.accdb"
Sub GenopfriskMDM()

'Denne forespørgsel opretter en ny tabel i ACCESS. Kataloglinjer i tabel er udtaget ved følgende
'parametre: Åben+Indgående katalog, Købbare og ikke Rigshospitals kontrakt.

Dim AppAcc, AppAccDatabase
Set AppAcc = CreateObject("Access.Application")
Application.DisplayAlerts = False

'Set Database Path & Name
AppAccDatabase = sBaseFile
'AppAccDatabase = "P:\KoncernOkonomi\indkobsafdelingen\ANALYSEAFDELINGEN\ACCESS\MDM Dublet tjek\Prod\dbMDMDubletter.accdb"

With AppAcc
'Open Database
.OpenCurrentDatabase AppAccDatabase

'Run Query
.DoCmd.OpenQuery ("q_tblUdtræk2")

End With
  
MsgBox "VBA \ MDM data genopfrisket"

End Sub

My colleague is having no trouble opening the Access database and run the queries. He only gets the error, when he tries to execute the macros in the Excel template. It works fine for me and a third colleague.

What can I do to solve the problem?

Best regards
Morten

pr2-eugin · Feb 17, 2014

Is everyone using the same version of Access?

Morten · Feb 17, 2014

pr2-eugin said:
Is everyone using the same version of Access?

Hi Paul,

Yes, everyone is using the same version of Access (Access 2010).
The problem first occurred when the IT department removed the folder with the Access database from one drive to another network shared drive.
We have tried to copy the Access database back to the old drive, so it looked like before, but we end up with the same error 2501.

Best regards

pr2-eugin · Feb 17, 2014

Is the Access DB split? If so has a Relink between the Front end and backend been performed again after the movement?

Check for the Query existence. Replace the front end with a new/working file.

Morten · Feb 17, 2014

pr2-eugin said:
Is the Access DB split? If so has a Relink between the Front end and backend been performed again after the movement?

Check for the Query existence. Replace the front end with a new/working file.

The Access DB is not split.

I'll try to make a new query and see if it solves the problem.

Right now I and another colleague can run queries both in Access and the Excel template without getting the error 2501.
Two other clleagues can run the queries in Access but not in the Excel template.
One of these colleagues tried to logon to another laptop and here it works perfect for him.

I have also checked references in alt+f11 and it's the same for all users.

gemma-the-husky · Feb 17, 2014

error 2501 would indicate the query failed to open correctly - try opening it directly.

the query presumably uses some functionality that is no longer working.

Morten · Feb 17, 2014

gemma-the-husky said:
error 2501 would indicate the query failed to open correctly - try opening it directly.

the query presumably uses some functionality that is no longer working.

None of our users have problems opening the query in the Access database, so we have tried that.
The problem is we have a made a template in Access, where we can work with the data from the query and some other queries. That Excel tool only work for some of our users.

gemma-the-husky · Feb 17, 2014

are you sure the message is this

OpenQuery-handling was cancelled.

can you check. Is it a select query, or an action query. If an action query, what sort of action query?

Morten · Feb 17, 2014

Thank you for your help.
I solved it by making a change in the macro settings.
I switched from "Disable all macros with notification" to "Enable all macros (not recommended; potentially dangerous code can run"

2501 error on DoCmd.OpenQuery (1 Viewer)

Morten

Registered User.

pr2-eugin

Super Moderator

Morten

Registered User.

pr2-eugin

Super Moderator

Morten

Registered User.

gemma-the-husky

Super Moderator

Morten

Registered User.

gemma-the-husky

Super Moderator

Morten

Registered User.

Similar threads

Users who are viewing this thread