Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Choice of Production or Test (2003)

    Does anyone know if there is a way to select the system you want to use when opening a database.

    When the 'ODBC for Oracle Connect' presents itself to the user, s/he can change the server to test or prod. A Test table name is 'IBSTest_Fee_Based' and in Prod 'IBSProd_Fee_Based.
    I need to have a way to connect to the correct tables by changing 'MSysObjects'. The Connect and ForeignName fields seem to be the fields that determine connection.

    Should I have a copy of 'MSysObjects' that I rename depending on the users selection from a forced form at signon?

    Thank you,
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

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

    Re: Choice of Production or Test (2003)

    MSysObjects is a system table. It is read-only to the user, you cannot edit it or rename it. You could use the Connect property of the DAO TableDef object to change the connection of a linked table.

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

    Re: Choice of Production or Test (2003)

    You can have only one MSysObjects table in the database (although you can have MSysObjects1, etc.) and that table name is the one used by Access to store all the information about the objects in the database. Because it is in use when the database is open, you can't rename it.

    Are all the table names different between the two databases? The usual way to relink tables in another database is to have a list of the tables in question stored somewhere and to loop through it creating the links. If you store only the table distinguisher (i.e. "Fee_Based") in a table used for this purpose, you can concatenate the appropriate prefix ("IBSTest_" or "IBSProd_") to each record to come up with the correct table name to use in creating the links.
    Charlotte

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

    Re: Choice of Production or Test (2003)

    You might want to look at Relink Access tables from code on the Access MVP site - we've used this concept in several databases so we can switch back and forth between a test database and a live database.
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choice of Production or Test (2003)

    Thank you Charlotte, Hans, and Wendell

    This project has me trying to mimic what we do in PowerBuilder and it has been a challenge. The Tree View control was a real trip.

    All of your help (found some in searching) has been great and saved me hours of research.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

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

    Re: Choice of Production or Test (2003)

    Your comment about the Tree View control caught my interest. We've used the Microsoft Tree View for several years, and in some cases it works very well, but if you end up with trying to display large record sets - say several thousand nodes, performance deteriorates into the go get a cuppa, chat at the water cooler and then check to see if it's done. If that becomes an issue for you, we switched to using the version from DBI, which you can read about here, and found it works quite well with tens of thousands of nodes, though programming it is very different from the MS version.
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Choice of Production or Test (2003)

    Thanks Wendell will keep that in mind.

    I created a table that contains an id, name, type, and owner.
    The id is the PK.
    Name is the name of a table or an attribute of the table,
    Type is the MS access type value.
    Owner contains Null if the Name is of a table or the id of the table that contains the attribute named in this row.

    I now need to create a drag and drop from the Tree view to a control that I can then build a dynamic report. <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    Should be fun the next few weeks.

    Thanks again for your help.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

Posting Permissions

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