Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Connect to more than 1 SQL DB (>1 SQL backends)

Discussion in 'Information Technology' started by rogge, Jul 28, 2006.

  1. rogge

    rogge
    Expand Collapse
    Guest

    All:
    I am wondering if one can connect to more than one SQL DB. I would like to
    create a front end that connects to more than one SQL Server backend.

    For instance, I would like to keep the main database separate from a postal
    code database. Then the postal code DB can be used for other databases and
    manages separately.

    Is the database file type, MDB, file the only method or can an Access
    project be used, ADP? Can this be done by linking tables, preferred Access
    and time constraints, or will a ADO connection need to be programmed,
    preferred by .NET or VB?

    If this can be completed using linked tables, please provide or point to a
    step-by-step guide for linking tables without using "My Data Connectons",
    thank you.

    Access 2003 / SQL Server 2000 / Win Server 2003

    Thank you for all your help! -r
     
  2. Loading...

    Similar Threads Forum Date
    Connected Blogs Oct 17, 2015
    connection! Blogs Oct 17, 2015
    Still No Connection Blogs Oct 17, 2015
    connection Blogs Oct 16, 2015
    Ways to Connect to The Guru - Suggestions? Questions and Answers May 27, 2014

  3. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    I use an MDB and DSN-less connections to connect to as many different SQL
    Servers as I want at any given moment. (I don't think you can do that with
    an ADP, but someone who uses those more may correct me on that.)

    Use TransferDatabase command and a table of links (tablename + connect
    string) to set your connection table-by-table. You can normalize another
    level to be more efficient at storing connection strings, but that's a
    challenge for another post.

    If you look at these topics and still have trouble, post back here.
     
  4. rogge

    rogge
    Expand Collapse
    Guest

    Thank you Rick, have a great week.

    That is what i was afraid of... I had used something similar to
    TransferDatabase to point the conneciton to the same folder. looping through
    a table is a good idea / if i do this i might as well use FORTRAN (ROTFLMHO).
    Since i will have more options with the front end.

    hmmm i might be able to use about 93.5% of an application i programmed about
    3 years ago.

    "Rick Wannall" wrote:

    > I use an MDB and DSN-less connections to connect to as many different SQL
    > Servers as I want at any given moment. (I don't think you can do that with
    > an ADP, but someone who uses those more may correct me on that.)
    >
    > Use TransferDatabase command and a table of links (tablename + connect
    > string) to set your connection table-by-table. You can normalize another
    > level to be more efficient at storing connection strings, but that's a
    > challenge for another post.
    >
    > If you look at these topics and still have trouble, post back here.
    >
    >
    >
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    It is recommended to use CreateTableDef instead of DoCmd.TransferDatabase
    Note: I think there's a bug in it's parameter clauses
    ie
    Set TDef = Db.CreateTabledef(TName)
    TDef.ForeignTableName="Foo"
    TDef.Connect="..."
    Db.Tabledefs.Append TDef
    'Will Work

    whereas

    Set TDef = Db.CreateTabledef(TName,,"Foo","....")
    Db.Tabledefs.Append TDef
    'will fail in my experience

    Pieter

    "Rick Wannall" <cwannall@yahoo.com> wrote in message
    news:eek:Iocg.75531$_S7.63910@newssvr14.news.prodigy.com...
    >I use an MDB and DSN-less connections to connect to as many different SQL
    >Servers as I want at any given moment. (I don't think you can do that with
    >an ADP, but someone who uses those more may correct me on that.)
    >
    > Use TransferDatabase command and a table of links (tablename + connect
    > string) to set your connection table-by-table. You can normalize another
    > level to be more efficient at storing connection strings, but that's a
    > challenge for another post.
    >
    > If you look at these topics and still have trouble, post back here.
    >
    >
     
  6. Rick Wannall

    Rick Wannall
    Expand Collapse
    Guest

    No argument on using CreateTableDef vs. TransferDatabase.

    Main focus is that you can use a table of connections and set them at will.
    The core of it along this line:

    strConnect = "myconnectioninfo"
    Set tdf = dbs.TableDefs("mytablename")
    tdf.Connect = strConnect
    tdf.RefreshLink

    If you put this inside a loop that gets you a list of tables, and
    additionally connect that list to a table of connection strings, you can
    control put multiple connect strings for the same table in the list of
    connections, and retrieve the one you want based on a setting that I call
    Mode (one of "Dev", "Live", "Test", "Train")
     

Share This Page