Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Location
    Ipswich
    Posts
    2

    2 SQL Database sources one frontend

    I have an access fronend that (Using DSN) point to one database on a SQL server. Currently everytime I make a change on our main business system (Customer Account Records) I have to export this from the VMS based system and have to import it into the Contacts database.



    Our VMS system is now mirrored into a SQL database on the same server as my Contacts Database.

    So rather than have to export and import into my Contacts database I was hoping to get my ADO connection strings to point to both, however I understand that I can not so this at the sametime.

    What I am looking to do is display information on a form that is sourced from tables in the VMS SQL database as well as my SQL Contacts database.

    Any suggestions would be welcomed!

    Many thanks

    Kevin

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Kevin,

    While typically there is just one back end, in reality you can have as many back ends as you like. For example, my last major Access app used an Access front end, an Access back end, and a second Oracle back end for some reporting capabilities.

    You will need two different connection strings, but the concept is exactly the same: link the back end tables to the front end .

  3. #3
    Join Date
    Mar 2006
    Location
    Ipswich
    Posts
    2
    Yes, I realise that but what I have is a form with tabs

    A/C Header - A/C Address - Contact

    The first two tabs will display data from "sql Server" Database "Progress"

    The third tab will display data from "sql Server" ContactsDatabase"

    However the contacts table relationship with the Address table is on fields ACCOUNT_ID & ADDRESS_REF, The address table is related to the Header table by ACCOUNT_ID only.

    I want to be able to display one contact record from the ContactsDatabase but also pull in its related Header and Address info from the Progress database.

    Both databases reside on the same SQL server.

    I currently have an existing Access front end that does this using linked tables and DSN.

    However I want to do this progamatically using ADO.

    The data from the Progress data will be read only and only used for informational purposes. I know I can create different connection strings but then what do I do with the data, do I put them into two recordsets? can I relate the recordsets to diplay the data on my form?

    Is there a prefered solution for this type of setup.

    Should I write an import routine to copy the Account Header and Address data from my Progress database into new tables on my Contacts Database? (Didn't really want to do this because as the Contacts Database application develops there is a lot more information in the Progress database that I need to tap into)

    Many Thanks

    Kevin

  4. #4
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Kevin,

    Sorry I misunderstood the original request.

    It looks like you will need independent ADO connections and recordsets to accomplish your goal. (Whenever you have more than one external database to hit at the same time, you are looking at more than one connection object.)

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums