Results 1 to 12 of 12
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    How to create repetitive tasks?


    This seems like a good place for this question. I like to create automated routines to do repeated tasks. I spent thirty years creating a toolbox of utilities in another system. It won’t be fun, but I see I need quite a few for Access. If you know of a good place to look for trustworthy Access utilities, like I describe below, please chime in.

    I’ve spent a few days now, exporting tables, queries, etc. to another db for testing.
    I’ve wasted hours exporting a few dozen objects. As far as I can tell, you can only export one object at a time. You can even save the export (that includes the path to the destination, but I’m not sure that VBA can modify the properties of an export). Even then, you still have to run each export and not forget one.

    I won’t say what I used to do (in my old utility to do the same), so here is what I want to do:

    Be in a given db that I want to export from.
    Edit a text file or create a form to specify what objects to export:
    1. The target to export to
    2. If the object is a table:
    a). Specify a criteria (optional)
    b) Specific records (optional)
    c) Specify “and” or “or” for the above two conditions
    3. Then be able to run the export of all objects at once from the toolbox utility.

    I’m going to write another thread in programming, with a question that might help with this one. Once I post, I’ll add links in both directions. Here:
    https://www.accessforums.net/showthr...732#post498732

    What’s the best way to tackle a project like this, with macros, code or something else?
    >>>
    If you're even curious how this was done, on 1/2" tape originally, to backup/restore/move data and code to a development/client computer, and later with pseudo tape into a file, I'll attach the code.

    999UTFILESTAPE.txt
    Last edited by twgonder; 08-03-2022 at 10:39 AM. Reason: add link

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Are you talking about just Data here? It's not absolutely clear.
    Assuming it is simply data how do you want to export it? To Excel? To CSV?

    I have a export routine that I created that lets me export any query directly to a Excel sheet and formats it to a Table with filters etc.
    You simply give it the query name and it does the rest.

    If you want to do multiple queries, simply collect the query names into a multiselect listbox and loop around the selected items.
    If you want to add criteria it can get a little more involved as each query would need to have matching field names or you get a bit cleverer and create a routine that does that on the fly, and build the export query names and criteria into a table that can be reused.

    I also have a tool for doing that from SQL Server views, that sends a pass through query to get the appropriate data and return it as the query for export into the Excel routine.
    The additional advantage being that I can create a new view on the server, put the SQL expression in the backend table, and it appears in the client reporting options without any need for front end database updates.
    It's all completely data driven, rather than hard coded.
    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
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Exporting objects to another .accdb

    Quote Originally Posted by Minty View Post
    Are you talking about just Data here? It's not absolutely clear.
    Assuming it is simply data how do you want to export it? To Excel? To CSV?
    I'm wanting to export ( tables, queries, etc.) to another .accdb file for testing, and then roll back into the production .accdb.

    [The old program I showed moved data, everything in that db system was just data (i.e., dictionaries to files, records, programs, etc.). For this thread I've limited it to moving the Access objects. If the new tool was very smart, it could move data too. The T-LOAD command in the program loads records into a table (using ACCESS terminology), kind of like what a SQL statement can do. But I don't think SQL can move queries, forms, reports and modules (but cool if it can).]

    I have a export routine that I created that lets me export any query directly to a Excel sheet and formats it to a Table with filters etc.
    You simply give it the query name and it does the rest.
    Sounds totally different to me, since I'm overwriting objects in another .accdb as export in Access does.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You do like to find the hardest way to do something
    I'm wanting to export ( tables, queries, etc.) to another .accdb file for testing, and then roll back into the production .accdb.
    just use filecopy to copy the .accdb to somewhere else. To take individual tables/forms etc make changes and copy back is a route to breaking things, particularly to a production db. tables in particular you cannot send back otherwise any data input into production since you took your copy will be lost. And rather than exporting to, consider importing from

    Investigate the use of DDE queries to make changes to a production table

    2. If the object is a table:
    a). Specify a criteria (optional)
    b) Specific records (optional)
    c) Specify “and” or “or” for the above two conditions
    Presume you have found out how to link tables. Failing that you can just write a query. There are 3 methods

    Basic
    Code:
    SELECT  *
    FROM myTable IN 'C:\Path\mydb.accdb';
    if password involved
    Code:
    SELECT *
    FROM [MS Access;PWD=password;DATABASE=C:\Path\mydb.accdb].mytable
    where multiple tables involved
    Code:
    SELECT *
    FROM myTable1 INNER JOIN myTable2 ON myTable1.PK  = myTable2.FK IN '' [ms access;pwd=;Database=C:\Path\mydb.accdb];
    these can be converted to action queries as required.

    All available in the access query GUI, just complete the appropriate properties. Recommend use the sql as above, modified to something real in your world, return to the gui and view properties rather than guess what goes where

    What’s the best way to tackle a project like this, with macros, code or something else?
    personally wouldn't touch macros. 'Best way' depends on what you are trying to achieve. There is often more than one way, I'm a believer in doing as much as possible in SQL with regards data, leaving code for navigation and presentation. there are others here who like to do as much as possible in code. You might want to take a look at my free version of Access Studio, downloadable from here https://www.accessforums.net/showthread.php?t=86393
    probably won't meet your requirements, but one way of working

    The recommended method of working is to have three environments, Development, Testing and Production. Testing should mimic the production environment as closely as possible in terms of network connections so it will reflect real life. And I presume you develop testing scripts (or to be more precise, your users should) based on the specified requirements of the app and its modifications

  5. #5
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Matter of opinion

    Quote Originally Posted by CJ_London View Post
    You do like to find the hardest way to do something just use filecopy to copy the .accdb to somewhere else. To take individual tables/forms etc make changes and copy back is a route to breaking things, particularly to a production db. tables in particular you cannot send back otherwise any data input into production since you took your copy will be lost. And rather than exporting to, consider importing from

    Investigate the use of DDE queries to make changes to a production table...
    I prefer to take the time to write a procedure, test it, and then use it with twenty-keystrokes to replace doing thousands of mouse clicks or typing command prompt statements over-and-over-and-over. That's the whole point of programming I suppose.

    As to copying the .accdb, I do that when it's appropriate, but many times it's not. If I send in a sample with a request for help, do you want a db uploaded here that has fifty tables and thousands of records? Of course not, that's why I'm exporting a small set from the development db to a test db. Nor while developing, do I want to risk testing dubious code snips downloaded from well-meaning tutorials or other sites (like AccessForums.net).

    I have three "levels" of dbs that I normally work with (presently in Access only the first two). Testing, development and production. When I say that I change production environments, keep in mind that I wrote the software, so I know what the files do and which are safe to update and when (ususally at night when no one else is using the db). I'm learning what I can and can't do in Access. For example, I could always update code in my old system on a production box, because the code didn't become active until it was explicitly compiled. Not the case with Access.

    As I said, I'm in deep investigation of all things Access at the moment, so I do appreciate the query examples you provided.
    Please, just don't presume that my 40 years of experience in enterprise computing makes me an idiot because I don't know even 10% of Access yet.
    Last edited by twgonder; 08-04-2022 at 07:49 PM.

  6. #6
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You could certainly create a VBA routine to export certain tables, queries and anything else you needed into a new database.

    How valuable this would be is down to your time to develop the routines and how flexible you want it to be ultimately.
    If it's just for your personal use during development then create a table with ObjectName, ObjectType, and a DoIWantToExportIt flag.

    Then write VBA to loop though the objects in that table and create/copy them into a new external database.
    I'd possibly make a form to do it so you can specify/store the external database filename, and also choose to overwrite an existing copy if the file already exists.
    You could have a control box that said "Only Export x records" and use that with a TOP predicate to just get a sample dataset in each table.

    However, this really isn't much different to opening a new blank database, and pressing the get external data option, and pointing it to your source database and getting the objects you want, except for the ability to restrict numbers of records (although you can specify to only import the table structure not the data).

    Like I said - if you are likely to use it a lot, then it's worth building a tool that is clever, and might even be useful to others, but if it's only for very occasional use then I think it's not worth the effort.
    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 ↓↓

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Point taken about a small sample but still think better to import from rather than export to- would take minutes rather than hours and if you find you need another object, easy enough to import on its own without closing the sample db, opening the source db, exporting, closing then reopening the sample

  8. #8
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by CJ_London View Post
    Point taken about a small sample but still think better to import from rather than export to- would take minutes rather than hours and if you find you need another object, easy enough to import on its own without closing the sample db, opening the source db, exporting, closing then reopening the sample
    I'll look into importing instead of exporting. I just don't see the difference, unless importing is better in some way. If I need five tables, I need five tables and the associated forms, queries, reports and modules for testing. What am I missing here?

  9. #9
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by Minty View Post
    You could certainly create a VBA routine to export certain tables, queries and anything else you needed into a new database.

    ...

    However, this really isn't much different to opening a new blank database, and pressing the get external data option, and pointing it to your source database and getting the objects you want, except for the ability to restrict numbers of records (although you can specify to only import the table structure not the data).
    I'll try doing this from the reverse direction that I have been doing. Would what you're suggesting just make a link to the external data, or create duplicates of the tables, queries, forms, reports, modules in the new blank database?

  10. #10
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by twgonder View Post
    I'll try doing this from the reverse direction that I have been doing. Would what you're suggesting just make a link to the external data, or create duplicates of the tables, queries, forms, reports, modules in the new blank database?
    During the first stage of the Access Import dialogue you have the option to link to tables or import them.
    When you get to the next dialogue there is an Options section - if you open that, you will see you can set an number of very self explanatory options, including importing queries as new tables.
    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 ↓↓

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I just don't see the difference, unless importing is better in some way. If I need five tables, I need five tables and the associated forms, queries, reports and modules for testing.
    Exporting:
    You have to first create the destination .accdb
    you then return to the source file and copy each object, one at a time (select object, select file) - these do not appear in the navigation pane if the destination is is open - so you need to hit F11 a couple of times to refresh it
    relationships do not get copied, so you have to go to the destination file and recreate them
    you cannot copy associated features such as menus and toolbars

    Importing:
    You have to first create the destination .accdb
    without returning to the source file, you select file then select all objects in one go - under options, relationships will be copied if the relevant tables are and you can opt for other features as well
    if at a later time you import a table that has relationships with a previously imported table, that relationship will copy across as well

    Not checked but pretty sure in both cases if you have copied a query, form or report and have forgotten an associated table or query, it will break if you open and save it if autocorrect is on.

  12. #12
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by twgonder View Post
    I'll look into importing instead of exporting. I just don't see the difference, unless importing is better in some way. If I need five tables, I need five tables and the associated forms, queries, reports and modules for testing. What am I missing here?
    I wrongly assumed that importing was the mirror of exporting. Now I see it's not, and it's much more useful. Being old school, I tend to think in terms of push instead of pull. Thanks for the tip.

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

Similar Threads

  1. Repetitive Subcomponent
    By Western_Neil in forum Database Design
    Replies: 3
    Last Post: 07-17-2016, 03:09 PM
  2. Replies: 3
    Last Post: 04-26-2016, 10:40 AM
  3. Replies: 5
    Last Post: 07-10-2014, 09:37 AM
  4. Create basic DB for maintenance tasks
    By TeaBase in forum Access
    Replies: 4
    Last Post: 12-13-2013, 04:15 PM
  5. Repetitive Import Problem.
    By jasonbarnes in forum Import/Export Data
    Replies: 5
    Last Post: 02-18-2011, 11:09 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