Results 1 to 5 of 5
  1. #1
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    813

    VBA *batch* routine to import tables (NOT using a stored procedure as part of "External Data")

    Experts:

    In an existing routine, I import XLSX files via a simple VBA routine. That is, I merely browse to the folder containing the desired spreadsheets and click 'Ok'. While this process works great, I would like to now adjust so that I can import ACCDB tables in a similar fashion.

    That is, preferably, I do NOT want to use a "stored process" (under the *External Data* ribbon) since I will store the local folder path (of my computer). Furthermore, the "Get External Data" allows me to import from only one (1) DB file at the time (vs. a batch routine of every ACCDB in the source folder).



    Instead, just like now with the Excel routine, I would like to bring up the "Browse Dialogue" and then point to the folder.

    The attached zip file contains the following:
    1. Folder "Import_Files" includes 2 XLSX and 2 ACCDB files.
    2. Also, attached DB "Import_Routine" includes a single form object "F01_MainMenu".

    Current Excel Process:
    1. I open the form and click on "Import Data"
    2. I then browse to folder "Import_Files" and click "OK".
    3. Now, both spreadsheets are imported and their table names are derived from their tab names + prefix "01_tbl_". For example, XLS file #1 with tab name = "Source_1" is imported as "01_tbl_Source_1".

    New Access Process:
    - Just like with the Excel routine, I want to click on the form's command button "Import Access Tables".
    - ... then browse to the same folder...
    - and then, I want the tables be imported as "01_tbl_Source_3" and "01_tbl_Source_4" for DB files File_3 and File_4, respectively.

    ... that's pretty much it! Again, my preference is NOT to use a stored/saved procedure from the "External Data" ribbon.

    That said, what VBA would allow me to mimic the existing VBA routine but import the ACCDB tables vs. XLSX tabs? That is, the VBA routine would have to scan through any ACCDB file in the source folder an import **all tables** from **all files** via a single click.

    Thank you for any assistance in advance,
    Tom
    Attached Files Attached Files

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,107
    You can connect to any external access database from the current database using something like

    Code:
    Dim OtherDb as DAO.Database
    Dim rs as DAO.Recordset
    
    Set OtherDb = Opendatabase("PATH AND FILENAME TO DB.mdb")
    
    Set rs = OtherDb.Openrecordset("NAME OF TABLE")


    So if you open the database you should be able to then get the table names into a recordset and loop around them.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,729
    Here you are Tom, should do what you need.

    I used a slightly different method by setting up a hidden Access application instance to open each file and looped through the tabledefs to get the names of the tables to be imported. I have not checked to see what happens if there is a name conflict as I don't know what you would like to do in that case. Maybe prefix the tables with the file name ("File_3_01_tbl_" instead of just "01_tbl_")?

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    skydivetom is offline Expert
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    813
    Vlad:

    YOU ARE T-H-E MAN !!!!!!!

    Your solutions are always *brilliant* and do EXACTLY what the requirements (posted in original thread) outline.

    10,000 thanks... AGAIN!

  5. #5
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,729
    You're welcome Tom!
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 24
    Last Post: 06-25-2021, 06:08 AM
  2. Replies: 28
    Last Post: 03-23-2021, 11:02 AM
  3. Replies: 4
    Last Post: 11-14-2019, 11:30 AM
  4. Replies: 13
    Last Post: 01-30-2013, 03:05 PM
  5. Replies: 16
    Last Post: 07-22-2011, 09:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums