Join 2 Access Databases

[lithium112]

I need to join 2 access databases. I have tried using dataAdapters and then filling 2 different datatables. Then I used the merge method to join the datatables. The problem I had with that is that it added extra rows when adding the 2nd datatable in. Would anybody happen to know how to get the 2 databases to add the columns without adding extra rows?

So far I have tried filling the datatables with dataAdapters and then used the merge method. I have also tried Linq which didn't quite work out like I was hoping. If you need any other information, please let me know. Thanks!

[JOSheaIV]

Let me get a little more information first.

You say you are trying to merge 2 databases correct? However, a DataTable will only hold 1 table. A database can be made up of multiple tables. So are you trying to merge the Databases?

Let's assume you are. Then you need to be using a DataSet, which can hold multiple tables. These do support a merger (and I am pretty sure work with the adapters).

One other question, these databases, the way you make it sound, they have different tables, or tables with the same name, but vary (specifically in columns). Is that correct?

[lithium112]

I am trying to pull specific columns from 2 different databases, so I'm guessing I'll need to merge the databases to accomplish this. The more I research the issue, I believe there is a way to join the 2 databases in a query but haven't found out how.

Yes, the databases have different tables and columns that I need.

[oussama_1]

Actually you can take only the columns that you want instead of the whole database, change your command to something like this:

 Dim Command As New System.Data.OleDb.OleDbDataAdapter()
Command = New System.Data.OleDb.OleDbDataAdapter("select ColumnName from TableName", yourConnection)

[lithium112]

I am looking into pulling the columns from the databases from a query. I have something very similar: I am using c# btw.

var dt = new dataTable();
using (var dbConn = new OleDbConnection(connectionString)
{
       dbConn.Open();
       using (var da = new OleDbDataAdapter(MyQuery, dbConn))
       {
            da.fill(dt);
       }   
}

Now what I am currently looking into is how to get the query to pull the columns from both databases.

So i have a query similar to below:

SELECT TableFromDatabase1.ColumnName
TableFromDatabase2.ColumnName
FROM TableName

I tried an inner join on the 2nd database but then the query says that it can't find the location of the 2nd database. Is there a way to pass in a second connection string so that it can successfully join with another database? Or is there actually an easier way to do this? Thanks for your help guys!

[oussama_1]

my bad i forgot it's c sharp thread.
Your connection string links your program to only one database and in your query you are trying to select from both databases, what you need to do is to create 2 connection strings, each one for its own database and import your wanted columns into the same datatable (dt)

[JOSheaIV]

Hmmm, well we aren't seeing all your code, but I have a very good feeling I know what the problem is.

It's your "ConnectionString". While we can't see it, I assume you are assigning the Database name as part of it? I haven't seen Access's connection string, only SQL Server, Oracle, AS400, and IBMUDB2, so can you please show us what you have?

Assuming you are assigning the table, then their lies your problem. You are telling the connection "hey go talk to that database over their", and in your query you then try and talk to another database. The problem is, your connection has no clue how to talk to the other one.

Also, I personally haven't written a cross database join before, only table. Theoretically I can see it as possible. Something like

SELECT * FROM Database1.TableName T1 INNER JOIN Database2.TableName T2 ON ([...])

That "[...]" is what you would do your join with. What columns you want to match. Problem here, is this returns you some rows. So if you have matching column names you'll get probably overwritting issues.

Also, if your columns share the same name you need to use those aliases like T1.ColumnName = T2.ColumnName.

How about writing up some more of the SQL for us, might be able to help you more. Not seeing your table design, or knowning much about it is hurting us

[cgeier]

Please provide the table structure and some sample data from each table/database.

[lithium112]

We have decided to not add the information from the other database for now. I am going to be trying this in my spare time and will make some sample databases. Once I do, I'll give an update. I would really like to know how to do this for the future. Thanks for the help guys!