Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Unanswered: Run a single Query on Multiple Access Database

    Hi Experts,

    I am a newbie to Access. I have about 300 mdb files all with the same name, same tables under different subfolders. for example:

    H:\Southern\folder1\database.mdb
    H:\Southern\folder2\database.mdb
    H:\Southern\folder3\database.mdb
    .
    .
    .
    .
    .
    .
    H:\Southern\folder300\database.mdb

    now i have a query named "exportquery". It joins a few tables from lets say in first database and extracts a few columns and then there is a macro that will export result dataset to excel.

    I want to programatically automate this process over all the 300 mdb files. I mean can run the query and export it to excel sheets by writing a macro. so the macro would have to:

    1)Search for mdb files
    2)open one mdb file run the query, export the result and close
    3)open another mdb file run the query, export the result and close.

    Hope my question made sense.

    looking forward for some valuable suggestions.

    Thanks and regards,
    inakul.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    300 dbs? ...ouch. this is the problem. it will force you to build a real kludge...


    append the records of all the dbs (identical table) into a single table. add a new field to the table and be sure to append the differentiating name i.e. folder2 ...so you know will always know where each record came from.

    then as you create new dbs - which I presume you are condemn to be doing - just append that data to your consolidated db as you go.....

    one person's opinion. hope it helps....
    www CahabaData com

  3. #3
    Join Date
    Nov 2011
    Posts
    2
    thanks maite...

    anymore suggestions??? think we can do a directory search/file search and pass that as a string to open each DB in a loop to get done what needs to be done??

    but i just know how to go about it..

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The following procedure receives the name of an Access database and execute a query that imports the contents of a table in this external database named "Tbl_Modules" into a table with the same name ("Tbl_Modules") in the current database:
    Code:
    Sub ExecQuery(ByVal DatabaseName As String)
    
        Const c_SQL As String = "INSERT INTO Tbl_Modules ( Module_Name, Module_Description, Granted, Disabled ) " & _
                                "SELECT Module_Name, Module_Description, Granted, Disabled " & _
                                "FROM Tbl_Modules IN '@DB';"
        
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef("")
        With qdf
            .SQL = Replace(c_SQL, "@Db", DatabaseName)
            .Execute
            .Close
        End With
        Set qdf = Nothing
        
    End Sub
    If you know how to retrieve the name of the external Access databases in a loop, you can call this function for each found name. You just need to modify the c_SQL constant that contains the SQL statement of the query.

    Example of use:
    Code:
    ExecQuery "C:\Users\Sinndho\Documents\Access\Credentials\Credentials.mdb"
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •