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:

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