Comment puis-je obtenir les résultats d'une requête d'Access dans Excel s'il a un UDF?
Je reçois le message d'erreur suivant: " Erreur d'exécution '3085': Fonction non définie 'XXXX' dans l'expression ". L'erreur se produit lors de l'ouverture d'un jeu d'enregistrements (requête d'accès) à partir d'Excel VBA. La requête en cours d'ouverture a une fonction définie par l'utilisateur (UDF) qui déclenche l'erreur.
Le code est dans Excel Office 365. La requête est dans Access Office 365.
J'ai a utilisé avec succès la requête appelée (et d'autres avec les UDF) pendant environ douze mois, et "soudainement" elle ne fonctionne plus. J'ai recherché et testé de nombreuses options sur Google sans succès.
La plupart des discussions disent que cela ne peut pas être fait, ou de ne pas utiliser un udf mais essayez un intégré qui fonctionne. Je conteste ces réponses parce que cela a fonctionné auparavant. Le principal udf que j'utilise est celui appelé "iMax" qui est écrit dans d'autres articles. Il fonctionne comme max () dans Excel. ( Pas de fonction max (x, y) dans Access ) p>
J'ai aussi vu des threads qui suggèrent d'exécuter ceci en deux étapes: 1 - changer la requête en une requête de création de table. 2 - tirez les résultats du tableau dans Excel. Alors que je pourrais peut-être m'en tirer (après beaucoup de retouches), cela me conduirait à créer de nombreuses tables temporaires avec des milliers et des milliers de lignes et ne semble pas très lisse.
J'ai compilé vba et compacté la base de données sans impact sur mon problème.
J'ai longtemps créé une base de données factice avec une simple fonction publique udf qui retournait le numéro 1, une simple requête qui renvoie trois enregistrements et un champ pour la fonction résultats. Cela génère la même erreur lors de l'extraction dans Excel.
Sub RunQuery()
Dim MyDatabase As dao.Database
Dim qdf As dao.QueryDef
Dim rs As dao.Recordset
Dim qryname As object
Dim SheetName As String
Set MyDatabase = DBEngine.OpenDatabase _
("SomePath\SomeFilename.accdb")
For Each qryname In Range("SomeRange")
Set rs = MyDatabase.OpenRecordset(qryname) '<<<ERROR IS HERE
SheetName = "SomeSheetName"
With Sheets(SheetName)
.ListObjects(SomeTableName).DataBodyRange.Rows.ClearContents
.Range("A2").CopyFromRecordset rs
End With
Set rs = Nothing
Set qdf = Nothing
Next qryname
End Sub
Pour toutes les requêtes de la boucle For qui n'ont pas d'UDF, les résultats sont extraits et vidés dans une série de tableaux dans Excel. Toute requête avec une erreur udf au niveau du "Set rs = Mydatabase.OpenRecordset (qryname)
3 Réponses :
La plupart des discussions disent que cela ne peut pas être fait,
et ils ont raison.
Votre seule option est d'utiliser l'automatisation pour ouvrir une instance d'Access et, dans ce cadre, exécuter la requête.
Gustav - Cela a fonctionné pour moi et pour d'autres au bureau pendant de nombreux mois sur la même requête. Pourquoi cela aurait-il fonctionné auparavant et soudainement pas maintenant? Et peut-être en guise de suivi avez-vous une suggestion sur l'automatisation? Je devrai google, mais toute aide à ce sujet serait appréciée.
Je ne peux pas dire pourquoi cela a fonctionné, cela ne devrait pas. @HansUp a gentiment fourni l'exemple.
Si vous exécutez la requête dans une session d'application Access, comme l'a suggéré Gustav, le service d'expression peut gérer l'UDF dans votre requête.
Voici un extrait de code Excel VBA testé rapidement qui extrait les données d'une requête qui inclut un UDF :
Const cstrDbFile As String = "C:\share\Access\Database2.accdb"
Dim objAccess As Object
Dim rs As Object
Dim ws As Worksheet
Dim strSelect As String
Set objAccess = CreateObject("Access.Application")
objAccess.Visible = True ' useful during testing '
objAccess.OpenCurrentDatabase "C:\share\Access\Database2.accdb"
strSelect = "SELECT ID, DummyFunction('a', '', 'c') FROM Dual;"
Set rs = objAccess.CurrentDb.OpenRecordset(strSelect)
If Not rs.EOF Then
Set ws = ThisWorkbook.Sheets("Sheet1")
ws.Range("A1").CopyFromRecordset rs
End If
rs.Close
objAccess.Quit
Merci HansUp. Cela a résolu mon problème. Il s'exécute sans donner l'erreur d'exécution.
Eh bien, comme indiqué, la plupart disent que cela ne devrait pas fonctionner.
Cependant, si vous êtes sûr à 100% que cela a fonctionné et a fonctionné en même temps?
Vous devez définir le "bac à sable" "mode du moteur de base de données JET (maintenant ACE).
Le service d'expression n'autorise normalement pas l'évaluation des fonctions VBA en tant que paramètre de sécurité pour empêcher l'injection SQL, ou le code s'exécutant en dehors d'Access pour permettre à SQL de s'exécuter + appeler les fonctions VBA. À un moment donné, cette fonctionnalité par défaut était "activée", mais maintenant la valeur par défaut est définie pour accéder uniquement.
Vous devez définir le dossier dans lequel l'application Access est approuvée. Cela devrait permettre aux fonctions VBA de fonctionner maintenant. donc, assurez-vous de définir le dossier comme approuvé.
Si l'emplacement (dossier) où votre application d'accès n'est PAS approuvé, Access utilisera le mode sandbox et VBA dans le SQL ne fonctionnera pas. p>
Si l'emplacement est approuvé, ALORS l'accès utilise le paramètre de registre de votre ordinateur.
Je parie que l'emplacement n'est pas fiable - vous obtenez donc toujours le mode bac à sable pour SQL dans Access.
Si vous êtes sûr à 100% que l'emplacement du dossier est défini comme approuvé dans Accédez, et vous recevez toujours les erreurs, vous devez alors modifier le paramètre de registre pour le mode «bac à sable» Access.
Le paramètre dans le registre est décrit ici: https://support.office.com/en-us/article/Turn-sandbox-mode-on-or-off-to-disable-macros-8CC7BAD8-38C2-4A7A-A604-43E9A7BBC4FB
Les paramètres de registre sont:
pour l'accès x32 bits:
Software\Microsoft\Office\ClickToRun\Registry\Machine\Software\ Wow6432Node\Microsoft\Office\16.0\Access Connectivity Engine\Engines
Ce qui précède est pour Office 2016 p>
14 = 2010
15 = 2013
16 = 2016
La valeur clé du mode sandbox est: 0 à 3
0 Le mode Sandbox est désactivé à tout moment.
1 Le mode Sandbox est utilisé pour Access, mais pas pour les programmes non Access.
2 Le mode Sandbox est utilisé pour les programmes non Access, mais pas pour Access.
3 Le mode Sandbox est utilisé à tout moment. Il s'agit de la valeur par défaut, définie lorsque vous installez Access
Donc, ci-dessus, vous voulez un paramètre de 0.