Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    DoCmd.TransferDatabase with wildcard (*) to ignore last 2 characters in filename

    Experts:



    I'd like to get some assistance with modying a table import routine using VBA (NOT a "stored procedure" under the *External Data* ribbon).

    Attached zip file contains the following:
    1. Subfolder "Import_Data" includes a source file "020_ImportFile_07" (which contains table [01_Table]). ** Please note that my actual file includes additional objects (e.g., form, modules, etc.)
    2. DB "Import_With_Stored_Path" includes VBA to import table [01_Table] from file "020_ImportFile_07".

    Current Process:
    1. Open DB file "Import_With_Stored_Path"
    2. Open form "F01_MainMenu" (in VBA design mode) and update **DoCmd.TransferDatabase acImport** to reflect your folder path.
    3. Close/re-open form and click on command button "Import Table".

    Current Challenge:
    a. The last 2 digits (i.e., "07") in the filename "020_ImportFile_07" indicate the file's version... this version # will change over time.
    b. So, once the filename changes from, e.g., "020_ImportFile_07" to "020_ImportFile_08", the import routine (in DB "Import_With_Stored_Path") no longer works.

    My question:
    How would I modify the following VBA (with proper syntax)... ?

    ... from:
    Code:
    DoCmd.TransferDatabase acImport, "Microsoft Access", "YOUR_FOLDER_PATH\020_ImportFile_07.accdb", acTable, "01_Table", "01_Table", False
    ... to:
    Code:
    DoCmd.TransferDatabase acImport, "Microsoft Access", "YOUR_FOLDER_PATH\020_ImportFile_*.accdb", acTable, "01_Table", "01_Table", False
    That is, the wildcard of "_*" in the filename reference currently does NOT work in VBA. What's the correct syntax?

    Thank you,
    Tom
    Attached Files Attached Files

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm pretty certain you can't use a wildcard in that type of command, which DB would it open if there were more than one?
    Try it in the immediate window as a test.
    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
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Minty -- thank you for the feedback.

    Although there may be several ACCDB in that particular folder, there will be only one (1) file starting with "020_ImportFile_". Does that make a difference?

    See below:

    Code:
        Dim strFile As String    
        strFile = "020_ImportFile_*"    
        
        'Import routine... I need to modify the line below so that I can use a wildcard instead of the "_07" in the filename
        DoCmd.TransferDatabase acImport, "Microsoft Access", "YOUR_FOLDER_PATH\" & strFile & ".accdb", acTable, "01_Table", "01_Table", False
    ... results in an error (see attached JPG).
    Attached Thumbnails Attached Thumbnails Error.JPG  

  4. #4
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Follow-up on post #3:

    It appears the following is working...

    Code:
        'If table already exists, delete it first
        DeleteTable "01_Table"
        
        Dim strFile As String
        
        strFile = Dir("YOUR_FILE_PATH\020_ImportFile_*.accdb")
          
        'Import routine... I need to modify the line below so that I can use a wildcard instead of the "_07" in the filename
        DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\Users\ThomasBock\Desktop\Import\Import_Data\" & strFile, acTable, "01_Table", "01_Table", False

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,

    Not sure why go to this hardcoded version from the previous one you had which was importing all tables from all Access files you had in any folder that you selected it.

    If you want to specify one file to import you could add a settings table with a field to store that path and file name and put that on the form. Three you could update it directly or add code to the double-click event to open a file dialog so you could browse for it.

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

  6. #6
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Thank you for the feedback/question... I appreciate it.

    Allow me to summarize (in bullet-format)...
    - As I have posted plenty of threads over the past few weeks, you are well aware of the overall project (data migration)
    - Over the course of this project, I have made many, many tweaks... presently though I've narrowed it down to 4 steps:

    1. Identify legacy fields from various sources
    2. Consolidate legacy data and identify distinct values
    3. Perform value transformation (legacy to target)
    4. Generate final product tables

    Now, the import method using "Browse" is currently applied to step #1. That is, I perform a mass data import of any ACCDB file residing in a folder. That process works great (again, thank you for helping me w/ that last week).
    Next, once I transition into the next steps (i.e., I still separate all step-related objects in separate DBs) I create, e.g., a table in "Step #1"... that table is then utilized in "Step #2".

    So, while the "Browse" method works great for the mass data import in step 1, I need to have the alternate method to "transfer", e.g., only one (1) specific table (vs. all tables in a DB file) in some of the other steps.

    I know, this was a long-winded answer to a short question.

    All that said though, I will use both methods when dealing w/ the 4 steps.

    Here's the only challenge though. Presently, the import routine works great when I'm performing certain activities in the DB files. Alternatively, once I post the updated files to SharePoint and team members will download the latest version, that process won't work unless they update the filepath as well.

    So, ideally, I would eventually want to come up w/ a method that allows both: Using the "Browse" procedure to point to a file directory but NOT import all tables except one. So, basically, it would be a combination between the "browse" (file selection) and "hard-coded" (only import Table1). Hope this makes sense.

    Cheers,
    Tom

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    So just add a settings table to hold the name of the table; if one is entered edit the code to only import that one:
    Code:
    if tdf.Name = dLookup("TableName","tblSettings)"
    If the table is empty
    Code:
    dCount("*","tblSettings")=0
    then import all tables.

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

  8. #8
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Again, thanks for the feedback... I implemented the line "if tdf.Name" into the "Browse" version and it imports only one (1) table. However, additional tweaking may be required.

    Before going into the subject of "tweaking", please allow me to do a quick recap of the 3 different "methods". Btw, attached zip file contains a folder with some dummy tables AND 3 databases:
    1. "01_Import_Routine_Browse_Method"
    2. "02_Import_Stored_Path"
    3. "03_Import_Routine_Browse_Method_Settings_Tabl e"

    Below is a recap of the "process", "pros", and "application" for either 3 files:


    "01_Import_Routine_Browse_Method"
    ============================


    *Browse* Method -- Process:
    - Open form "F01_MainMenu" and click command button
    - Browse to folder "\...\Import\Source_Files" and click "Ok"
    - Indicates "2 source files were successfully imported"... total # of tables, however, equals 6.

    *Browse* Method -- Pros:
    - Allows me to import all tables from multiple files via action (command button on form).
    - This works great when importing all of my source files which may contain multiple tables each.
    - I am using this method in my "step #1" and will continue to use it going forward.

    *Browse* Method "Application":
    - Again, this method works great when importing n tables from n files.
    - However, this process cannot be used when attempting to import/update, e.g., a "single" table object from a specific file.


    "02_Import_Stored_Path"
    ==================================


    *Stored Path* Method -- Process:
    - Open form "F01_MainMenu" and click command button
    - Indicates that "Table_02" was imported from filename "010_File_01.accdb"

    *Stored Path* Method -- Pros:
    - Allow me to execute a routine, e.g., on "Form Open" and automatically import the latest table (from another DB) without any interaction by the user.
    - This ensures the user will always have the most up-to-date table version (based on an external file/DB).

    *Stored Path Method "Application":
    - Again, I now can enforce automated "table-updating" from one DB to another without any user interaction. If the message box is disabled, user won't even notice that the table(s) was/were updated.
    - However, once I post the file on, e.g., SharePoint and another user will work w/ the file, this process won't work unless he/she updates the hard-coded file path (in the form).


    "03_Import_Routine_Browse_Method_Settings_Tabl e"
    ================================================


    - So, this version is a combination of the "browse" method but importing only a specified object.
    - I really like how easy it was to only import the designated table "Table_02" into the database.
    - As part of the testing, I then added a 2nd table "Table_05" into table "tblSettings".
    - However, only one table ("Table_02") was added while "Table_05" was NOT added. I know, I originally specified to import only one (1) table but I wanted to see how flexible this process was in the event I needed to import multiple but NOT all tables.

    My question(s):
    1. Is it possible to modify the code so that any table included in "tblSettings" would be imported?
    2. I did not know where the line "dCount("*","tblSettings")=0" should go in the event my table is empty.
    3. Lastly, and that's probably the biggest piece... let's go back to the hard-code paragraph "Application".
    3a. The major benefit of specifying the path is that no user-interaction is required. At the same time, I am aware that the filepath requires an update when a different user works w/ the file.
    3c. So, is there some way where the "Browse" dialogue is automatically thrown forcing the user to drill down to the source file? This assumes the user will then even know where to "go" in order to get to the source folder containing the DB holding tables to be imported.

    I know, I know... 3c is a dilemma that I need to work out. Fully understand that this is a limitation... I am merely brainstorming how I can best accommodate the automation process while at the same time not requiring the user to update file path.

    I welcome any thoughts on this... !

    Thank you,
    Tom
    Attached Files Attached Files

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,

    Please review the updated file. When you open it the file the code will attempt to process the files in a subfolder of its current location called "Source_Files". If that subfolder is not found it opens the browser. I also tweaked the code to import all tables you would add to tblSettings.

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

  10. #10
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Your solution(s) -- to every problem I have encountered -- are always, always PERFECT! This modified version ("best of the 2 methods") works fabulous.

    Just one final tweak may be beneficial... in this example, I have 2 DBs containing 3 tables each. For testing only, I just added a 3rd table to tblSettings. Now, in the message box, which variable displays the # of tables imported (vs. iCounter... # of files)? If that variable doesn't exist, how would I need to tweak the code to display the # of tables imported?

  11. #11
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,
    Please review this latest update, added a table counter. I also added a Table_02 in 011_File_02.accdb and the code does indeed import three tables (02 from both files and 05 from 011_File_02.accdb), adding "1" to the table name similar to the native Access naming convention when importing objects with the same name. This behaviour might be OK if you do not have overlaps or you could tweak the code to add the file name to the table name (i.e. Table_02_010_File_01) on the Docmd.TransferDatabase line.

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

  12. #12
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- I like this new version a lot! Thank you for the additional mods.

    May I ask a follow-up question? As part of this new process, I would not even need/want to open up that particular form. So, how would the code have to be tweaked where, e.g., the code is moved from the form into (maybe) a module? Then, once I open up the DB, the import routine still "kicks in" (w/o the form) and updates/import all specified tables.

    Just wondering if that's doable.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Tom,

    Please have a look at the following link:
    https://support.microsoft.com/en-us/...4-8670416628b0

    Move the code currently in the Click event of the (hidden) button to a public function in a new standard module, create a new AutoExec macro and add RunCode (calling the new public function) as its first Action.

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

  14. #14
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad:

    Oh, that's totally awesome... the AutoExec routine works perfectly!!!

    Many thanks again for your fantastic help.

  15. #15
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- this is a follow-up to post #14.

    As part of the integration process, I realized I need a little more help with making (I hope) a minor tweak to the code.

    Please find attached two (2) zip files:
    1. Scenario_1
    2. Scenario_2

    Recap of "Scenario 1":
    - This version works great using the AutoExec macro
    - In the module, I added new variable "strFolderName" and updated the 2 lines in the "If (Dir(CurrentProject.Path" section.
    - The key, however, is that the DB resides in a folder which then contains subfolder "Source_Files" (from which I import the specified tables).

    Now, allow me to recap "Scenario 2" ... which mimics my actual folder structure as part of my 4-step process:
    - In this version, I have changed the folder name from "Source_Files" to "Step_1".
    - Also, I added another folder "Step_2".
    - Now, in both folders ("Step_1" and "Step_2"), I have included the AutoExec DB "Import_Macro".

    Envisioned process for DB in "Scenario 2 - Step 1":
    - When in folder "Step_1", I want the VBA routine to NOT "look" into a subfolder.
    - Instead, I want the routine to import all specified tables (based on "tbl_ImportTables") within the *same* folder.
    - So, tables from the other 2 DBs within the same folder need to be imported.

    Envisioned process for DB in "Scenario 2 - Step 2":
    - Alternatively, for the 2nd scenario, I need the VBA routine to "go up a folder" and then "go down" into a subfolder.
    - So, in here, I need to be able to insert the tables from subfolder "Step_1" where this folder does NOT exist in the same location as the DB.

    Now, given these 2 different directory scenarios, I'd be perfectly ok to **comment out/un-comment** VBA code depending on either "step-1 scenario" or step-2 scenario".

    My question:
    What modification to the VBA needs to be completed in order to address both step-1 and/or step-2 scenarios (i.e., *1. Tables in DBs within same folder*; *2. Tables in DBs in a different subfolder*)?

    Thank you again for the help in advance,
    Tom
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-21-2018, 10:18 AM
  2. Make Query Ignore Special Characters
    By NadiaVKhan in forum Queries
    Replies: 39
    Last Post: 04-04-2017, 09:43 AM
  3. How To Use Wildcard Characters in Query Search
    By Keats713 in forum Queries
    Replies: 2
    Last Post: 12-21-2016, 10:14 AM
  4. DoCmd.TransferDatabase acImport Issue
    By remingtont in forum Programming
    Replies: 0
    Last Post: 11-12-2010, 03:59 PM
  5. getting error using docmd.transferdatabase
    By haggisns in forum Import/Export Data
    Replies: 1
    Last Post: 11-02-2010, 08:18 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