Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

    Here you go Tom, please open it holding down the Shift key to bypass the AutoExec macro and open the module to get familiar with the changes. I have added a new table to hold the ScenarioType (Step1, Step2 or Browse). Based on that it does the importing accordingly (also added a line to avoid getting into an endless loop by trying to import from itself).

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

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

    Awesome... this works great. With your permission, allow me to clarify one or two items. I summarize what I did first:

    Folder "Step 1":
    1. Copied the DB into Folder "Step 1" where it resides with the other ACCDB 2 files.
    2. Opened table "tbl_ImportTables" and changed values to all "odd-numbered" tables such as {Table_01, Table_03, Table_05}.
    3. Opened table "tbl_ImportSettings" and reviewed record. Although I'm in "Step1", I did NOT change value to "Step2"... 'not because I did not want to but merely due to learning more about the routine and how it works.

    I then copied the same DB into folder "Step 2":
    1. As you know, in this case, the DB is the only ACCDB in this subfolder.
    2. Now, in tbl_ImportTables, I changed table references to all "even-numbered" values such as {Table_02, Table_04, Table_06}.
    3. And again, in "tbl_ImportSettings" I kept the value of "Step2".

    Now, here's what I expected to happen:
    a. I go into "Step 1" (containing "Step2" in [tbl_ImportSettings]) and I was fully prepared to see that it would fail. However, it did not fail and imported {Table_01, Table_03, Table_05}.
    b. Now, I got into "Step 2" (containing "Step2" in [tbl_ImportSettings]) and I did expect the routine to succeed. I did and imported {Table_02, Table_04, Table_06}.

    Recap:
    - Based on b, it appears it does NOT make a difference what value ("Step2" or else) may be included in [tbl_ImportSettings].
    - If the latter is incorrect, I'm pleasantly surprised the correct tables were imported (i.e., with or without a subfolder).
    - Lastly, please see updated routine where I changed the value in in [tbl_ImportSettings]. That is, instead of referencing "Step1" or "Step2", I should have used more proper language in the previous thread.
    - Specifically, it's not so much whether it is the step #, the difference is really "SameFolder" or "DifferentFolder"... or something like it.

    All that said, the attached version works great and I updated the scenarios to "SameFolder" or "DifferentFolder". The correct tables (1, 3, 5) AND (2, 4, 6) are imported for "SameFolder" and "DifferentFolder", respectively.

    So, I'm absolutely happy how this worked out. I'll start implementing this generic example into my actual solution. In case I run into any unexpected "surprises", I will post them here.

    Again, **1000 THANKS** for your continued help... you're always so very helpful and pleasant to communicate with.

    Cheers,
    Tom

    P.S. Attached ZIP does NOT require any changes... 'just wanted to provide the most recent baseline.
    Attached Files Attached Files

  3. #18
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Tom,
    It does matter what you have in the new table; what happens in your existing setup is that for a file located in either Step_1 or Step_2 subfolder the path to the source tables is identical: up one folder then down to "Step_1" subfolder. It just happens that for the macro file in Step_1 the source is the same as it's own location.
    Cheers,
    Vlad

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

    Thank you... I have started integrating the solutions into my actual project but I'm now running into issues when in "different" folder.

    I'm 100% confident it's for the same reasons you mentioned. I apologize for not having used more concise/actual "folder language" (vs. the "Step1" or "Step_1", etc. references).... trying to be generic in this forum "gets me in trouble" all the time.

    Ok, allow me to recap what I currently have:
    a. I have multiple folders. I hope for the purposes of this thread, I only need to describe two of them... one for "same folder" and another one for "different" folder.
    b. So, my first folder is called "01_Maintain_Shipyard_Master"; alternatively, my 2nd folder is called "02_Generate_LK_FF_TD".
    c. When in folder "01_...", the DB is ultimately in the "same" folder; when in folder "02_..." the DB is in the "different" folder.

    More info:
    - Attached are 2 DB ... I know the code should be generic but I had to tweak the DELETE table routine given that each table is different and I can't simply use the, e.g., DELETE "Table_*" routine...
    - Ideally, the DEL table would be synchronized with the values stored in table "tbl_ImportTables" (vs. hard-coding the table names).


    Now, this is where I'm getting confused due to the "Step_1" or "Step1" references... again, this is due to my posting the thread using poor language... mea culpa.

    That said, what needs to be replaced with either "01_Maintain_Shipyard_Master" (i.e., "same" folder) OR "02_Generate_LK_FF_TD" (i.e., "different" folder")?

    Right now, it appears I'm somewhat in a do loop ... I know you addressed it in the code but when I'm using the DB in the different folder, it does not import the tables right away. Instead, it opens another blank database.

    Finally, I also included a PowerPoint containing 2 slides which shows the different values in the admin tables + the manual code changes based on the actual folder names.

    I thought I was so close given it worked w/ the generic versions just fine... unfortunately, it doesn't work just yet in the actual solution. I

    I love this approach and really would like to solve it.

    Thank you,
    Tom
    Attached Files Attached Files

  5. #20
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Vlad -- allow me to provide you a quick update to post #19.

    - The routine works exactly as envisioned when "mimicking" my project setup (without any all sub-subfolders).
    - Now, once I copy the same exact files into my actual project directory, the *behavior* when in the 2nd folder is different.
    - Please see attached JPG.... so, when in "different" folder (2nd folder), I open the DB.
    - At this point, the "donut" appears to spin a bit longer compared when using the sample directory.
    - Next, in additional to the DB that I opened (and want to transfer tables into), the source DB is also opened. Further, another blank Access DB window has also opened.
    - Meanwhile, in the background, the correct table has been imported into the particular DB.

    What may cause the AutoExec/VBA routine to work differently when in a different subfolder? That is, why would it open the source DB + a blank Access window? Please keep in mind that there are other subfolders under my "Step_1" equivalent folder. I am hopeful that the additional subfolders (which may also contain DBs) won't be searched.

    Best,
    Tom
    Attached Thumbnails Attached Thumbnails WhenInDifferentFolder.JPG  

  6. #21
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I narrowed down the cause for the underlying problem. I recap below:

    Recap of generic process:
    - Regardless of the folder (whether "Step_1" or "Step_2"), the filename of DB -- which contains the AutoExec macro -- is always named "Import_Macro".
    - Thus, as you know, when in folder "Step_2" and opening "Import_Macro", the routine first goes up a folder and then down a folder.
    - Once it drilled down into the other folder, it scans through the DBs including the other file "Import_Macro"... given that both DBs have the same filename, file "Import_Macro" (in folder "Step_2") skips scanning through "Import_Macro" (in folder "Step_1").
    - Thus, the endless do-loop is prevented.

    Recap of actual process:
    - I copy DB "Import_Macro" into both folder (e.g., "Step_1" and "Step_2").
    - I change the value in [tbl_ImportSettings].[DirectoryInfo] from "SameFolder" to "DifferentFolder".
    - ... and finally, I rename the filename from "Import_Macro" to, e.g., "Other_Name_Macro"..... AND THIS IS THE UNDERLYING CAUSE why it's not working in my actual folder structure.

    As you know, now I have 2 AutoExec DBs in the folder structure. So, when opening up "Other_Name_Macro" (Step_2 folder) and it goes up/down into "Step_1" folder, it finds the DB "Import_Macro" file. At this time, "Import_Macro" is also opened... which in turn performs another import routine which its own message box.

    My question: Is there a work-around to the file naming conflict? I have a constraint where I need to keep the current file naming convention as is (due to workflows on SharePoint). Only thing I can think of it to "auto-acknowledge" the Msgbox in file "Import_Macro" and the auto-close that file.

    Any final thoughts on this?

    Thanks,
    Tom

Page 2 of 2 FirstFirst 12
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