none
How to read multiple Access database (mdb) files using OLEDB and populate Single dataset. RRS feed

  • Question

  • Hello Everybody,

    I have two Access database files (file1.mdb and file2.mdb). Both the databases are having one table each named "ProtocolLogs". So both the mdb files contains same database schema with different records.

    I am using OLEDB for reading mdb file. While reading I am using filter to get only valid records and fill the dataset.

    Now my doubt is, can it be possible that OLEDB shall read both the mdb files with same filter condition and populate the dataset with returned records?

    Kindly suggest me.

    Best Regards,

    Bibhudatta

    Monday, June 7, 2010 1:27 PM

Answers

  • Thanks a lot David for the reply. Let's see how this kind of implementation is going on. If I shall face any issue regarding this, I shall keep on posting to this thread as a discussion and information for others.

    Best Regards,

    Bibhudatta

    • Marked as answer by Bibhudatta Wednesday, June 9, 2010 9:52 AM
    Wednesday, June 9, 2010 9:52 AM

All replies

  • It's absolutely possible to do this.  The DataSet doesn't know or care where the data came from.  You'll just need to pull the data from separate OleDbReaders or OleDbDataAdapters.

    Are you running into a problem trying to retrieve the data from the different databases?


    David Sceppa
    Monday, June 7, 2010 8:38 PM
    Moderator
  • Hi David,

    Thank you very much of your help. I was trying something else and found a query to accomplish this.

    SELECT * from ProtocolLogs IN 'D:\Bibhu_Personal\Projects\C#_2008\AccessDBReader\AccessDBReader\bin\Debug\ProtocolLogsDB_1.mdb' where GenerateDate between #09/06/2010# and #12/06/2010# UNION SELECT * from ProtocolLogs IN 'D:\Bibhu_Personal\Projects\C#_2008\AccessDBReader\AccessDBReader\bin\Debug\ProtocolLogsDB_2.mdb' where GenerateDate between #09/06/2010# and #12/06/2010#

    This statement is querying both the mdb files and fills a single dataset as whole with corresponding records.

    I have used only one OleDbConnection, OleDbCommand, OleDbDataAdapter for this.

    Please let me know if this process is correct or not.

     

    Best Regards,

    Bibhudatta

    Tuesday, June 8, 2010 7:23 AM
  • The ability to reference a different database in your query is extremely powerful, especially if you want to compare or combine values in the different databases.

    Personally, I'd explicitly connect to each Access database and retrieve the results of the queries separately.  The DataTable doesn't know or care that the data came from different databases.  In the specific example you cited, the contents of the DataTable will be the same whether you use the Jet engine to assemble the results into a single resultset or whether you query the two databases separately.  But I don't know if you'll need to rely on queries that reference multiple databases in other parts of your application, so I won't tell you to abandon that approach.


    David Sceppa
    Tuesday, June 8, 2010 10:43 PM
    Moderator
  • Thanks a lot David for the reply. Let's see how this kind of implementation is going on. If I shall face any issue regarding this, I shall keep on posting to this thread as a discussion and information for others.

    Best Regards,

    Bibhudatta

    • Marked as answer by Bibhudatta Wednesday, June 9, 2010 9:52 AM
    Wednesday, June 9, 2010 9:52 AM