title: How to create a join across two foxpro databases using the MS Ole DB provider?
body: <h3>Question:</h3>
  
  <p>I am working with existing commercial MS Visual Foxpro application, and need to extract data from the database directly using a c# application. Unfortunately, not all of the tables are stored in the same database, some of the records are stored in a database broken down by year. How do I create a query against these two databases using a single join?</p>
  
  <p>I am using Microsoft's <a href="http://www.microsoft.com/downloads/en/details.aspx?familyid=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4&amp;displaylang=en" rel="nofollow">OLE DB Provider for Visual FoxPro 9.0 (SP2)</a></p>
  
  <h3>More Detail:</h3>
  
  <p>Essentially, the customer information is stored in one database, and the customer purchase history is stored a database broken down by year. So I am trying to create a simple query to print out customers and their most recent purchase from this year.</p>
  
  <p>In graphical form, the file structure of the db looks like this:</p>
  
  <pre><code>Data\
   +-2009\
   |  +-MyDB.dbc
   |  +-Sales.dbf
   +-2010\
   |  +-MyDB.dbc
   |  +-Sales.dbf
   +-MyDB.dbc
   +-Customers.dbf
  </code></pre>
  
  <p>Currently I can connect to each DB individually, and query them:</p>
  
  <pre><code>// This works to connect to the customer DB
  string connectionPath1 = @"Provider=vfpoledb.1;Data Source=E:\Data\MyDB.dbc";
  OleDbConnection conn1 = new OleDbConnection(connectionPath1);
  OleDbCommand command1 = new OleDbCommand(@"SELECT * FROM Customers", conn1);
  OleDbDataReader reader1 = command1.ExecuteReader();
  
  // This works to connect to the annual sales record DB
  string connectionPath2 = @"Provider=vfpoledb.1;Data Source=E:\Data\2010\MyDB.dbc";
  OleDbConnection conn2 = new OleDbConnection(connectionPath2);
  OleDbCommand command2 = new OleDbCommand(@"SELECT * FROM Sales", conn2);
  OleDbDataReader reader2 = command2.ExecuteReader();
  </code></pre>
  
  <p>What I can't do is execute my join statement:</p>
  
  <pre><code>//How do I do this?
  OleDbConnection connMagic = new OleDbConnection(connectionPath1, connectionPath2); //non-valid code
  OleDbCommand commandIWant = new OleDbCommand(@"SELECT Customers.Name, Sales.Item, Sales.Date FROM Customers LEFT JOIN Sales ON (Customers.ID=Sales.CustomerID)", connMagic);
  OleDbDataReader reader3 = commandIWant.ExecuteReader();
  </code></pre>