Importation de Base de données MS Access Requête dans Excel en utilisant VBA sans Invite de Connexion
Je suis en train d'importer un MS Access requête dans excel sans déclencher le journal invite. J'ai tenté cette opération un peu de façons différentes, mais les deux méthodes n'ont pas donné moi une solution complète.
Détails:
- Mon accès de la source de requête n'est pas protégée accès au fichier de base de données (database 1.accdb) construit en MS Access 2010. Cette base de données obtient des tables de différentes sources (par utilisation de tables liées) et effectue le traitement des données. L'une de ces sources requiert un mot de passe, donc, quand je lance la requête, d'un journal à l'invite de vient de me demander des informations d'identification (que j'ai). Je n'ai pas de problèmes avec la requête elle-même.
- Ma feuille de calcul excel (intégré dans excel 2010) contient le code VBA qui récupère les tables à partir d'autres sources de données et certains d'entre eux nécessitent une authentification, donc j'ai construit une invite personnalisée qui permet à un utilisateur d'entrer des informations d'identification pour toutes les tables.
Le problème ici c'est que j'ai une invite à venir dans la feuille de calcul excel qui demande à l'utilisateur pour l'ouverture de session, mais alors une autre invite apparaît lorsque la requête access est importé. Voici ce que j'ai essayé de faire pour traiter le problème:
Méthode 1: Utiliser l'Enregistreur de Macros:
J'ai utilisé excel intégré dans l'enregistreur de macro pour suivre mes étapes manuelles dans l'importation de la requête access. Quand je suis à l'enregistrement de la macro, les importations d'œuvres et que la requête vient avec pas d'erreurs comme prévu. Cependant, lorsque j'essaie d'exécuter la macro, j'obtiens une erreur à l'exécution:
"Run-time error '1004':
The query did not run, or the database could not be opened. Check the database
server or contact your database administrator. Make sure the external database
is available and has not been moved or reorganized, then try the operation
again."
Code de l'Enregistreur de Macro:
Sub Macro2()
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;" _
, "Data Source=C:\Database1.accdb;Mode=Share Deny Write;" _
, "Extended Properties="""";Jet OLEDB:System database="""";" _
, "Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";" _
, "Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;" _
, "Jet OLEDB:Global Partial Bulk Ops=2;" _
, "Jet OLEDB:Global Bulk Transactions=1;" _
, "Jet OLEDB:New Database Password="""";" _
, "Jet OLEDB:Create System Database=False;" _
, "Jet OLEDB:Encrypt Database=False;" _
, "Jet OLEDB:Don't Copy Locale on Compact=False;" _
, "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;" _
, "Jet OLEDB:Support Complex Data=False;" _
, "Jet OLEDB:Bypass UserInfo Validation=False"), _
Destination:=Range("$A$4")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array("Query3")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\Database1.accdb"
.ListObject.DisplayName = "Table_Database1"
.Refresh BackgroundQuery:=False
End With
Range("I3").Select
End Sub
Je pense que la raison pour laquelle cette macro ne fonctionne pas (mais les étapes manuelles faire) c'est parce que certains paramètres sont ignorés par l'enregistreur. Si j'ai supprimé les citations de certains des champs de mot de passe, le code n'a pas d'erreur, mais je reçois le journal invite de nouveau. J'espérais que quelqu'un ici peut voir si il y a un paramètre manquant ou incorrect paramètre assigné.
Méthode 2: Utilisation de la Bibliothèque de DAO:
Pour cette méthode, j'ai dû faire quelques modifications. J'ai d'abord eu pour ajouter une référence à mon rédacteur en chef pour "Microsoft DAO 3.6 Object Library". Ensuite, j'ai eu pour convertir mes .fichier accdb à un .fichier mdb si je peux utiliser le DAO fonctions:
Code pour la Méthode DAO:
Sub Macro3()
Dim db1 As Database
Dim db2 As Database
Dim recSet As Recordset
Dim strConnect As String
Set db1 = OpenDatabase("C:\Database1.mdb")
strConnect = db1.QueryDefs("Query3").Connect _
& "DSN=myDsn;USERNAME=myID;PWD=myPassword"
Set db2 = OpenDatabase("", False, False, strConnect)
db2.Close
Set db2 = Nothing
Set recSet = db1.OpenRecordset("Query3")
With ActiveSheet.QueryTables.Add(Connection:=recSet, Destination:=Range("$A$4"))
.Name = "Connection"
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
recSet.Close
db1.Close
Set recSet = Nothing
Set db1 = Nothing
End Sub
Cette méthode fonctionne et je peux contourner la base de données du journal invite... aussi longtemps que ma requête ne retourne pas une grande quantité de documents. Quand j'étais de retour à ~60 000 dossiers, le code devrait pas prendre plus de 5 à 10 secondes pour obtenir un résultat. Cependant, quand j'ai essayé de tirer plus de ~100 000 notices, excel ne répondait plus et accrocher (je laisse le code à exécuter pendant environ 10 minutes avant, j'ai arrêté). Je pense que j'ai atteint une certaine limitation du DAO, d'autres que je ne peux pas trouver de la documentation qui porte sur cette question.
Toute aide est appréciée.
OriginalL'auteur user2465349 | 2013-06-20
Vous devez vous connecter pour publier un commentaire.
Essayez ceci :
OU...dans ce cas, vous devez écrire votre requête dans la fenêtre VBA
CopyFromRecordset
méthodeComme une mise à jour, je crois que j'ai trouvé pourquoi je ne peux pas utiliser le
CopyFromRecordset
méthode correctement. Lorsque j'essaie d'exporter une de mes grandes tables pour un classeur excel, MS Access renvoie un message disant que j'ai mis plus de disques que je peux monter sur le presse-papiers et m'indique le nombre maximum est de 65 000 habitants. Ce message correspond étroitement avec ce que je vivais avant, quand Excel peut se bloquer lorsque j'ai rapporté plus de ~de 60 000. Le message a également suggéré que je casse ma table en petits groupes ou de limiter le nombre de disques que j'ai à l'exportation. Suis-je en mesure de limiter le nombre de documentsCopyFromRecordset
retourne?Salut, Lorsque vous copiez des données du jeu d'enregistrements à l'aide de méthode copyfromrecordset...vous pouvez spécifier maxrecords il y a aussi e.g ThisWorkbook.Worksheets("Feuil1").Range("A4").CopyFromRecordset daoRcd,65536 ce que vous avez besoin est juste une boucle par le jeu d'enregistrements jusqu'expressions du FOLKLORE. J'espère qu'il va travailler pour vous
ThisWorkbook.Worksheets("Sheet1").Range("A4").CopyFromRecordset daoRcd,65536
Nice un Transformateur. C'est beaucoup plus élégante que la façon dont je le faisais avant. Je suis passer sur votre méthode maintenant!
OriginalL'auteur Transformer
J'ai fait quelques recherches et a été en mesure de me sortir de ce trou. La raison pour laquelle excel peut se bloquer lors de l'utilisation de la
CopyFromRecordset
méthode est parce que j'ai été essayer d'apporter plus de 65 000 enregistrements à la fois. Apparemment, MS Access n'a pas suivi excel lors de sa limite d'enregistrement a été augmenté, passant de 65 000 à 1 000 000 enregistrements.Ce que j'ai fait pour une solution de contournement est d'ouvrir la requête et récupérer les petits morceaux de dossiers (<=65 000 personnes) à la fois en utilisant une boucle. Le code qui a fonctionné pour moi est indiqué ci-dessous.
Je tiens à noter quelques choses je suis tombé sur le code du bâtiment:
OpenRecordset
méthode est importante, parce que les autres options (telles que dbOpenDynamic) pourrait plus que doubler le temps d'exécution en fonction du nombre d'opérations sont.CopyFromRecordset
méthode de ne pas ramener les en-têtes de champ automatiquement, donc j'ai ajouté une boucle pour ce faire à l'avance.CopyFromRecordset
méthode ne donner à l'utilisateur une indication si le processus est terminé ou pas, j'ai donc ajouté de la période d'état de la barre des messages à l'aide de laApplication.StatusBar
propriété.En résumé, ce code me permet d'éliminer efficacement MS Access de me donner un journal invite quand j'essaie d'importer une requête Access dont la source est protégée. Ce n'est pas la même protection que l'on retrouve dans le .mdb fichier lui-même (qui peut être spécifié dans la chaîne de connexion dans le fichier).
OriginalL'auteur user2465349