Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple databases (2002 SP-1)

    Here is an advanced topic for someone. I have developed an Access front-end to produce reports from an SQL Server database. I have linked to the SQL Server through an ODBC connection, which has worked well.

    I now have a need to grab data from two different databases residing on the same server. It would be nice to be able to combine the data from both databases into one report, but I would settle for just being able to grab the data from both databases from within the same MDB.

    I looked into TransferDatabase function, but that became too cumbersome. I know this can be accomplished as an ADP, but that means migrating [read: start from scratch] my entrire current application. Is there a way to do this from within my current MDB. Failing that, please point me to a decent resource for creating/working with Access Projects.

    Thanks

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

    Re: Multiple databases (2002 SP-1)

    Why not just link to the appropriate tables in the new database as well?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple databases (2002 SP-1)

    Is far as I can tell, you only have access to one ODBC connector at a time. And you can only access one database with each ODBC connector. Is there something I am missing?

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

    Re: Multiple databases (2002 SP-1)

    Are you linking the tables thru ODBC to SQL Server? It appears that you are. Can A2002 link to multiple databases?
    Is your database an .MDB or an .ADP?
    If an .MDP then can't you just run the AddIn "Database Linker" and link the tables via ODBC?
    If an .ADP then you cannot link the tables anyway (AFAIK).
    I don't have any experience with Access2002 but have A97 and A2000 and in both those you can link to multiple databases.
    Pat

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

    Re: Multiple databases (2002 SP-1)

    Not true - each table can be in it's own SQL Server database. The path is stored in the properties of the linked table, and we do it routinely. All you need to do is to create an ODBC source for each of the SQL Server databases, and then make the appropriate connection.
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple databases (2002 SP-1)

    <big> <font face="Comic Sans MS">I stand corrected. Thank you, Pat and Wendell!! The solution was so easy, I overlooked it. Isn't that the way it usually is!</font face=comic></big> <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>

Posting Permissions

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