none
What's the right strategy for population datasets from multiple databases RRS feed

  • Question

  • Hello all,

    First of all i am very new to ADO.NET and Visual Basic.NET. I have the following question:

    I have 3 databases (Microsoft Access). I want to retrieve from 2 databases a table. These tables are structure wise the same. I want to combine these tables and than count a collum (like in a select count statement). I read a couple of books and searched the internet, but i don't have a good idea how to handle my problem.

    I tried to retrieve the data from the databases, but i do not have a clue how to combine the results on a easy way.

    Thanks in advance.

    Kind regards,

     

    Maarten.

    Monday, February 21, 2011 5:11 PM

Answers

  • If you just want to join data from multiple databases with Access, you can do this without the need for a data set.

    There are several ways to do this.  One way is to create linked tables.  You can create a new "master" database and link in all the related tables from the different databases using the Access UI, then run queries against the linked tables from ADO.NET.

    Another option is to combine the databases on the fly using ADO.NET directly.  You can join tables from multiple databases using the OLEDB provider like so (C# code):

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb;
    
    namespace CrossDB
    {
    	class Program
    	{
    		static void Main(string[] args)
    		{
    			try
    			{
    				using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Issues\Database1.accdb;"))
    				{
    					conn.Open();
    					OleDbCommand cmd = conn.CreateCommand();
    					cmd.CommandText = @"select count(t1.id) from [C:\Issues\Database1.accdb].[Table1] as t1, [C:\Issues\Database1.accdb].[Table1] t2 where t1.id=t2.id";
    					using (OleDbDataReader dr = cmd.ExecuteReader())
    					{
    						while (dr.Read())
    						{
    							Console.WriteLine("{0}", dr.GetInt32(0));
    						}
    					}
    				}
    			}
    			catch (Exception ex)
    			{
    				Console.WriteLine(ex.Message);
    			}
    			Console.ReadLine();
    		}
    	}
    }
    

    In general the syntax is:

    SELECT * FROM [<database file path>].[<table name>] AS <alias>

    For example if I have a database named c:\db1.mdb with a table named Foo, I can say:

    SELECT t1.* FROM [c:\db1.mdb].[Foo] AS t1

    The alias (AS t1) is handy because it saves you lots of typing.  For example if I want to join 2 tables:

    SELECT t1.*, t2.* FROM [c:\db1.mdb].[Foo] AS t1, [c:\db2.mdb].[Foo] AS t2 WHERE t1.id=t2.id

    Without aliasing, the SQL would look really bad:

    SELECT [c:\db1.mdb].[Foo].*, [c:\db2.mdb].[Foo].* FROM [c:\db1.mdb].[Foo], [c:\db2.mdb].[Foo] WHERE [c:\db1.mdb].[Foo].id=[c:\db2.mdb].[Foo].id


    Matt
    Monday, February 21, 2011 8:11 PM
    Moderator

All replies

  • If you just want to join data from multiple databases with Access, you can do this without the need for a data set.

    There are several ways to do this.  One way is to create linked tables.  You can create a new "master" database and link in all the related tables from the different databases using the Access UI, then run queries against the linked tables from ADO.NET.

    Another option is to combine the databases on the fly using ADO.NET directly.  You can join tables from multiple databases using the OLEDB provider like so (C# code):

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Data.OleDb;
    
    namespace CrossDB
    {
    	class Program
    	{
    		static void Main(string[] args)
    		{
    			try
    			{
    				using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Issues\Database1.accdb;"))
    				{
    					conn.Open();
    					OleDbCommand cmd = conn.CreateCommand();
    					cmd.CommandText = @"select count(t1.id) from [C:\Issues\Database1.accdb].[Table1] as t1, [C:\Issues\Database1.accdb].[Table1] t2 where t1.id=t2.id";
    					using (OleDbDataReader dr = cmd.ExecuteReader())
    					{
    						while (dr.Read())
    						{
    							Console.WriteLine("{0}", dr.GetInt32(0));
    						}
    					}
    				}
    			}
    			catch (Exception ex)
    			{
    				Console.WriteLine(ex.Message);
    			}
    			Console.ReadLine();
    		}
    	}
    }
    

    In general the syntax is:

    SELECT * FROM [<database file path>].[<table name>] AS <alias>

    For example if I have a database named c:\db1.mdb with a table named Foo, I can say:

    SELECT t1.* FROM [c:\db1.mdb].[Foo] AS t1

    The alias (AS t1) is handy because it saves you lots of typing.  For example if I want to join 2 tables:

    SELECT t1.*, t2.* FROM [c:\db1.mdb].[Foo] AS t1, [c:\db2.mdb].[Foo] AS t2 WHERE t1.id=t2.id

    Without aliasing, the SQL would look really bad:

    SELECT [c:\db1.mdb].[Foo].*, [c:\db2.mdb].[Foo].* FROM [c:\db1.mdb].[Foo], [c:\db2.mdb].[Foo] WHERE [c:\db1.mdb].[Foo].id=[c:\db2.mdb].[Foo].id


    Matt
    Monday, February 21, 2011 8:11 PM
    Moderator
  • Since you are still learning, IMHO I think it's best if you learn best practices from the beginning. Using Access/JET databases is not going to help your career in the long run. I won't go into the litany of reasons, but if you want to learn how to use a "real" DBMS engine, I suggest starting with SQL Server Express. The skills you learn there can migrate to far more sophisticated DBMS applications. A year from now you'll probably reach Access' limits and your frustration will increas exponentially. I've written a dozen books for people just like you--using VB and databases. I'm sure one of them will help.

    __________________________________________________________________
    William Vaughn
    Mentor, Consultant, Trainer, MVP
    http://betav.com
    http://betav.com/blog/billva
    http://www.hitchhikerguides.net

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    Please click the Mark as Answer button if a post solves your problem!

    Monday, February 21, 2011 8:29 PM
    Moderator
  • Hello Matt,

    Thanks for the reply, i will investigate your option according to OLEDB provider.

    @Wiiliam thanks for your suggestion. The current program i am working on uses Access database and i can't switch that to SQL Server.

     

    Kind regards,

    Maarten.

    Tuesday, February 22, 2011 11:37 AM