Results 1 to 12 of 12
  1. #1
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74

    Export Table without open DB

    Good afternoon.
    I would like to know if anyone have experience in exporting MS ACCESS tables between 2 databases without open the original DB (background?).
    In few words, i have my BE database where there are some tables which need to be used from a third party application. The idea it was to create a separate database where to move the only tables they need and gives the access to this new database which i will refresh everyday. The problem is that i would like to avoid to do this manually (open my BE and export table in the second database). There is a way to do automatically in background ?
    Or, any other suggestion ? I would like to avoid to give rights to my BE database.
    Thank you,
    L.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    I could swear this was asked on UA and the answer was No.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    to get inside anything, it must be opened.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with gasman and ranman that it would need to be open. But, just thinking and typing, why couldn't you copy the tables involved to another database(X) and let them access that. If you told us more about your processes and how the third party needs to interact, then you might get more focused assistance. It isn't clear if your concern is with the work involved or the sensitivity of the data/table involved.
    It seems you could give them rights to database(X) without them having access to your standard BE.

  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
    You can write some code to import (or export) the tables into a new (or target) database then open the file with the code via a scheduler task (using the built in Windows scheduler) at certain times.

    For an example you can have a look at my free utility which creates a "fused" file from a front-end and the associated back-end; you can use it as is by creating a simple "front-end" with no interface, just the linked tables in question.
    http://forestbyte.com/ms-access-utilities/fba-fuze/

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

  6. #6
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Quote Originally Posted by orange View Post
    I agree with gasman and ranman that it would need to be open. But, just thinking and typing, why couldn't you copy the tables involved to another database(X) and let them access that. If you told us more about your processes and how the third party needs to interact, then you might get more focused assistance. It isn't clear if your concern is with the work involved or the sensitivity of the data/table involved.
    It seems you could give them rights to database(X) without them having access to your standard BE.
    Thanks for the answer and yes, this is exactly what i did, sorry if i was not clear in the explanation: I have 5 tables in my BE which need to be used from third party tool so what i did is to create a separate DB where i imported these 5 tables as local tables from my BE. In this way they will not touch my BE. The problem is that i need to updated these tables everyday because data need to be updated and i was looking for something can be done without any "manual" activity: open target database, import the 5 tables and close every time. I hope it is more clear now. Cheers

  7. #7
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Quote Originally Posted by Gicu View Post
    You can write some code to import (or export) the tables into a new (or target) database then open the file with the code via a scheduler task (using the built in Windows scheduler) at certain times.

    For an example you can have a look at my free utility which creates a "fused" file from a front-end and the associated back-end; you can use it as is by creating a simple "front-end" with no interface, just the linked tables in question.
    http://forestbyte.com/ms-access-utilities/fba-fuze/

    Cheers,
    Thank you, much appreciated. I will go trough and see if i can use to manage my scope.
    Cheers

  8. #8
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    [QUOTE=Gicu;492334]You can write some code to import (or export) the tables into a new (or target) database then open the file with the code via a scheduler task (using the built in Windows scheduler) at certain times.

    Any example of this especially the code to be used to open the file via a scheduler task in Windows ?
    Thanks

  9. #9
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    [QUOTE=lmarconi;492352][QUOTE=Gicu;492334]You can write some code to import (or export) the tables into a new (or target) database then open the file with the code via a scheduler task (using the built in Windows scheduler) at certain times.

    Following this suggestion, the idea coming in my mind is: in my BE , create a Macro Autoexec where i will add a code to transfer the 5 tables in the target DB (the one to be used from third party application) and open the BE database with the Windows Scheduler (i just give a look to the help on line looks like is possible to schedule to open a db). In this way, if everything work fine, everyday at certain time the windows scheduler should use the task i prepare to open the db and when the db is open the Macro Autoexec will automatically transfer the tables. Sound good ? Of course i need to test, not sure it work.
    Cheers

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You do not need code to do that, you open the scheduler and you add a task to open your Access file that has the code to copy the tables.
    https://www.windowscentral.com/how-c...ler-windows-10

    As for the code to copy the files you can use "INSERT INTO" statements or Docmd.TransferDatabase.

    My utility would probably work for you as it has an "auto-run" switch that would trigger the code to run and create the copy then close itself.

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

  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
    Sorry just saw you're latest post, yes, that is exactly the plan, but I would suggest you don't open the actual BE but just another file that has links to the 5 tables.

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

  12. #12
    lmarconi is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Sep 2020
    Location
    Muscat
    Posts
    74
    Quote Originally Posted by Gicu View Post
    Sorry just saw you're latest post, yes, that is exactly the plan, but I would suggest you don't open the actual BE but just another file that has links to the 5 tables.

    Cheers,
    Thanks, fully agree and, yes, I did as you suggested. I Create another database where, from the BE, i linked the tables i need then i create a function who do the following:

    1. Create a copy of the linked table(s) (otherwise when i transfer IT in the target db, the table will be linked and i don't want this);
    2. Transfer the table(s) to the target database using CopyObject.
    3. Delete the created copy of the table(s).

    Now the problem i have is that i don't have rights to create Task in Task Scheduler so need to check with IT department, anyway what i should do from my side i did and thanks for usual and evaluable support of everyone.
    Cheers.

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

Similar Threads

  1. Add error code for export VBA if PDF is already open?
    By templeowls in forum Programming
    Replies: 3
    Last Post: 12-16-2021, 04:19 PM
  2. Replies: 5
    Last Post: 08-26-2018, 07:54 PM
  3. Replies: 1
    Last Post: 10-29-2015, 07:03 AM
  4. open excel after export
    By xopherira in forum Import/Export Data
    Replies: 6
    Last Post: 07-28-2015, 06:58 AM
  5. Export Data w/FSO - Open object recordset failed
    By JayZoll in forum Programming
    Replies: 1
    Last Post: 11-12-2012, 01:38 PM

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