Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Jet 4.0)

    Need Code to Loop and Copy Objects to New MDB?
    A2k (9.0.3821) SR-1 Jet 4.0

    I do all my development from a single development database that has all my application, examples and common forms and report design, and code modules.

    I do this because I

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    Not to nitpick, but the CurrentData class object would only include AllTables & AllQueries (and AllViews, AllFunctions, & AllStoredProcedures for SQL Server projects). You'd use CurrentProject object for AllForms, AllReports, AllMacros, & AllModules. Nitpicking aside, that's a good suggestion for looping thru database objects.

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

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    If you have the developer edition of Office, you might want to get your feet wet with Visual Source Safe and just share objects from a repository database into your new project when you start it instead of developing in one humongous database and then trying to break it out. There's a recent thread in VB/VBA on VSS here that might be of interest to you. VSS takes a little getting used to but is a lifesaver when you need to go back a version on just one object. Just what the <img src=/S/doctor.gif border=0 alt=doctor width=25 height=33> ordered.
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    Thanks for pointing this out, Mark, it's not nitpicking at all. I'm just starting to use Access XP, was stuck with 97 up to now (and still use that mostly), so I am not very familiar with these new features yet. Your remarks are very useful for other Loungers reading this thread. I will modify my previous reply.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    <P ID="edit" class=small>(Edited by HansV on 28-Feb-03 14:57. Mention use of CurrentProject for forms, reports and modules - thanks to Mark D for pointing this out)</P>Something like this:

    Sub LoopThroughTables(sType As String)
    Dim dbs As Object
    Dim obj As AccessObject
    Set dbs = CurrentData
    For Each obj In dbs.AllTables
    If Mid(obj.Name, 5, Len(sType)) = sType Then
    ' Got one! Do something with it, for example:
    Debug.Print obj.Name
    End If
    Next obj
    Set obj = Nothing
    Set dbs = Nothing
    End Sub

    Call it with the identifier of your project:

    LoopThroughTables "PT"

    You can do the same for queries by using AllQueries instead of AllTables.

    For forms, reports and modules, use CurrentProject instead of CurrentData, and AllForms, AllReports and AllModules instead of AllTables:

    ...
    Set dbs = CurrentProject
    For Each obj In dbs.AllForms
    ...

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    Hi Charlotte

    Finally bit the bullet and installed VSS

    I have Ver 6 build 8163, Is this most current? if not how do I get most current?

    Other then help, is there a tutorial anywhere on how to get started using VSS with access?

    Thanks, John

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    Hi Hans & Mark

    Charlotte is right with VSS, just one of those things I should of started day 1 with.

    Your code will help me until I get my arms around VSS

    Thanks, John

  8. #8
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    As the poor sod that started the VSS thread that Charlotte mentioned <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, I will concur that it's the way to go.

    I haven't yet used it with Access, just VB, I'll say this, besides it's interface shortcomings (not very intuitive to me), I can now see the benefits to it. I does take a bit to get used to it, but once there..... I have only really started using it this week so I can't add much in the way of info other than.....

    I don't know if that version is the most recent, but you can, and should get VSS SP6 here
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    Hi Carbonnb

    At least I will be working with most current version service pack 6 which appears as 6.0c in help, about

    Thanks, john

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    Does VSS cost anything to buy, or can it be obtained free of charge?
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

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

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    Office 2000 developer came with VSS 6.0a, which is probably what you have, and XP developer came with 6.0b (Build 9119). They all work pretty much the same, so it shouldn't make any real difference. The on-line help is the best resource I've seen.
    Charlotte

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

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    It's a licensed application, so it's not free. The versions of Office developer beyond 97 include a full installation of VSS, as does Visual Studio and VB Pro and higher.
    Charlotte

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    I must have it then, 'cause I have A2000 premium? Where would it be?
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    Patt, Office Premium is not Office Developer - VSS is part of the latter, but AFAIK not of the former. Premium was Office Pro plus FrontPage and PhotoDraw.

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Need Code to Loop and Copy Objects to New MDB? (A2k (9.0.3821) SR-1 Je

    Thanks Hans

Posting Permissions

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