SQL server and Access SELECT in one Query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • TomH
    #1

    SQL server and Access SELECT in one Query

    How do I create one SELECT query in VB .NET that pulls from a SQL Server
    database and a MS Access database? The SQL server table has a unique
    identifier as a PK and the Access table has a autonumber (replication ID) as
    a FK, which matches the SQL table PK, so I know I can join them on this field.

    Currently, I am using SQLCommand to access only the SQL server data and DAO
    to access only the MS Access data. Is there a way to create a
    SQLConnectionSt ring that points to multiple DBs?

    What is the best approach?

    Thanks,

    Tom
    Tags: None
    • Cor Ligthert [MVP]
      #2
      Re: SQL server and Access SELECT in one Query

      Tomh,

      If you ask this in the newsgroup

      microsoft.publi c.dotnet.framew ork.adonet

      Than you have much more change on an answer on this particular question.

      Cor

      "TomH" <TomH@discussio ns.microsoft.co mschreef in bericht
      news:18D0CD10-1B71-436B-A796-43A6928E7491@mi crosoft.com...
      How do I create one SELECT query in VB .NET that pulls from a SQL Server
      database and a MS Access database? The SQL server table has a unique
      identifier as a PK and the Access table has a autonumber (replication ID)
      as
      a FK, which matches the SQL table PK, so I know I can join them on this
      field.
      >
      Currently, I am using SQLCommand to access only the SQL server data and
      DAO
      to access only the MS Access data. Is there a way to create a
      SQLConnectionSt ring that points to multiple DBs?
      >
      What is the best approach?
      >
      Thanks,
      >
      Tom

        Comment

        • Coder
          #3
          Re: SQL server and Access SELECT in one Query

          Why use DAO? It's so old I can't even remember the syntax any more...

          Maybe your question hides specific details that mean you are limited to
          existing code or something... if not use ADO.Net and save a lot of hassle.

          You could fill one table in a dataset with a sqldataadapter, and another
          with the oledbadapter (for Access), and also define your relationship in the
          dataset between the two tables, exactly as you described plus with the
          options of referential integrity checking, cascaded deletes, updates etc
          etc. That's what it's for isn't it?

          Cheers




          "TomH" <TomH@discussio ns.microsoft.co mwrote in message
          news:18D0CD10-1B71-436B-A796-43A6928E7491@mi crosoft.com...
          How do I create one SELECT query in VB .NET that pulls from a SQL Server
          database and a MS Access database? The SQL server table has a unique
          identifier as a PK and the Access table has a autonumber (replication ID)
          as
          a FK, which matches the SQL table PK, so I know I can join them on this
          field.
          >
          Currently, I am using SQLCommand to access only the SQL server data and
          DAO
          to access only the MS Access data. Is there a way to create a
          SQLConnectionSt ring that points to multiple DBs?
          >
          What is the best approach?
          >
          Thanks,
          >
          Tom

            Comment

            • RobinS
              #4
              Re: SQL server and Access SELECT in one Query

              I don't know of any way that you can open two databases with the
              same connection. Why do you want to? Is there another solution
              to resolve what you're trying to accomplish?

              The only thing I can think of is you could add linked tables to your
              Access database that access the SQLServer database tables you're
              interested in, and then you could query those tables and your pure
              Access tables together.

              Robin S.
              -------------------------------------------
              "TomH" <TomH@discussio ns.microsoft.co mwrote in message
              news:18D0CD10-1B71-436B-A796-43A6928E7491@mi crosoft.com...
              How do I create one SELECT query in VB .NET that pulls from a SQL Server
              database and a MS Access database? The SQL server table has a unique
              identifier as a PK and the Access table has a autonumber (replication ID)
              as
              a FK, which matches the SQL table PK, so I know I can join them on this
              field.
              >
              Currently, I am using SQLCommand to access only the SQL server data and
              DAO
              to access only the MS Access data. Is there a way to create a
              SQLConnectionSt ring that points to multiple DBs?
              >
              What is the best approach?
              >
              Thanks,
              >
              Tom

                Comment

                • Michel Posseth [MCP]
                  #5
                  RE: SQL server and Access SELECT in one Query


                  http://www.sqlservercentral.com/colu...kingtables.asp

                  regards

                  Michel Posseth



                  "TomH" wrote:
                  How do I create one SELECT query in VB .NET that pulls from a SQL Server
                  database and a MS Access database? The SQL server table has a unique
                  identifier as a PK and the Access table has a autonumber (replication ID) as
                  a FK, which matches the SQL table PK, so I know I can join them on this field.
                  >
                  Currently, I am using SQLCommand to access only the SQL server data and DAO
                  to access only the MS Access data. Is there a way to create a
                  SQLConnectionSt ring that points to multiple DBs?
                  >
                  What is the best approach?
                  >
                  Thanks,
                  >
                  Tom

                    Comment

                    • Rad [Visual C# MVP]
                      #6
                      Re: SQL server and Access SELECT in one Query

                      On Sun, 3 Dec 2006 20:26:00 -0800, TomH wrote:
                      How do I create one SELECT query in VB .NET that pulls from a SQL Server
                      database and a MS Access database? The SQL server table has a unique
                      identifier as a PK and the Access table has a autonumber (replication ID) as
                      a FK, which matches the SQL table PK, so I know I can join them on this field.
                      >
                      Currently, I am using SQLCommand to access only the SQL server data and DAO
                      to access only the MS Access data. Is there a way to create a
                      SQLConnectionSt ring that points to multiple DBs?
                      >
                      What is the best approach?
                      >
                      Thanks,
                      >
                      Tom
                      I believe in SQL server you can create a linked server to access the access
                      database
                      --
                      Bits.Bytes
                      http://bytes.thinkersroom.com

                        Comment

                        Previous template Next
                        Working...