-
Mar 15th, 2011, 07:31 AM
#1
Thread Starter
New Member
Easiest way of merging two Access databases together
Hi All,
I have absolutely no clue about databases (although I've been developing for years) and find myself having to merge the contents of one file into another e.g. FileNew.accdb into FileMain.accdb, from a VB application. The two files contain 1 table, 1 primary key, identical columns etc. and I want to merge everything so my SQL is pointing to "Select * from ...."
I have accessed this data by adding it as a datasource in the solution explorer to the application (a different application I should point out) and accessing it using OleDb.OleDbConnection.
Having looked around the net it would seem I should be able to do this using SqlConnection but the first problem I get is that elements of the connectionString aren't recognised (I assume this is due to not setting references correctly or something). Do I still need to add the database as a data source in order to satisfy filling the SqlDataAdapter etc?
Whats the difference between SqlConnection and OleDbConnection?
Basically I need a complete idiots guide to merging two files which expands on the following psuedo code:
get data from file 1
get data from file 2
Merge file1 data into file 2
Save file2 data
close both files
Thanks in advance
-
Mar 15th, 2011, 02:32 PM
#2
Lively Member
Re: Easiest way of merging two Access databases together
Easiest way of merging Access database together?
I presume you want to merge two similar tables from 2 .accds? if this is the case you can create an append query from within MS ACCESS db.
Open FileNew.accdb within MS ACCESS then make an APPEND QUERRY, then RUN it.
Code:
INSERT INTO <YourTableDest> IN 'C:\FileMain.accdb'
SELECT <YourTableSour>.*
FROM <YourTableSour>;
<YourTableDest> is your table from FileMain.accbd
<YourTableSour> is your table from FileNew.accbd
Last edited by nubie; Mar 15th, 2011 at 02:47 PM.
-
Mar 16th, 2011, 04:16 AM
#3
Thread Starter
New Member
Re: Easiest way of merging two Access databases together
Hi Nubie,
Thanks for that - unfortunately I need to do this merge from a VB.Net application.
I was hoping someone could tell if something like the DataSet.Merge(xxxxx) function was appropriate and if so a good example of how to use it
Should I have posted this thread in the VB.NET forum?
-
Mar 16th, 2011, 04:29 AM
#4
Re: Easiest way of merging two Access databases together
Welcome to VBForums
The SQL statement that nubie posted (like most others) can be run from VB.Net too, just create a command object (OLEDBCommand) and set the properties as apt, then do .ExecuteNonQuery
Whats the difference between SqlConnection and OleDbConnection?
They are almost identical, but the OleDB one is for any database system, whereas the SQL one is for SQL Server only (and because it is specific, it is more efficient).
In general any database related item that starts with Sql (such as SqlCommand) is directly equivalent to the OleDb equivalent (such as OleDbCommand).
Should I have posted this thread in the VB.NET forum?
It is better here, as you tend to get the database specialists (who can give advice based on lots of experience), rather than just those with general VB.Net knowledge (who can be very helpful, but are likely to give suggestions that aren't as good).
-
Mar 16th, 2011, 05:00 AM
#5
Thread Starter
New Member
Re: Easiest way of merging two Access databases together
Hi Si,
As I said, I have no clue about DB stuff and SQL (too many years dealing with embedded device code!!) so will have to look at the command object solution.
I'll bite the bullet and post some (probably embarassing) code that I originally used to approach the problem and maybe someone can expalin to me why it doesn't work. Given that the source has 1500 records in it and the destination is empty - I would have expected the destination to contain all the records form the source after running this code - but it remains empty. As said before, the table structure for both is identical.
Code:
Private Sub MergeDatabase(ByVal SourceDB As String, ByVal DestinationDB As String)
Dim CellPassword As String = ""
Dim DestinationDataBaseConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DestinationDB & ";Jet OLEDB:Database Password=" & CellPassword & ";Mode=Share Deny None;"
Dim SourceDataBaseConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & SourceDB & ";Jet OLEDB:Database Password=" & CellPassword & ";Mode=Share Deny None;"
Dim sql As String = "SELECT * FROM Cell_Data"
Try
'Open Connections
Dim sourcecon As New OleDb.OleDbConnection
sourcecon.ConnectionString = SourceDataBaseConnectionString
sourcecon.Open()
Dim destcon As New OleDb.OleDbConnection
destcon.ConnectionString = DestinationDataBaseConnectionString
destcon.Open()
'Get source data into DataSet
Dim Source_DS As New CellDataPointsDataSet
Dim Source_DA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, sourcecon)
Source_DA.Fill(Source_DS, "Cell_Data")
Dim Source_CB As New OleDb.OleDbCommandBuilder(Source_DA)
'Get Destination data into DataSet
Dim Dest_DS As New CellDataPointsDataSet
Dim Dest_DA As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(sql, destcon)
Dest_DA.Fill(Dest_DS, "Cell_Data")
Dim Dest_CB As New OleDb.OleDbCommandBuilder(Dest_DA)
'Merge the databases
Dest_DS.Merge(Source_DS, True, MissingSchemaAction.AddWithKey)
'Save changes to the Destination accdb
Dest_DA.Update(Dest_DS, "Cell_Data")
'Close Connections
sourcecon.Close()
destcon.Close()
Catch ex As Exception
End Try
End Sub
-
Mar 16th, 2011, 05:03 AM
#6
Thread Starter
New Member
Re: Easiest way of merging two Access databases together
...I should also point out that if I loop through and add each row instead of using the .Merge function it works perfectly. So I'm definitely doing something wrong with the Merge
-
Mar 16th, 2011, 10:30 AM
#7
Lively Member
Re: Easiest way of merging two Access databases together
check this out, this works in my sample vb.net app
Code:
Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\FileMain.accdb")
Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO d SELECT * FROM [MS Access;DATABASE=C:\FileNew.accdb].[s]", AccessConn)
AccessConn.Open()
AccessCommand.ExecuteNonQuery()
AccessConn.Close()
Note:
d --> destination table
s --> source table
-
Mar 17th, 2011, 12:08 PM
#8
Thread Starter
New Member
Re: Easiest way of merging two Access databases together
Hi nubie,
I was hoping to get a try at that today - but obviously didn't. I'll let you know how I get on when I do find time to look at it (in the next few days I hope)
Cheers
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|