Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    automating Access (97/SR2 + 2000/SR1)

    Can anyone give me some pointers on automating Access <img src=/S/help.gif border=0 alt=help width=23 height=15>
    The first thing I want to do is to be able to create an MDE from an MDB file.
    This process really needs to be done through a VBScript, as I want to use FinalBuilder to create a "release zip", including the mde plus a few other files.
    I've looked at automating through the Access.Application but can't see how you do the actual Create MDE step, apart from using Send Keys, but that seems a bit dodgy

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    You could try this:
    RunCommand acCmdMakeMDEfile

    Although I've never tried it, so I don't know how it works. It may just bring up the pop-up window.

    -- Mark
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    Mark, thanks for the response
    Not much luck with acCmdMakeMDEFile I'm afraid.
    Originally I think it was something to do with taking the default option, which I found a couple of references to solving using the SendKeys.
    Thought I'd cracked it, but then I got an error basically saying that you couldn't use the command from code, but must use the menu <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    Next thing I've found in poking around is a note about the undocumented SysCmd 603, <source>, <dest> function which certainly seems to do the trick with Access 97
    - I made a quick VB program to do a CreateObject("Access.Application") and then Obj.SysCmd 603 ... and I've got a MDE <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Now to try with Access 2000 and XP

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: automating Access (97/SR2 + 2000/SR1)

    What is FinalBuilder and why do you need to create an MDE from code in the first place? It isn't necessary to use MDEs, but they're easy to create from the menu, so what's the point?
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    Charlotte,
    to quote their web site
    FinalBuilder is designed to automate tasks that you routinely do as part of your software development cycle. It allows you to quickly define a repeatable list of actions that can be executed in the same manner time after time. FinalBuilder cuts down build times by automating tasks that often take many laborious manual steps.
    More info at http://www.atozedsoftware.com/finalbuilder.html

    Why MDE's ?
    Our current way of distributing the Access reports is a zip file with the MDE + readme + various PDF's + SQL scripts
    - I use the MDE as some of the code comes from commercial libraries (like FMS's) so I want to make sure I stay within licence terms, and the hassle of Access security / MDW's seems even worse

    I would like to automate the process of creating the release zips, as a first step in trying to reduce my manual software configuration management problem
    - we have reports in Access 97 and 2000 for each of the two versions of our main product, each of which has at least 25 modules, so that's about 100 zips to keep up-to-date <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    I've had a quick play with Visual Source Safe, and it'd be good managing the modules, forms and reports, but the queries are only stored as a single binary in with the tables, so versioning them doesn't seem to be possible.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    Are you deploying commercial software to clients, or is this an internal package being deployed within a company? If it's the latter you may have some other alternatives in dealing with the configuration management problem. Post if that's the case, and I'll provide more details.

    As to Visual Source Safe, we use it for all large projects once they are deployed. About the only hitch we see (other than a fairly rare glitch) is that tables, import/export specifications and some miscellaneous stuff is stored in a single blob. But queries are separate - that is you can check out an individual query, modify it, and then check it back in. If you want to work on a table (even if you are dealing with data), you have to check out all tables, make your mods and then restore it. In general we work with linked tables, but there are occasions where you want to have a local table for configuration purposes, and in those cases you have to check out and then check back in. Hope this helps.
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    Wendell, thanks for the response, and it does help
    Commercial I'm afraid, so my options are a bit limited. Currently I'm running to stand still, so I need to find any quick wins to free up some of my time to do some actual development <img src=/S/frown.gif border=0 alt=frown width=15 height=15>

    Ah, queries are separate <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> I'd missed that and/or forgotten. Maybe I'll have to take another look at VSS.
    I use a few local tables, for config, update notes, and the like, but just checking those out shouldn't be too much of a problem.

    Even more interesting, FInalBuilder has options for working with VSS, like Get Latest Version, Check Out Files, etc. so assuming I can make something to do the MDB to MDE conversion, I may be able to automate a lot of what I need to do

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    That's interesting. I'll have to try to remember the SYSCMD 603, in case I need it in the future.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: automating Access (97/SR2 + 2000/SR1)

    You can't just check out local tables. You have to check all tables and miscellaneous data objects (i.e., references, etc.) at once. The biggest VSS advantage is that you can roll back changes to individual objects (not tables, obviously) or even "pin" versions, so that you can always get back to a prior release.

    I can see using MDEs for a commercial product, but you have to create a new MDE each time you make a change anyhow, so what's the difference between creating it from the menu or running some kind of script? My employer also produces commercial software but I'm afraid I just don't see the point of FinalBuilder. We use Wise Installer to build our setups, and we use Access security to keep people out of the code. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    But what if I could have an automated daily build, if needed, of each of the 100 release zips, taking out the latest version of the mdb from VSS, creating the MDE, adding the various documents, then I'd be a happier person <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Oh, I think FInalBuilder can also drive Wise Installer
    I saw the original reference to it on Joel on Software's site, very good site for software building

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: automating Access (97/SR2 + 2000/SR1)

    The mind boggles! <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> Why would you need daily builds of a hundred zips (whatever those might be in this context)? The installers use CAB files. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    If I change one of the main components, e.g. the ODBC reattachment form, I have to update each of the report databases. Well I ought to, but I don't have the time, so it's a case of updating the next time any work is done on that particular database.

    I can see how getting each of the databases into VSS will make my life easier, currently I have a little Access add-in which copies selected objects (forms, queries, report, modules) into the target database from a master database, but if I understand VSS (and that's a BIG if!), I should be able to make the change to one copy and then each of the databases that use that 'object' will be updated when I rebuild it. My hope is that FinalBuilder could run, not necessarily on a schedule, maybe just at my prompting, and rebuild every database using the latest check out from VSS.

    The need for a daily build is more of a general company policy/goal, which along with automated testing, management hope <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> will improve the quality of the software

    Not looked at Wise, although we have got a copy and someone is looking at it for our updates, so I've just stuck with zips as it's something fairly simple

    I've just checked FinalBuilder and it does have Wise + InstallShield

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    It sounds like you might benefit from making a library database that is called from the report databases. Unfortunately, I don't think VSS will do what you suggest. In VSS each database stands on it's own, so if you have components they have to be stored in each database. I'm not that familiar with the nitty gritty of VSS, so maybe there is some sort of include capability and we've never used it. After a bit of reflection, I guess you could create a core set of functions in a database, and then do development of reports outside of VSS in VSS databases created for that specific reason. However it would be nice to be able to have the report database objects managed under VSS as well.
    Wendell

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: automating Access (97/SR2 + 2000/SR1)

    You share objects in VSS from one database to another by opening VSS itself and dragging an object from one project to another. Then when any of the shared objects is updated from any of the projects that share it, it is updated for all of them. The exception here is miscellaneous data objects--tables, relationships, references. Those have to be maintained in each project individually, although you can import tables, relationships, etc. from within Access and add them to the current project without any problems. You can also copy the two objects that contain all those items from one VSS project to another and simply rename them to make them fit the current project (i.e., rename them currentprojectnamevss.corrrectextension).
    Charlotte

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, UK, Berkshire, England
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: automating Access (97/SR2 + 2000/SR1)

    Thanks Wendell and Charlotte
    I can see that I'm going to have to get more familiar with VSS <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    I'd had a quick go with the sharing of objects, but not got any further
    - partly concern it would work, partly finding the time to do the sharing for all the databases.

    Do you know of any good books and/or web sites on VSS <img src=/S/question.gif border=0 alt=question width=15 height=15>
    - tricks and tips, gotchya's, good practise, that sort of thing.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •