locked
How to join two different access database table ? RRS feed

  • Question

  • User1401320955 posted

    Hello frd's

    I have two different database. both have a country table in common. Lets consider first table A and second table B. both have a country with a various different field's.in both databse table Common is Country ID.

    Now in C# i want to join this two tables. Both are Different Access Database. (A first Accesss database and B second access database)

    How do i achieve this ? I got information from some resoruce that you can achieve this by using Linq to Dataset. but as my knowledge linq is mostly for SQL and why only for small purpose go for LINQ as already project is already done only reports part remianing. Please frd's if you have any infromation regarding this share with me.

     

    Waiting for your reply.

     

    Thank you.

     

    Wednesday, September 21, 2011 1:35 AM

Answers

  • User1781613271 posted


    As posted above by Hans_V executing remote query will help you in joining table from 2 Access Databases. Below is the sample that we
    can do in ASP build using C#.


    ADD Connectinstring in Web.Config (Example Below)
    <connectionStrings>
      <add name="JETDBSTRING"
           connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source = Write Path For Your Access Database"
           providerName="Microsoft.ACE.OLEDB.12.0" />
    </connectionStrings>

    Below is the example how you can access in your webpage code

       OleDbConnection conn = new OleDbConnection();
       conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["JETDBSTRING"].ConnectionString;
       OleDbCommand cmd = new OleDbCommand();
       cmd.Connection = conn;
       cmd.CommandText = @"select * from COUNTRY union  select * from  COUNTRY in '(Path For Your Access Table in single Quotes)'";

       DataSet Ds = new DataSet();
       OleDbDataAdapter Da = new OleDbDataAdapter();
       Da.SelectCommand = cmd;
       Da.Fill(Ds, "COUNTRY");

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 1, 2011 8:05 AM

All replies

  • User-1768369891 posted

    hi in sql do this...

    SELECT a.userID, b.usersFirstName, b.usersLastName  FROM databaseA.dbo.TableA a inner join database B.dbo.TableB b  ON a.userID=b.userID

    Wednesday, September 21, 2011 1:44 AM
  • User1401320955 posted

    Hi navneet thanks for your reply.

     

    but i don't want in sql i want clear idea about Access. your suggestion i already implemented in past for one store procedure in Sql server database.

    I want to achive Access different database table's joing in code behind. C# code.

     

    Thank you.

    Wednesday, September 21, 2011 1:51 AM
  • User-1768369891 posted

    hi try this..

    i suggest you that it done in sql in this manner for like databasename.Table name..OR in Access Read this...

    http://office.microsoft.com/en-us/access-help/join-tables-and-queries-HA010096320.aspx

    http://www.cwnresearch.com/resources/databases/access/tutorials/access2000/SQLQueries/SQLQueries.html

    Wednesday, September 21, 2011 2:38 AM
  • User-1199946673 posted

    There are two ways to go.

    1. You can link the table in the other database. Once you've done that you can access the table just like any other table

    2. You can use a remote query:

    SELECT * FROM country IN 'C:\PathTo\Db.mdb'

    If the database is password protected, you should use a different syntax:

    SELECT * FROM country IN '' [MS Access;PWD=xxxxxx;DATABASE=C:\PathTo\DB.mdb]

    Note the 2 single quotes after IN, they must be there,,,

    So making a union between thes two tables goes like this:

    SELECT * FROM country
    UNION
    SELECT * FROM country IN 'C:\PathTo\Db.mdb'

    Wednesday, September 21, 2011 3:05 AM
  • User3866881 posted

    Hello webaspdotnet,

    My addition to Hans_v's first answer about how to link tables:

    http://www.brighthub.com/computing/windows-platform/articles/28501.aspx#imgn_1

    Thursday, September 22, 2011 9:36 PM
  • User1401320955 posted

    hi hans_v,

     

    Thanks for your reply.

     

    can u suggeest me some common way. we are writing our connection string related property in web.config. So want same solution to use this web.config connection sting property.

     

    can u suggest me regaridng this ??

     

    thanks once again.

    Monday, September 26, 2011 1:53 AM
  • User1401320955 posted

    Thanks Decker for useful link. but i can't modify client database.

     

    Monday, September 26, 2011 1:54 AM
  • User1781613271 posted


    As posted above by Hans_V executing remote query will help you in joining table from 2 Access Databases. Below is the sample that we
    can do in ASP build using C#.


    ADD Connectinstring in Web.Config (Example Below)
    <connectionStrings>
      <add name="JETDBSTRING"
           connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source = Write Path For Your Access Database"
           providerName="Microsoft.ACE.OLEDB.12.0" />
    </connectionStrings>

    Below is the example how you can access in your webpage code

       OleDbConnection conn = new OleDbConnection();
       conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["JETDBSTRING"].ConnectionString;
       OleDbCommand cmd = new OleDbCommand();
       cmd.Connection = conn;
       cmd.CommandText = @"select * from COUNTRY union  select * from  COUNTRY in '(Path For Your Access Table in single Quotes)'";

       DataSet Ds = new DataSet();
       OleDbDataAdapter Da = new OleDbDataAdapter();
       Da.SelectCommand = cmd;
       Da.Fill(Ds, "COUNTRY");

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, October 1, 2011 8:05 AM